Oracle 窗口函數實戰：ROW_NUMBER、RANK、DENSE_RANK、LAG 詳解

語法通式

FUNCTION() OVER (PARTITION BY col1 ORDER BY col2)

• PARTITION BY：分組，各組內部獨立計算。
• ORDER BY：組內排序依據。

三大排序函數差異

LAG/LEAD 取前後行值

LAG(expr, offset, default) OVER (PARTITION BY ... ORDER BY ...)
-- offset：向前幾行（默認 1），default：超出分區返回值（默認 NULL）

典型用途：計算環比增長、檢測數據跳變。

實戰示例

SELECT deptno, ename, sal,
       ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) rn,
       RANK()       OVER (PARTITION BY deptno ORDER BY sal DESC) rk,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) dr,
       LAG(sal) OVER (PARTITION BY deptno ORDER BY sal DESC) prev_sal
FROM emp;

輸出將展示各部門薪資排序名次及上一行薪資，便於薪資結構分析。