PostgreSQL Oracle 兼容性 - Analysis函数之keep

作者

digoal

日期

2018-05-10

标签

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

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.