SQL 開窗函數（Window Function）是一種強大的分析工具，它能在保留原有數據行的基礎上，對 "窗口"（指定范圍的行集合）進行聚合、排名或分析計算，解決了傳統GROUP BY聚合會合并行的局限性。

一、開窗函數的核心特點

• 不合并行：與GROUP BY不同，開窗函數計算后會保留所有原始行，只是為每行附加一個計算結果。
• 窗口定義：通過OVER()子句定義 "窗口"（即計算范圍），可按條件分區、排序或限定行范圍。
• 適用場景：排名（如 top N）、累計計算（如累計求和）、移動分析（如近 3 天平均值）、前后行數據獲取等。

二、基本語法結構

開窗函數的通用語法：

函數名(參數) OVER ([PARTITION BY 分區列1, 分區列2...]  -- 可選：按列分組，每組獨立計算[ORDER BY 排序列1 [ASC|DESC], ...]  -- 可選：分區內的排序方式[ROWS | RANGE 窗口范圍]  -- 可選：定義窗口的具體行范圍（行級窗口）
)

• 函數名：可以是排名函數（RANK()、ROW_NUMBER()等）、聚合函數（SUM()、AVG()等）或分析函數（LAG()、LEAD()等）。
• OVER()子句：核心部分，用于定義 "窗口" 的規則。

三、OVER()子句詳解

1.?PARTITION BY：分區（分組）

• 作用：將數據按指定列分成多個獨立的 "分區"，開窗函數在每個分區內單獨計算（類似GROUP BY的分組，但不合并行）。
• 示例：按 "部門" 分區，每個部門內部獨立計算工資排名。

2.?ORDER BY：分區內排序

• 作用：指定分區內的行排序規則，影響排名函數的結果和窗口范圍的界定。
• 注意：若不指定PARTITION BY，則全表視為一個分區，按ORDER BY整體排序。

3.?ROWS | RANGE：窗口范圍（行級窗口）

• 作用：在分區內，進一步限定參與計算的行范圍（如 "當前行 + 前 2 行 + 后 1 行"）。
• 關鍵字：
  • ROWS：基于物理行數界定范圍（如 "前 2 行"）。
  • RANGE：基于值的邏輯范圍界定（如 "值在當前行 ±10 以內的行"），僅支持數值 / 日期類型。
• 常用范圍表達式：
  • UNBOUNDED PRECEDING：分區的第一行
  • CURRENT ROW：當前行
  • n PRECEDING：當前行之前的第 n 行
  • n FOLLOWING：當前行之后的第 n 行
  • 組合示例：ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（當前行 + 前 2 行 + 后 1 行）

四、常用開窗函數分類及示例

以下示例基于員工表employee，結構如下：

1. 排名函數（用于生成排名）

（1）ROW_NUMBER()：生成唯一序號

• 功能：為分區內的每行分配一個連續的唯一序號（即使值相同，序號也不同）。
• 示例：按部門分區，按工資降序排名（工資相同則按入職時間升序）：

SELECT name, department, salary,ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, hire_date ASC) AS row_num
FROM employee;

• 結果：

  name	department	salary	row_num
  李四	技術部	9000	1	（同工資，入職早排前）
  王五	技術部	9000	2
  張三	技術部	8000	3
  錢七	市場部	8500	1
  趙六	市場部	7000	2

（2）RANK()：帶跳號的排名

• 功能：相同值排名相同，后續排名會 "跳號"（如兩個第 1 名，下一個是第 3 名）。
• 示例：按部門分區，按工資降序排名：

SELECT name, department, salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM employee;

• 結果：

  name	department	salary	rank_num
  李四	技術部	9000	1
  王五	技術部	9000	1	（與李四并列第 1）
  張三	技術部	8000	3	（跳號，直接第 3）
  錢七	市場部	8500	1
  趙六	市場部	7000	2

（3）DENSE_RANK()：無跳號的排名

