VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

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)

参考链接

聚集函数