All Products
Search
Document Center

KEEP

Last Updated: Jun 18, 2021

The KEEP function operates on the values from a set of rows that rank based on a given sorting rule and returns the first or last value. As an aggregate function, the KEEP function operates on all rows and returns a single output row. As an analytic function, the KEEP function divides the query result set into groups based on one or more expressions in the query_partition_clause.

The KEEP function must be used in conjunction with the MIN, MAX, SUM, AVG, COUNT, VARIANCE or STDDEV function.

Syntax

KEEP (DENSE_RANK {FIRST | LAST} ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ]]...) [ OVER ( [query_partition_clause] ) ]
[ OVER ( [query_partition_clause] ) ]

To use it as an aggregate function, it operates on a set of rows and returns a single value. You do not need to add the OVER keyword. 作为聚合函数使用时,该函数对一组行的集合进行聚合计算,结果只能返回一个值,此时不需要加 OVER 关键字。

参数

参数

说明

OVER

使用 OVER 子句定义窗口进行计算。

expr

Null values in the measure column are ignored. 度量列中的空值将被忽略。

返回类型

返回与度量列相同的数据类型。

示例

分析函数示例

建表 employees,并向里面插入数据,执行以下语句:

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);

对于每个部门:按照薪水排序,对排在第一的员工的薪水求和;按照雇用日期排序,对排在最后的员工的薪水求和。 执行以下语句:

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;

查询结果如下:

+-----------+---------------+--------+-------+-------+
| 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)

聚合函数示例

建表 employees,并向里面插入数据,执行以下语句:

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);

按照薪水排序,对排在第一的员工的薪水求和;按照雇用日期排序,对排在最后的员工的薪水求和。 执行以下语句:

SELECT 
       SUM(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) "Worst",
       SUM(salary) KEEP (DENSE_RANK LAST ORDER BY HIREDATE) "Best"
  FROM employees;

查询结果如下:

+-------+-------+
| Worst | Best  |
+-------+-------+
|  1400 | 27500 |
+-------+-------+
1 row in set (0.00 sec)