PostgreSQL Oracle 相容性 - Analysis函數之keep

标簽

PostgreSQL , Oracle , 分析函數 , 視窗函數 , keep

https://github.com/digoal/blog/blob/master/201805/20180510_02.md#%E8%83%8C%E6%99%AF 背景

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  
           

https://github.com/digoal/blog/blob/master/201805/20180510_02.md#postgresql-keep-%E5%85%BC%E5%AE%B9%E7%94%A8%E6%B3%95 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://github.com/digoal/blog/blob/master/201805/20180510_02.md#%E5%8F%82%E8%80%83 參考

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, ...)》