前言

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

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

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

二、基本語(yǔ)法結(jié)構(gòu)

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

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

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

三、OVER()子句詳解

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

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

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

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

3. ROWS | RANGE：窗口范圍（行級(jí)窗口）

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

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

以下示例基于員工表employee，結(jié)構(gòu)如下：

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

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

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

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

• 結(jié)果：

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

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

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

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

• 結(jié)果：

  name	department	salary	rank_num
  李四	技術(shù)部	9000	1
  王五	技術(shù)部	9000	1	（與李四并列第 1）
  張三	技術(shù)部	8000	3	（跳號(hào)，直接第 3）
  錢七	市場(chǎng)部	8500	1
  趙六	市場(chǎng)部	7000	2

（3）DENSE_RANK()：無(wú)跳號(hào)的排名

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

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

• 結(jié)果：

  name	department	salary	dense_rank_num
  李四	技術(shù)部	9000	1
  王五	技術(shù)部	9000	1
  張三	技術(shù)部	8000	2	（不跳號(hào)，第 2）
  錢七	市場(chǎng)部	8500	1
  趙六	市場(chǎng)部	7000	2

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

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

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

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

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

• 結(jié)果：

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

（2）累計(jì)聚合（帶ORDER BY和范圍）

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

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

• 結(jié)果（技術(shù)部）：

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

3. 分析函數(shù)（獲取前后行數(shù)據(jù)）

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

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

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

• 結(jié)果（技術(shù)部）：

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

（2）LEAD(列名, n)：獲取當(dāng)前行的后 n 行數(shù)據(jù)

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

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

• 結(jié)果（技術(shù)部）：

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

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

六、注意事項(xiàng)

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

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

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

最新評(píng)論