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)
参考链接