• 功能：相同值排名相同，后續排名不跳號（如兩個第 1 名，下一個是第 2 名）。
• 示例：按部門分區，按工資降序排名：

SELECT name, department, salary,DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM employee;

• 結果：

  name	department	salary	dense_rank_num
  李四	技術部	9000	1
  王五	技術部	9000	1
  張三	技術部	8000	2	（不跳號，第 2）
  錢七	市場部	8500	1
  趙六	市場部	7000	2

2. 聚合開窗函數（聚合函數 +OVER()）

將SUM()、AVG()、COUNT()等聚合函數與OVER()結合，為每行計算所在窗口的聚合結果。

（1）全分區聚合（無ORDER BY和范圍）

• 功能：計算整個分區的聚合值（每行的結果相同）。
• 示例：計算每個部門的平均工資，附加到每行：

SELECT name, department, salary,AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employee;

• 結果：

  name	department	salary	dept_avg_salary
  張三	技術部	8000	8666.67	（(8000+9000+9000)/3）
  李四	技術部	9000	8666.67
  王五	技術部	9000	8666.67

（2）累計聚合（帶ORDER BY和范圍）

• 功能：按排序順序計算 "累計" 聚合值（如累計求和、累計平均值）。
• 示例：按部門分區，按入職時間升序，計算累計工資總和：

SELECT name, department, hire_date,salary,SUM(salary) OVER (PARTITION BY department ORDER BY hire_date ASCROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 從第一行到當前行) AS cumulative_salary
FROM employee;

• 結果（技術部）：

  name	department	hire_date	salary	cumulative_salary
  王五	技術部	2018-05-10	9000	9000	（第一行，累計 = 自身）
  李四	技術部	2019-03-20	9000	18000	（累計 = 9000+9000）
  張三	技術部	2020-01-15	8000	26000	（累計 = 9000+9000+8000）

3. 分析函數（獲取前后行數據）

（1）LAG(列名, n)：獲取當前行的前 n 行數據

• 功能：返回當前行之前第 n 行的指定列值（默認 n=1）。
• 示例：獲取每個部門中，當前員工的前一位入職員工的工資：

SELECT name, department, hire_date,salary,LAG(salary, 1) OVER (PARTITION BY department ORDER BY hire_date ASC) AS prev_emp_salary
FROM employee;

• 結果（技術部）：

  name	department	hire_date	salary	prev_emp_salary
  王五	技術部	2018-05-10	9000	NULL	（第一行，無前一行）
  李四	技術部	2019-03-20	9000	9000	（前一行是王五的工資）
  張三	技術部	2020-01-15	8000	9000	（前一行是李四的工資）

（2）LEAD(列名, n)：獲取當前行的后 n 行數據

• 功能：返回當前行之后第 n 行的指定列值（默認 n=1）。
• 示例：獲取每個部門中，當前員工的后一位入職員工的工資：

SELECT name, department, hire_date,salary,LEAD(salary, 1) OVER (PARTITION BY department ORDER BY hire_date ASC) AS next_emp_salary
FROM employee;

• 結果（技術部）：

  name	department	hire_date	salary	next_emp_salary
  王五	技術部	2018-05-10	9000	9000	（后一行是李四的工資）
  李四	技術部	2019-03-20	9000	8000	（后一行是張三的工資）
  張三	技術部	2020-01-15	8000	NULL	（最后一行，無后一行）

五、開窗函數與GROUP BY的區別

六、注意事項

1. 排序影響：ORDER BY在開窗函數中不僅影響排名，還會影響窗口范圍的界定（如累計計算）。
2. 性能考量：復雜的窗口范圍（如RANGE）可能導致性能下降，大表建議優先用ROWS。
3. 數據庫支持：主流數據庫（MySQL 8.0+、PostgreSQL、SQL Server、Oracle）均支持開窗函數，但部分細節可能有差異。

通過上述講解，可掌握開窗函數的核心語法和應用場景。實際使用時，需根據業務需求靈活組合PARTITION BY、ORDER BY和窗口范圍，實現復雜的數據分析。