PostgreSQLOracle相容性-Analysis函式之keep

標籤

PostgreSQL , Oracle , 分析函式 , 視窗函式 , 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, …)》