KEEP

功能描述

Vastbase在Oracle兼容模式下支持KEEP分析函数，既支持keep(dense_rank first | last order by…)子句的使用，也支持KEEP与聚合函数表达式结合使用。

其中聚合函数包括：MIN，MAX，SUM，AVG，COUNT，VARIANCE，STDDEV七种。
KEEP中可以在order by后添加nulls first和nulls last（缺省值）。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库初始化时指定DBCOMPATIBILITY='A'）。
暂不支持keep的窗口聚集函数，即暂不支持KEEP语法中包含over子句。
KEEP内必须有order by，且只能是order by，不可为partition by或group by。
KEEP子句中的order by暂不支持包含聚合函数和窗口函数，且暂不支持keep使用嵌套聚集函数。
暂不支持在使用KEEP的同时在聚合函数内使用distinct。
暂不支持列存。
暂不支持pivot中使用KEEP。

语法格式

keep(dense_rank first | last order by...)

first::=
last::=

参数说明

KEEP

保持满足括号内条件的记录。
dense_rank

排序策略。
FIRST | LAST

数据进行筛选。
- FIRST表示从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等）。
- LAST表示从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等）

示例

示例1 结合聚合函数MIN使用keep。

1、创建测试表。

CREATE TABLE emp (
empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBER(7,2),
deptno NUMBER(2) 
);

2、向测试表中插入数据并查询插入结果。

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
SELECT * FROM emp;

3、执行如下SQL查询表中数据。

获取表中sal列的最小值，别名为max列（按hiredate列排名第一的sal列的最小值）的数据。
```
select min(sal),min(sal) keep (dense_rank first order by hiredate) as max from emp;
```
查询结果返回为如下：
```
min | max
----+-----
800 | 800
(1 row)
```
获取表中sal列的最小值，别名为max列（按hiredate列降序排名第一的sal列的最小值）的数据。
```
select min(sal),min(sal) keep (dense_rank first order by hiredate desc) as max from emp;
```
查询结果返回为如下：
```
min | max
----+------
800 | 1100
(1 row)
```

示例2 查询中带有group by子句，结合聚合函数MIN使用keep，且keep中order by子句后跟多个字段。

1、创建测试表并插入数据。

CREATE TABLE test (name, sal, deptno) AS
SELECT 'a', 1, 1 FROM DUAL
UNION ALL SELECT 'b', 1, 1 FROM DUAL
UNION ALL SELECT 'c', 1, 1 FROM DUAL
UNION ALL SELECT 'd', 2, 1 FROM DUAL
UNION ALL SELECT 'e', 3, 1 FROM DUAL
UNION ALL SELECT 'f', 3, 1 FROM DUAL
UNION ALL SELECT 'g', 4, 2 FROM DUAL
UNION ALL SELECT 'h', 4, 2 FROM DUAL
UNION ALL SELECT 'i', 5, 2 FROM DUAL
UNION ALL SELECT 'j', 5, 2 FROM DUAL;

2、查询插入情况。

SELECT * FROM test;

结果返回为如下：

 name | sal | deptno
------+-----+--------
 a    |   1 |      1
 b    |   1 |      1
 c    |   1 |      1
 d    |   2 |      1
 e    |   3 |      1
 f    |   3 |      1
 g    |   4 |      2
 h    |   4 |      2
 i    |   5 |      2
 j    |   5 |      2
(10 rows)

3、执行以下SQL查询表数据。

SELECT deptno, min(name) keep ( dense_rank first order by sal desc, name ) ,
max(sal)
FROM test
group by deptno;

查询结果为如下，返回结果中min列表示根据deptno聚合后，各部分按照sal，name列降序排名第一的name列最小值：

 deptno | min | max
--------+-----+-----
      1 | e   |   3
      2 | i   |   5
(2 rows)

参考链接

聚集函数

VastbaseG100