[分析函數(shù)] over partition by

1. over partition by 和 group by的區(qū)別

- over partition by 可以將匯總數(shù)據(jù)和源數(shù)據(jù)在一行中顯示

例如: 查詢每個員工的薪資和部門最高薪資

- group by 只能顯示分組數(shù)據(jù)和聚集結(jié)果

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

* 有排序，當前行在分組內(nèi)相同順序號的行 進行求和

(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