oracle分析函数keep,PostgreSQL 实现Oracle分析函数之keep

keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数。使用KEEP 时和DENSE_RANK FIRST /DENSE_RANK LAST一起使用，获取一组中排名第一或者排名最后的记录。必须有order by 子句用来排序。后面也可以接over()分析函数部分。https://www.cndba.cn/foucus/article/4123

SELECT deptno, MIN(t.mgr) KEEP (DENSE_RANK FIRST ORDER BY t.sal) a

from emp t

group by deptno;

例如上面这条语句a字段的含义就是：

1、按deptno分组，

2、分组内按sal排序，

3、DENSE_RANK FIRST表示HOLD住sal排在前面的一组数据(当排在前面的sal有重复值时，多条被HOLD)，

4、然后在这组记录中，执行前面的聚合函数，这里是min(t.mgr)。https://www.cndba.cn/foucus/article/4123https://www.cndba.cn/foucus/article/4123

例子：

oracle；

建表：

https://www.cndba.cn/foucus/article/4123

CREATE TABLE emp

(

emp_id NUMBER(6),

ename VARCHAR2(45),

dept_id NUMBER(4),

hire_date DATE,

sal NUMBER(8,2)

);

插入数据：https://www.cndba.cn/foucus/article/4123

https://www.cndba.cn/foucus/article/4123

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (101, 'Tom', 20, TO_DATE('21-09-1989', 'DD-MM-YYYY'), 2000);

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (102, 'Mike', 20, TO_DATE('13-01-1993', 'DD-MM-YYYY'), 8000);

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (120, 'John', 50, TO_DATE('18-07-1996', 'DD-MM-YYYY'), 1000);

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (121, 'Joy', 50, TO_DATE('10-04-1997', 'DD-MM-YYYY'), 1000);

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (122, 'Rich', 50, TO_DATE('01-05-1995', 'DD-MM-YYYY'), 3000);

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (123, 'Kate', 50, TO_DATE('10-10-1997', 'DD-MM-YYYY'), 5000);

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (124, 'Jess', 50, TO_DATE('16-11-1999', 'DD-MM-YYYY'), 6000);

INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (100, 'Stev', 10, TO_DATE('01-01-1990', 'DD-MM-YYYY'), 7000);

COMMIT;

SQL> SELECT emp_id,ename,dept_id,hire_date,sal,

2 DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY sal) DENSE_RANK,

3 MIN(hire_date) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY dept_id) min_first,

4 MIN(hire_date) KEEP (DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY dept_id) min_last,

5 MAX(hire_date) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY dept_id) max_first,

MAX(hire_date) KEEP (DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY dept_id) max_last

6 FROM emp; 7

EMP_ID ENAME DEPT_ID HIRE_DATE SAL DENSE_RANK MIN_FIRST MIN_LAST MAX_FIRST MAX_LAST

---------- --------------------------------------------- ---------- --------- ---------- ---------- --------- --------- --------- ---------

100 Stev 10 01-JAN-90 7000 1 01-JAN-90 01-JAN-90 01-JAN-90 01-JAN-90

101 Tom 20 21-SEP-89 2000 1 21-SEP-89 13-JAN-93 21-SEP-89 13-JAN-93

102 Mike 20 13-JAN-93 8000 2 21-SEP-89 13-JAN-93 21-SEP-89 13-JAN-93

120 John 50 18-JUL-96 1000 1 18-JUL-96 16-NOV-99 10-APR-97 16-NOV-99

121 Joy 50 10-APR-97 1000 1 18-JUL-96 16-NOV-99 10-APR-97 16-NOV-99

122 Rich 50 01-MAY-95 3000 2 18-JUL-96 16-NOV-99 10-APR-97 16-NOV-99

123 Kate 50 10-OCT-97 5000 3 18-JUL-96 16-NOV-99 10-APR-97 16-NOV-99

124 Jess 50 16-NOV-99 6000 4 18-JUL-96 16-NOV-99 10-APR-97 16-NOV-99

8 rows selected.

PostgreSQL兼容写法：

建表：

https://www.cndba.cn/foucus/article/4123

bill=# create table emp (empno int, ename text, mgr int, sal int, deptno int);

CREATE TABLE

插入数据：

insert into emp values (7369, 'SMITH', 7902, 800, 20);

insert into emp values (7900, 'JAMES', 7698, 950, 30);

insert into emp values (7876, 'ADAMS', 7788 , 1100, 20);

insert into emp values (7521, 'WARD' , 7698 , 1250, 30);

insert into emp values (7654, 'MARTIN', 7698 , 1250, 30);

insert into emp values (7934, 'MILLER', 7782 , 1300, 10);

insert into emp values (7844, 'TURNER', 7698 , 1500, 30);

insert into emp values (7499, 'ALLEN', 7698, 1600, 30);

insert into emp values (7782, 'CLARK', 7839 , 2450, 10);

insert into emp values (7698, 'BLAKE', 7839 , 2850, 30);

insert into emp values (7566, 'JONES', 7839 , 2975, 20);

insert into emp values (7788, 'SCOTT', 7566 , 3000, 20);

insert into emp values (7902, 'FORD' , 7555 , 3000, 20);

insert into emp values (7839, 'KING' , 7567, 5000, 10);

分开查询：https://www.cndba.cn/foucus/article/4123

https://www.cndba.cn/foucus/article/4123

bill=# select deptno,min(mgr),max(mgr) from (

bill(# select *, dense_rank() over w1 from emp window w1 as (partition by deptno order by sal) -- 得到dense_rank的值 , order by sal 对应 FIRST

bill(# ) t

bill-# where dense_rank=1

bill-# group by deptno;

deptno | min | max

--------+------+------

10 | 7782 | 7782

20 | 7902 | 7902

30 | 7698 | 7698

(3 rows)

bill=# select deptno,min(mgr),max(mgr) from (

bill(# select *, dense_rank() over w1 from emp window w1 as (partition by deptno order by sal desc) -- 得到dense_rank的值 , order by sal desc 对应 LAST

bill(# ) t

bill-# where dense_rank=1

bill-# group by deptno;

deptno | min | max

--------+------+------

10 | 7567 | 7567

20 | 7555 | 7566

30 | 7839 | 7839

(3 rows)

合并查询:

bill=# select t1.deptno, t1.min, t1.max, t2.min, t2.max from

bill-# (select deptno,min(mgr),max(mgr) from (select *, dense_rank() over w1 from emp window w1 as (partition by deptno order by sal)) t where dense_rank=1 group by deptno) t1

bill-# join

bill-# (select deptno,min(mgr),max(mgr) from (select *, dense_rank() over w1 from emp window w1 as (partition by deptno order by sal desc)) t where dense_rank=1 group by deptno) t2

bill-# using (deptno);

deptno | min | max | min | max

--------+------+------+------+------

10 | 7782 | 7782 | 7567 | 7567

20 | 7902 | 7902 | 7555 | 7566

30 | 7698 | 7698 | 7839 | 7839

(3 rows)

版权声明：本文为博主原创文章，未经博主允许不得转载。