[分析函数] 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