窗口函數 (SQL Window Functions)
這篇 SQL Window Functions 教學將帶你認識窗口函數（Window Functions）的基本概念與用法。窗口函數可以在不改變查詢結果列數的情況下，對每一列進行計算，例如排名、編號、取前後列的值等。與聚合函數不同的是，窗口函數不會將多列合併為一列，而是保留每一列的原始資料，並在旁邊新增計算結果。
窗口函數常用於排名、分組排序、計算累計值、取得前後列資料等場景。搭配 OVER() 子句來定義「窗口」的範圍，可以指定分組（PARTITION BY）與排序（ORDER BY）方式。常見的窗口函數包括 ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG() 等。
Window Functions 語法 (SQL Window Functions Syntax)
SELECT column_name,
window_function() OVER (
PARTITION BY column_name
ORDER BY column_name
) AS alias
FROM table_name;說明：
| 關鍵字 | 說明 |
|---|---|
| window_function() | 窗口函數名稱，例如 ROW_NUMBER()、RANK()、LEAD() 等 |
| OVER() | 定義窗口的範圍，是窗口函數必備的子句 |
| PARTITION BY | （選填）指定分組欄位，類似 GROUP BY，但不會合併資料列 |
| ORDER BY | （選填）指定窗口內的排序方式 |
Window Functions 用法 (Example)
假設我們有一個員工資料表 employees 如下（若需了解如何建立資料表，請參考 CREATE TABLE 教學）：
| Name | Department | Salary |
|---|---|---|
| 張一 | 業務部 | 35000 |
| 王二 | 資訊部 | 42000 |
| 李三 | 業務部 | 38000 |
| 趙四 | 資訊部 | 45000 |
| 陳五 | 人資部 | 40000 |
| 林六 | 業務部 | 36000 |
| 黃七 | 資訊部 | 48000 |
ROW_NUMBER() — 依序編號
ROW_NUMBER() 會為每一列依照指定的排序方式，從 1 開始依序給予不重複的編號。即使有相同的值，編號也不會重複。
SELECT Name, Department, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS row_num
FROM employees;查詢結果如下：
| Name | Department | Salary | row_num |
|---|---|---|---|
| 黃七 | 資訊部 | 48000 | 1 |
| 趙四 | 資訊部 | 45000 | 2 |
| 王二 | 資訊部 | 42000 | 3 |
| 陳五 | 人資部 | 40000 | 4 |
| 李三 | 業務部 | 38000 | 5 |
| 林六 | 業務部 | 36000 | 6 |
| 張一 | 業務部 | 35000 | 7 |
RANK() — 排名（有間隔）
RANK() 會依照指定的排序方式給予排名。如果有相同的值，會給予相同的排名，但下一個排名會跳過。例如有兩個第 1 名，下一個就是第 3 名（跳過第 2 名）。
為了更清楚地展示 RANK() 的特性，假設員工資料中王二與陳五的薪資都是 42000：
SELECT Name, Department, Salary,
RANK() OVER (ORDER BY Salary DESC) AS rank_num
FROM employees;查詢結果如下：
| Name | Department | Salary | rank_num |
|---|---|---|---|
| 黃七 | 資訊部 | 48000 | 1 |
| 趙四 | 資訊部 | 45000 | 2 |
| 王二 | 資訊部 | 42000 | 3 |
| 陳五 | 人資部 | 42000 | 3 |
| 李三 | 業務部 | 38000 | 5 |
| 林六 | 業務部 | 36000 | 6 |
| 張一 | 業務部 | 35000 | 7 |
可以看到王二與陳五薪資相同，都是第 3 名，而下一個排名直接跳到第 5 名（跳過第 4 名）。
DENSE_RANK() — 排名（無間隔）
DENSE_RANK() 與 RANK() 類似，也會對相同值給予相同排名，但不同的是，下一個排名不會跳過，而是連續的。
SELECT Name, Department, Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS dense_rank_num
FROM employees;同樣假設王二與陳五的薪資都是 42000，查詢結果如下：
| Name | Department | Salary | dense_rank_num |
|---|---|---|---|
| 黃七 | 資訊部 | 48000 | 1 |
| 趙四 | 資訊部 | 45000 | 2 |
| 王二 | 資訊部 | 42000 | 3 |
| 陳五 | 人資部 | 42000 | 3 |
| 李三 | 業務部 | 38000 | 4 |
| 林六 | 業務部 | 36000 | 5 |
| 張一 | 業務部 | 35000 | 6 |
與 RANK() 相比，DENSE_RANK() 在王二與陳五（都是第 3 名）之後，下一個排名是第 4 名而不是第 5 名。
ROW_NUMBER vs RANK vs DENSE_RANK 比較
以下整理三個排名函數的差異：
| 函數 | 相同值的處理 | 排名是否連續 |
|---|---|---|
| ROW_NUMBER() | 即使值相同，編號也不重複 | 連續（1, 2, 3, 4…） |
| RANK() | 相同值給予相同排名 | 不連續，會跳過（1, 2, 2, 4…） |
| DENSE_RANK() | 相同值給予相同排名 | 連續，不跳過（1, 2, 2, 3…） |
LAG() — 取前一列的值
LAG() 可以取得目前列的前 N 列（預設為前 1 列）的值，常用於比較當前資料與前一筆資料的差異，例如計算薪資差距、月營收變化等。
SELECT Name, Department, Salary,
LAG(Salary, 1) OVER (ORDER BY Salary DESC) AS prev_salary
FROM employees;查詢結果如下：
| Name | Department | Salary | prev_salary |
|---|---|---|---|
| 黃七 | 資訊部 | 48000 | NULL |
| 趙四 | 資訊部 | 45000 | 48000 |
| 王二 | 資訊部 | 42000 | 45000 |
| 陳五 | 人資部 | 40000 | 42000 |
| 李三 | 業務部 | 38000 | 40000 |
| 林六 | 業務部 | 36000 | 38000 |
| 張一 | 業務部 | 35000 | 36000 |
第一列因為沒有前一列，所以 prev_salary 為 NULL。每一列的 prev_salary 就是依照薪資由高到低排序後，前一筆員工的薪資。
LEAD() — 取後一列的值
LEAD() 與 LAG() 相反，可以取得目前列的後 N 列（預設為後 1 列）的值。常用於預測或比較下一筆資料。
SELECT Name, Department, Salary,
LEAD(Salary, 1) OVER (ORDER BY Salary DESC) AS next_salary
FROM employees;查詢結果如下：
| Name | Department | Salary | next_salary |
|---|---|---|---|
| 黃七 | 資訊部 | 48000 | 45000 |
| 趙四 | 資訊部 | 45000 | 42000 |
| 王二 | 資訊部 | 42000 | 40000 |
| 陳五 | 人資部 | 40000 | 38000 |
| 李三 | 業務部 | 38000 | 36000 |
| 林六 | 業務部 | 36000 | 35000 |
| 張一 | 業務部 | 35000 | NULL |
最後一列因為沒有後一列，所以 next_salary 為 NULL。
PARTITION BY 搭配窗口函數 — 分組內排名
PARTITION BY 可以將資料依照指定欄位分組，讓窗口函數在每個分組內分別計算。這類似 GROUP BY 的分組概念，但不會將資料合併。
例如，依部門分組，在每個部門內依薪資由高到低排名：
SELECT Name, Department, Salary,
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS dept_rank
FROM employees;查詢結果如下：
| Name | Department | Salary | dept_rank |
|---|---|---|---|
| 李三 | 業務部 | 38000 | 1 |
| 林六 | 業務部 | 36000 | 2 |
| 張一 | 業務部 | 35000 | 3 |
| 陳五 | 人資部 | 40000 | 1 |
| 黃七 | 資訊部 | 48000 | 1 |
| 趙四 | 資訊部 | 45000 | 2 |
| 王二 | 資訊部 | 42000 | 3 |
可以看到每個部門的排名都是從 1 開始重新計算，這就是 PARTITION BY 的作用。
搭配 LAG() 計算薪資差距
結合 PARTITION BY 與 LAG()，可以在每個部門內計算員工與前一位員工的薪資差距：
SELECT Name, Department, Salary,
LAG(Salary, 1) OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS prev_salary,
Salary - LAG(Salary, 1) OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS salary_diff
FROM employees;查詢結果如下：
| Name | Department | Salary | prev_salary | salary_diff |
|---|---|---|---|---|
| 李三 | 業務部 | 38000 | NULL | NULL |
| 林六 | 業務部 | 36000 | 38000 | -2000 |
| 張一 | 業務部 | 35000 | 36000 | -1000 |
| 陳五 | 人資部 | 40000 | NULL | NULL |
| 黃七 | 資訊部 | 48000 | NULL | NULL |
| 趙四 | 資訊部 | 45000 | 48000 | -3000 |
| 王二 | 資訊部 | 42000 | 45000 | -3000 |
每個部門的第一位員工因為沒有前一列，所以 prev_salary 和 salary_diff 都是 NULL。這個查詢可以清楚看出每個部門內員工之間的薪資差距。
常見窗口函數一覽
除了本篇介紹的函數之外，SQL 還提供許多其他窗口函數，以下為常見的窗口函數整理：
| 函數 | 說明 |
|---|---|
| ROW_NUMBER() | 依序給予不重複的列編號 |
| RANK() | 排名，相同值同名次，有間隔 |
| DENSE_RANK() | 排名，相同值同名次，無間隔 |
| LAG(col, n) | 取前 n 列的值（預設 n=1） |
| LEAD(col, n) | 取後 n 列的值（預設 n=1） |
| FIRST_VALUE(col) | 取窗口內第一列的值 |
| LAST_VALUE(col) | 取窗口內最後一列的值 |
| NTILE(n) | 將資料平均分成 n 組 |
| SUM() OVER() | 計算累計加總 |
| AVG() OVER() | 計算移動平均 |
窗口函數是 SQL 進階查詢中非常強大的工具，能夠在不改變結果集的情況下進行複雜的計算。搭配 PARTITION BY 和 ORDER BY，可以靈活地對資料進行分組排名、前後比較等操作。如果你需要對分組結果進行篩選，可以參閱 SQL HAVING 教學。
延伸閱讀
- SQL GROUP BY
- SQL HAVING
- SQL 聚合函數
- SQL SELECT
- SQL WHERE
- SQL ORDER BY
- SQL Subquery
- SQL JOIN
- SQL CASE
- SQL DISTINCT
- SQL AS
