分析函數二 (ROW_NUMBER、DENSE_RANK、KEEP、FIRST_VALUE、LEAD、CUME_DIST、NTILE、RATIO_TO_REPORT) (DML 七)

※ROW_NUMBER

顯示行號

SELECT
    deptno,
    sal,
    ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) rn1, 
    ROW_NUMBER() OVER(ORDER BY sal) rn2
FROM EMP ORDER BY deptno;

※結果：

可以看出RN1是照部門排列的，因為有PARTITION BY，而RN2很亂，所以將最外面的ORDER BY 改成sal後，結果如下：

※RANK、DENSE_RANK

按照排序的序號

SELECT 
    RANK() OVER(PARTITION BY deptno ORDER BY sal) r, 
    DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal) d, 
    sal,
    deptno 
FROM EMP WHERE deptno = 30;

※結果：

※第四行可以看出RANK會跳號，而DENSE_RANK不會跳號

※KEEP

必需要搭配GROUP BY使用

SELECT 
    -- MAX(sal) OVER(ORDER BY sal) r, 
    -- MIN(sal) OVER(ORDER BY sal) d, 
    deptno,
    MAX(sal) KEEP(DENSE_RANK FIRST ORDER BY sal) max, 
    MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY sal) min,
    MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) maxk, 
    MIN(sal) KEEP(DENSE_RANK LAST ORDER BY sal) mink
FROM EMP
GROUP BY deptno;

※結果：

※註解的部分不能執行，因為不能有GROUP BY

※FIRST_VALUE、LAST_VALUE

取出第一／最後的記錄

SELECT
    sal,
    FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) first, 
    LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) last
FROM EMP
WHERE deptno = 20;

※結果：

※看左邊的SAL再對照右邊的，可以看出FIRST因為是第一筆，所以都是800

※LEAD、LAG

將資料往前／往後移動
LEAD是領導的意思，以資料為單位，所以會將後面的資料往上帶，下面變null(預設)
LAG是拖的意思，以資料為單位，所以會將上面的資料往下帶，上面變null(預設)

SELECT
    sal,
    LAG(sal) OVER(PARTITION BY deptno ORDER BY sal) lag1,
    LAG(sal, 1) OVER(PARTITION BY deptno ORDER BY sal) lag2,
    LAG(sal, 2) OVER(PARTITION BY deptno ORDER BY sal) lag3,
    LAG(sal, 2, -99) OVER(PARTITION BY deptno ORDER BY sal) lag4,
    LEAD(sal, 2) OVER(PARTITION BY deptno ORDER BY sal) lead
FROM EMP
WHERE deptno = 30;

※結果：

※LAG(sal)不加參數，等同LAG(sal, 1)，1表示往下一行

※加第3個參數，可將移動的變成你加的第3個參數，不加就會顯示null，型態要和第2個參數一樣

※CUME_DIST

針對位置的百分比

SELECT
    deptno,
    sal,
    ROUND(CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal), 2) c
FROM emp;

※結果：

※以20部門為例，總共有五筆，所以每筆是1/5=0.2，但要注意薪水一樣，全部都會以最後的為主

※NTILE

NTILE要看結果比較好說明

SELECT
    deptno,
    sal,
    -- SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) sum,
    NTILE(1) OVER(PARTITION BY deptno ORDER BY sal) n1,
    NTILE(2) OVER(PARTITION BY deptno ORDER BY sal) n2,
    NTILE(3) OVER(PARTITION BY deptno ORDER BY sal) n3,
    NTILE(4) OVER(PARTITION BY deptno ORDER BY sal) n4,
    NTILE(5) OVER(PARTITION BY deptno ORDER BY sal) n5,
    NTILE(6) OVER(PARTITION BY deptno ORDER BY sal) n6,
    NTILE(7) OVER(PARTITION BY deptno ORDER BY sal) n7
FROM emp;

※結果：

※以30部門的N3為例，30部門有6筆，6/3=2，所以2筆資料為一組

20部門的N3則是5/3四捨五入後=2，所以也是2筆資料為一組

※RATIO_TO_REPORT

相對於欄位值的百分比

SELECT deptno, count(deptno) c FROM emp GROUP BY deptno ORDER BY deptno;
    
SELECT
    deptno,
    sum(sal) sum,
    ROUND(RATIO_TO_REPORT(sum(sal)) OVER(), 2) rs,
    ROUND(avg(sal), 2) avg,
    ROUND(RATIO_TO_REPORT(avg(sal)) OVER(), 2) ra
FROM emp
GROUP BY deptno
ORDER BY deptno;

※結果：

※第一張圖，可以看到10部門有3筆；20部門有5筆；30部門有6筆

※3個部門全部的薪水是 8750 + 10875 + 9400 = 29025
10部門的RS是用8750 / 29025算出來的，其他部門依此類推

※3個部門全部的平均薪水是2916.67 + 2175 + 1566.67 = 6658.34
10部門的RA是用 2916.67 / 6658.34算出來的，其他部門依此類推