PostgreSQL Oracle 兼容性 - Analysis函数之keep

背景

Oracle 分析函数KEEP，类似OVER的语法结构（当然，含义与之不同）。keep可以用于普通的查询，也可以用于分组聚合，同时亦可用于窗口中。

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

例子

create table emp (empno int, ename varchar2(64), mgr int, sal int, deptno int);  
  
  
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);  
  
  
  
postgres=# select * from emp order by deptno,sal,mgr;  
 empno | ename  | mgr  | sal  | deptno   
-------+--------+------+------+--------  
  7934 | MILLER | 7782 | 1300 |     10  
  7782 | CLARK  | 7839 | 2450 |     10  
  7839 | KING   | 7567 | 5000 |     10  
  7369 | SMITH  | 7902 |  800 |     20  
  7876 | ADAMS  | 7788 | 1100 |     20  
  7566 | JONES  | 7839 | 2975 |     20  
  7902 | FORD   | 7555 | 3000 |     20  
  7788 | SCOTT  | 7566 | 3000 |     20  
  7900 | JAMES  | 7698 |  950 |     30  
  7654 | MARTIN | 7698 | 1250 |     30  
  7521 | WARD   | 7698 | 1250 |     30  
  7844 | TURNER | 7698 | 1500 |     30  
  7499 | ALLEN  | 7698 | 1600 |     30  
  7698 | BLAKE  | 7839 | 2850 |     30  
(14 rows)  

Oracle 查询如下，下面看看PostgreSQL的兼容写法

SELECT   
deptno,  
MIN(t.mgr) KEEP(DENSE_RANK FIRST ORDER BY t.sal) a,   -- FIRST对应 pg order by sal , dense_rank()=1  
MAX(t.mgr) KEEP(DENSE_RANK FIRST ORDER BY t.sal) b,   
MIN(t.mgr) KEEP(DENSE_RANK LAST ORDER BY t.sal) c,    -- LAST对应 pg order by sal desc , dense_rank()=1  
MAX(t.mgr) KEEP(DENSE_RANK LAST ORDER BY t.sal) d   
FROM emp t group by deptno;    
  
    DEPTNO          A          B          C          D  
---------- ---------- ---------- ---------- ----------  
        10       7782       7782       7567       7567  
        20       7902       7902       7555       7566  
        30       7698       7698       7839       7839  

PostgreSQL keep 兼容用法

1、建表

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

2、灌入数据

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

3、分开查询如下

postgres=# select deptno,min(mgr),max(mgr) from (  
  select *, dense_rank() over w1 from emp window w1 as (partition by deptno order by sal)  -- 得到dense_rank的值 , order by sal 对应 FIRST  
) t   
where dense_rank=1   
group by deptno;  
  
 deptno | min  | max    
--------+------+------  
     10 | 7782 | 7782  
     20 | 7902 | 7902  
     30 | 7698 | 7698  
(3 rows)  

postgres=# select deptno,min(mgr),max(mgr) from (  
  select *, dense_rank() over w1 from emp window w1 as (partition by deptno order by sal desc)  -- 得到dense_rank的值 , order by sal desc 对应 LAST  
) t   
where dense_rank=1   
group by deptno;  
  
 deptno | min  | max    
--------+------+------  
     10 | 7567 | 7567  
     20 | 7555 | 7566  
     30 | 7839 | 7839  
(3 rows)  

4、合并查询，用JOIN

select t1.deptno, t1.min, t1.max, t2.min, t2.max from  
(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  
join  
(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  
using (deptno);  
  
 deptno | min  | max  | min  | max    
--------+------+------+------+------  
     10 | 7782 | 7782 | 7567 | 7567  
     20 | 7902 | 7902 | 7555 | 7566  
     30 | 7698 | 7698 | 7839 | 7839  
(3 rows)  

参考

https://docs.oracle.com/cd/B28359_01/server.111/b28313/analysis.htm

https://blog.csdn.net/java3344520/article/details/5603309

https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions

https://stackoverflow.com/questions/10756717/sql-server-how-to-imitate-oracle-keep-dense-rank-query

https://www.postgresql.org/docs/10/static/functions-window.html

《PostgreSQL SELECT 的高级用法(CTE, LATERAL, ORDINALITY, WINDOW, SKIP LOCKED, DISTINCT, GROUPING SETS, …)》

digoal’s 大量PostgreSQL文章入口