[分析函数] over partition by

1. over partition by 和 group by的区别

- over partition by 可以将汇总数据和源数据在一行中显示

例如: 查询每个员工的薪资和部门最高薪资

- group by 只能显示分组数据和聚集结果

2. rank()/dense_rank() over(partition by ...order by ...)

--查询每个部门工资最高的雇员的信息
select * from (select emp.*,rank() over(partition by deptno order by sal desc) maxsalno from emp) where maxsalno=1

3. min()/max() over(partition by ...)

--查询每位雇员信息的同时算出雇员工资与所属部门最高/最低员工工资的差额
select a.*,sal-maxsal diff from (select emp.*,max(sal) over(partition by deptno) maxsal from emp) aEMPNO ENAME DEPTNO SAL DIFF
---------- -------------------- ---------- ---------- ----------
7782 CLARK 10 2450 -2550
7839 KING 10 5000 0
7934 MILLER 10 1300 -3700
7566 JONES 20 2975 -25
7902 FORD 20 3000 0
7876 ADAMS 20 1100 -1900
7369 SMITH 20 800 -2200
7788 SCOTT 20 3000 0
7521 WARD 30 1250 -1600
7844 TURNER 30 1500 -1350
7499 ALLEN 30 1600 -1250

EMPNO ENAME DEPTNO SAL DIFF
---------- -------------------- ---------- ---------- ----------
7900 JAMES 30 950 -1900
7698 BLAKE 30 2850 0
7654 MARTIN 30 1250 -1600

14 rows selected.

select a.empno,a.ename,a.deptno,a.sal,maxsal,sal-maxsal diff from (select emp.*,max(sal) over(partition by deptno order by sal) maxsal from emp) a

EMPNO ENAME DEPTNO SAL MAXSAL DIFF
---------- -------------------- ---------- ---------- ---------- ----------
7934 MILLER 10 1300 1300 0
7782 CLARK 10 2450 2450 0
7839 KING 10 5000 5000 0
7369 SMITH 20 800 800 0
7876 ADAMS 20 1100 1100 0
7566 JONES 20 2975 2975 0
7788 SCOTT 20 3000 3000 0
7902 FORD 20 3000 3000 0
7900 JAMES 30 950 950 0
7654 MARTIN 30 1250 1250 0
7521 WARD 30 1250 1250 0

EMPNO ENAME DEPTNO SAL MAXSAL DIFF
---------- -------------------- ---------- ---------- ---------- ----------
7844 TURNER 30 1500 1500 0
7499 ALLEN 30 1600 1600 0
7698 BLAKE 30 2850 2850 0

14 rows selected.

*order by的作用

表示当前行中所在分组中相同排序序号的(max最大值)

select a.empno,a.ename,a.deptno,a.sal,sum(sal) over(partition by deptno order by sal) maxsal from emp a where deptno=20;

EMPNO ENAME DEPTNO SAL MAXSAL
---------- -------------------- ---------- ---------- ----------
7369 SMITH 20 800 800
7876 ADAMS 20 1100 1900
7566 JONES 20 2975 4875
7902 FORD 20 3000 10875 顺序号相同4 3000+3000+4875=10875
7788 SCOTT 20 3000 10875 顺序号相同4

* 有排序，当前行在分组内相同顺序号的行 进行求和

(4).lead()/lag() over(partition by ... order by ...)

-- 计算个人工资与比自己高一位/低一位工资的差额
select EMP.*,sal-lead(SAL,1,sal) over(partition by deptno order by sal) after,sal-lag(sal,1,sal) over(partition by deptno order by sal) before from EMP;select a.empno,a.ename,a.deptno,a.sal,sal-lead(SAL,1,sal) over(partition by deptno order by sal) after,sal-lag(sal,1,sal) over(partition by deptno order by sal) before from EMP;

EMPNO ENAME DEPTNO SAL AFTER BEFORE
---------- -------------------- ---------- ---------- ---------- ----------
7934 MILLER 10 1300 -1150 0
7782 CLARK 10 2450 -2550 1150
7839 KING 10 5000 0 2550
7369 SMITH 20 800 -300 0
7876 ADAMS 20 1100 -1875 300
7566 JONES 20 2975 -25 1875
7788 SCOTT 20 3000 0 25
7902 FORD 20 3000 0 0
7900 JAMES 30 950 -300 0
7654 MARTIN 30 1250 0 300
7521 WARD 30 1250 -250 0

EMPNO ENAME DEPTNO SAL AFTER BEFORE
---------- -------------------- ---------- ---------- ---------- ----------
7844 TURNER 30 1500 -100 250
7499 ALLEN 30 1600 -1250 100
7698 BLAKE 30 2850 0 1250

14 rows selected.

* lead(a,b,c) 表示当前行列a的下b行的值，如果没有则去值为c

lag(a,b,c) 表示当前行列a的上b行的值，如果没有则去值为c