前言

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

一、開窗函數(shù)的核心特點

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

二、基本語法結構

開窗函數(shù)的通用語法：

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

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

三、OVER()子句詳解

1. PARTITION BY：分區(qū)（分組）

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

2. ORDER BY：分區(qū)內(nèi)排序

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

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

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

四、常用開窗函數(shù)分類及示例

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

1. 排名函數(shù)（用于生成排名）

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

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

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()：帶跳號的排名

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

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()：無跳號的排名

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

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. 聚合開窗函數(shù)（聚合函數(shù) +OVER()）

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

（1）全分區(qū)聚合（無ORDER BY和范圍）

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

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和范圍）

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

SELECT 
  name, 
  department, 
  hire_date,
  salary,
  SUM(salary) OVER (
    PARTITION BY department 
    ORDER BY hire_date ASC
    ROWS 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. 分析函數(shù)（獲取前后行數(shù)據(jù)）

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

• 功能：返回當前行之前第 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 行數(shù)據(jù)

• 功能：返回當前行之后第 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	（最后一行，無后一行）

五、開窗函數(shù)與GROUP BY的區(qū)別

六、注意事項

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

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

到此這篇關于Mysql開窗函數(shù)的文章就介紹到這了,更多相關Mysql開窗函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！