标签
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
