建站
咨詢
售后
建站咨詢
新聞中心
KEEP 函數(shù)用于對(duì)一組行中的值進(jìn)行操作，將這組行按照給定的排序規(guī)則排序后返回排在第一或最后的值。作為聚合函數(shù)，KEEP 對(duì)所有行進(jìn)行操作并返回單個(gè)輸出行。作為分析函數(shù)，KEEP 基于 query_partition_clause 中的一個(gè)或多個(gè)表達(dá)式將查詢結(jié)果集分為幾組。
創(chuàng)新互聯(lián)服務(wù)緊隨時(shí)代發(fā)展步伐，進(jìn)行技術(shù)革新和技術(shù)進(jìn)步，經(jīng)過十余年的發(fā)展和積累，已經(jīng)匯集了一批資深網(wǎng)站策劃師、設(shè)計(jì)師、專業(yè)的網(wǎng)站實(shí)施團(tuán)隊(duì)以及高素質(zhì)售后服務(wù)人員，并且完全形成了一套成熟的業(yè)務(wù)流程，能夠完全依照客戶要求對(duì)網(wǎng)站進(jìn)行做網(wǎng)站、網(wǎng)站制作、建設(shè)、維護(hù)、更新和改版，實(shí)現(xiàn)客戶網(wǎng)站對(duì)外宣傳展示的首要目的，并為客戶企業(yè)品牌互聯(lián)網(wǎng)化提供全面的解決方案。
KEEP 函數(shù)必須與 MIN、MAX、SUM、AVG、COUNT、VARIANCE 或 STDDEV 函數(shù)一起使用。
語法
KEEP (DENSE_RANK {FIRST | LAST} ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ]]... )
[ OVER ( [query_partition_clause] ) ]作為分析函數(shù)使用時(shí)，您需要使用窗口函數(shù)的完整語法，它對(duì)一組行的集合進(jìn)行計(jì)算并返回多個(gè)值。作為聚合函數(shù)使用時(shí)，該函數(shù)對(duì)一組行的集合進(jìn)行聚合計(jì)算，結(jié)果只能返回一個(gè)值，此時(shí)不需要加 OVER 關(guān)鍵字。
參數(shù)
|
參數(shù) |
說明 |
|---|---|
|
OVER |
使用 |
|
expr |
可以是任何表達(dá)式。度量列中的空值將被忽略。 |
返回類型
返回與度量列相同的數(shù)據(jù)類型。
示例
分析函數(shù)示例
建表 employees，并向里面插入數(shù)據(jù)，執(zhí)行以下語句：
CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(40, 100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(50, 100, 'Hartstein', '2019-10-05',14000);
INSERT INTO employees VALUES(50, 100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(50, 100, 'Weiss', '2019-10-05',13500);
INSERT INTO employees VALUES(90, 100, 'Russell', '2019-07-11', 13000);
INSERT INTO employees VALUES(90,100, 'Partners', '2018-12-01',14000);對(duì)于每個(gè)部門：按照薪水排序，對(duì)排在第一的員工的薪水求和；按照雇用日期排序，對(duì)排在最后的員工的薪水求和。執(zhí)行以下語句：
SELECT last_name, department_id, salary,
SUM(salary) KEEP (DENSE_RANK FIRST ORDER BY salary)
OVER (PARTITION BY department_id) "Worst",
SUM(salary) KEEP (DENSE_RANK LAST ORDER BY HIREDATE)
OVER (PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary, last_name;查詢結(jié)果如下：
+-----------+---------------+--------+-------+-------+
| LAST_NAME | DEPARTMENT_ID | SALARY | Worst | Best |
+-----------+---------------+--------+-------+-------+
| Raphaely | 30 | 1700 | 1700 | 11000 |
| De Haan | 30 | 11000 | 1700 | 11000 |
| Errazuriz | 40 | 1400 | 1400 | 1400 |
| Raphaely | 50 | 1700 | 1700 | 27500 |
| Weiss | 50 | 13500 | 1700 | 27500 |
| Hartstein | 50 | 14000 | 1700 | 27500 |
| Russell | 90 | 13000 | 13000 | 13000 |
| Partners | 90 | 14000 | 13000 | 13000 |
+-----------+---------------+--------+-------+-------+
8 rows in set (0.01 sec)聚合函數(shù)示例
建表 employees，并向里面插入數(shù)據(jù)，執(zhí)行以下語句：
CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(40, 100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(50, 100, 'Hartstein', '2019-10-05',14000);
INSERT INTO employees VALUES(50, 100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(50, 100, 'Weiss', '2019-10-05',13500);
INSERT INTO employees VALUES(90, 100, 'Russell', '2019-07-11', 13000);
INSERT INTO employees VALUES(90,100, 'Partners', '2018-12-01',14000);按照薪水排序，對(duì)排在第一的員工的薪水求和；按照雇用日期排序，對(duì)排在最后的員工的薪水求和。執(zhí)行以下語句：
SELECT
SUM(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) "Worst",
SUM(salary) KEEP (DENSE_RANK LAST ORDER BY HIREDATE) "Best"
FROM employees;查詢結(jié)果如下：
+-------+-------+
| Worst | Best |
+-------+-------+
| 1400 | 27500 |
+-------+-------+
1 row in set (0.00 sec)
文章標(biāo)題：創(chuàng)新互聯(lián)OceanBase教程：OceanBaseKEEP
文章路徑：http://www.fisionsoft.com.cn/article/cosedsd.html
