目錄

什么是窗口函數？

基本語法結構?

為什么要用窗口函數？

常見的窗口函數分類

1?? 排名類函數

2?? 聚合類函數（不影響原始行）

3?? 值訪問函數

窗口范圍說明（ROWS / RANGE）

---

什么是窗口函數？

窗口函數是一類 SQL 函數，在不分組的情況下，可以對查詢結果中的某一“窗口”范圍內的數據進行計算。

窗口函數 = 能在每一行數據上“看見”其他相關行的函數。

它允許你在不聚合（不合并行）的前提下，對一組相關行進行計算，并把結果加回到原來的每一行上。

不同于聚合函數（如 SUM, AVG），窗口函數不會壓縮行，而是為每一行返回一個計算值。

基本語法結構?

<窗口函數>([參數]) OVER ([PARTITION BY 子句][ORDER BY 子句][ROWS 或 RANGE 子句]
)函數名(...) OVER (PARTITION BY ...   -- 按誰分組（可選）ORDER BY ...       -- 按什么順序（常用）ROWS BETWEEN ...   -- 控制范圍（高級用法）
)

• PARTITION BY: 把數據分組，每組內獨立執行函數（類似 GROUP BY，但不合并行）。

• ORDER BY: 確定組內數據順序。

• ROWS BETWEEN: 精確控制窗口范圍（例如：過去3行）。

舉例：

SELECT department_id,employee_id,salary,RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

為什么要用窗口函數？

在傳統的 SQL 聚合函數（如 SUM, AVG, COUNT）中，如果你寫：

SELECT person_id, SUM(weight)
FROM Queue
GROUP BY person_id;

你得到的是每個 person_id 的總重，但你沒法看到其他人的情況 —— 一條記錄一條記錄獨立計算。

而窗口函數就像給每一行配了一個“望遠鏡”，能看到前面的、后面的或整組內的數據，然后基于這些數據算出“每一行自己的視角”。

舉個通俗例子（排隊上車）：

有一張表：

turn	person_name	weight
1	Alice	250
2	Bob	300
3	Charlie	200
4	David	400

你想知道：每個人上車時，前面所有人（包括自己）累計多重了？

👉 用窗口函數就可以這樣寫：

SELECT person_name,weight,SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
FROM Queue;

輸出結果：

person_name	weight	cumulative_weight
Alice	250	250
Bob	300	550 (250+300)
Charlie	200	750 (250+300+200)
David	400	1150

🚀 神奇的是，你沒有把這些行合并，而是在每行里加上了“前面和自己的累積情況”。

---

常見的窗口函數分類

1?? 排名類函數

函數	描述
ROW_NUMBER()	每一組數據中按順序分配唯一行號
RANK()	同分并列，跳躍排名（如：1,1,3）
DENSE_RANK()	同分不跳（如：1,1,2）
NTILE(n)	將結果分為 n 個桶，每行給出所屬桶編號

1.ROW_NUMBER()

意義：按順序為每行分配一個“唯一編號”。
名稱：ROW_NUMBER = 行號。

語法示例：

ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num

用途：

• 對每個部門中員工薪資進行唯一編號（常用于分頁、去重等）

2.RANK()

意義：返回排名，相同值并列排名，后續名次跳躍。
名稱：RANK = 排名。

語法示例：

RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank

salary	RANK
1000	1
1000	1
900	3

3.DENSE_RANK()

意義：與 RANK() 類似，但排名連續不跳躍。
名稱：DENSE_RANK = 密集排名。

語法示例：

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank

salary	DENSE_RANK
1000	1
1000	1
900	2

4. NTILE(n)

意義：將數據平均分成 n 個桶，每行返回桶編號。
名稱：NTILE = "N Tile"，即“分桶”。

語法示例：

NTILE(4) OVER (ORDER BY salary DESC) AS quartile

用途：

• 按工資水平將員工劃分為四個檔次（四分位分析）

---

2?? 聚合類函數（不影響原始行）

函數	描述
SUM(), AVG(), MAX(), MIN()	聚合函數 + 窗口：在窗口范圍內計算
COUNT()	窗口內的行數統計

5. SUM(expr)、AVG(expr)、MAX(expr)、MIN(expr)

意義：在窗口內執行聚合計算，但不影響原始行展示。
名稱：

• SUM = 總和

• AVG = 平均

• MAX = 最大值

• MIN = 最小值

語法示例：

SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_total

用途：

• 滾動匯總、組內對比等。

---

3?? 值訪問函數

函數	描述
LAG(expr, n, default)	返回當前行前第 n 行的值
LEAD(expr, n, default)	返回當前行后第 n 行的值
FIRST_VALUE(expr)	窗口中的第一個值
LAST_VALUE(expr)	窗口中的最后一個值

6. LAG(expr, offset, default)

意義：返回當前行的前 N 行的值。
名稱：LAG = 滯后。

語法示例：

LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_salary

用途：

• 分析趨勢、比較環比。

7. LEAD(expr, offset, default)

意義：返回當前行的后 N 行的值。
名稱：LEAD = 領先。

語法示例：

LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_salary

用途：

• 預測未來、構建時間序列對比。

?8. FIRST_VALUE(expr)

意義：返回窗口中按排序后第一行的值。
名稱：FIRST_VALUE = 第一個值。

語法示例：

FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_salary

9. LAST_VALUE(expr)

意義：返回窗口中最后一行的值。
名稱：LAST_VALUE = 最后一個值。

注意： LAST_VALUE 需要配合 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 才能獲取整個分區最后一行值。

LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_salary

---

窗口范圍說明（ROWS / RANGE）

ROWS 是基于物理行號

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

?RANGE 是基于值范圍

RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW

常見用途總結

場景	推薦函數
排名	ROW_NUMBER, RANK, DENSE_RANK
對比趨勢	LAG, LEAD, FIRST_VALUE, LAST_VALUE
滾動匯總	SUM, AVG, COUNT + ROWS BETWEEN
分段統計	NTILE
時間窗口	RANGE BETWEEN