想在SQL里看得到每条数据的明细，又能算出分组后的排名、平均值等等统计结果，这要用到窗口函数。SQL窗口函数就是干这个的。它不用像GROUP BY那样把数据合并成一行，能在保留原始数据的同时，对指定 “窗口”（分组或排序后的数据集）进行计算，给每行 “贴” 上对应的统计标签。SQL窗口函数，又称开窗函数，也有数据库中称为分析函数。在此，我们不打算详细拆解SQL窗口函数，只准备按功能对SQL窗口函数进行类别细分，让SQL窗口函数容易记一点。SQL窗口函数具体可分为排名、分布、聚合、取值、分桶等5大类共12小类：想给数据排个名？用排名类；想知道某数值在整体里占多少？用分布类；想同时看明细和总和/平均值？用聚合类；想拿当前行和前几行、后几行对比？用取值类；想把数据分成几档（如：“优秀/普通”）？用分桶类。下面详细介绍12小类（不详细展开）都有哪？仅供参考：

一、排名类窗口函数

对数据进行排序并分配排名序号，用于明确数据在有序集合中的位置关系。

1、序号排名（ROW_NUMBER()）

为每行分配唯一序号，即使值相同也不会重复（如：1,2,3,4）。

• 函数：ROW_NUMBER()

• 语法：

  ROW_NUMBER() OVER (
    [PARTITION BY 分组列]  -- 可选，按列分组后分别排名
    ORDER BY 排序列 [ASC/DESC]  -- 必须，指定排序规则
  )
  

• 应用场景：需要严格排序且不允许并列的场景，如：“销售业绩TOP10”、“考试成绩唯一排名”等等。

• 示例：
  为各部门员工按薪资降序分配唯一排名：

  SELECT 
    dept, name, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_rank
  FROM employees;
  

  结果（部分）：

  dept	name	salary	row_rank
  技术部	张三	15000	1
  技术部	李四	15000	2

  注：同薪资但序号不同。

2、间隙排名（RANK()）

相同值排名相同，但后续排名会跳过中间间隙（如：1,1,3,4）。

• 函数：RANK()

• 语法：

  RANK() OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列 [ASC/DESC]
  )
  

• 应用场景：允许并列但需保持整体排名逻辑的场景，如：“运动会比赛排名”（若2人并列第1，下一人为第3）。

• 示例：
  为各部门员工按薪资降序排名（允许并列，后续排名跳跃）：

  SELECT 
    dept, name, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
  FROM employees;
  

  结果（部分）：

  dept	name	salary	rank
  技术部	张三	15000	1
  技术部	李四	15000	1
  技术部	王五	12000	3

  注：张三、李四同薪资并列第1；王五跳过第2名。

3、连续排名（DENSE_RANK()）

相同值排名相同，后续排名连续（如：1,1,2,3）。

• 函数：DENSE_RANK()

• 语法：

  DENSE_RANK() OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列 [ASC/DESC]
  )
  

• 应用场景：需要体现并列关系且保持排名连续性的场景，如：“资格赛晋级排名”（前2名并列时，下一名为第2而非第3）。

• 示例：
  为各部门员工按薪资降序排名（允许并列，后续排名连续）：

  SELECT 
    dept, name, salary,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rank
  FROM employees;
  

  结果（部分）：

  dept	name	salary	dense_rank
  技术部	张三	15000	1
  技术部	李四	15000	1
  技术部	王五	12000	2

  注：张三、李四同薪资并列第1；王五连续排名第2。

二、分布类窗口函数

分析数据在整体中的分布比例或位置，用于评估数据的相对重要性。

1、累积分布（CUME_DIST()）

计算当前行值在窗口内的累积分布比例（范围0~1），公式为“小于等于当前值的行数 / 总行数”。

• 函数：CUME_DIST()

• 语法：

  CUME_DIST() OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列 [ASC/DESC]
  )
  

• 应用场景：分析“某值以下的数据占比”，如：“薪资≤10k的员工占比”、“成绩≤80分的学生占比”。

• 示例：
  计算各部门员工薪资的累积分布比例：

  SELECT 
    dept, name, salary,
    CUME_DIST() OVER (PARTITION BY dept ORDER BY salary) AS cume_dist
  FROM employees;
  

  结果（部分）：

  dept	name	salary	cume_dist	说明(技术部共5人)
  技术部	赵六	8000	0.2	薪资≤8k的员工占20%
  技术部	王五	12000	0.6	薪资≤12k的员工占60%

2、百分比排名（PERCENT_RANK()）

计算当前行排名在窗口内的百分比位置（范围0~1），公式为：“（当前行排名-1）/（总行数-1）”。

• 函数：PERCENT_RANK()

• 语法：

  PERCENT_RANK() OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列 [ASC/DESC]
  )
  

• 应用场景：评估数据在整体中的相对位置，如：“某学生成绩在年级的百分比排名”、“某产品销量在全品类中的百分比位置”。

• 示例：
  计算各部门员工薪资的百分比排名：

  SELECT 
    dept, name, salary,
    PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary) AS percent_rank
  FROM employees;
  

  结果（部分）：

  dept	name	salary	percent_rank	说明（技术部共5人）
  技术部	赵六	8000	0.0	最低薪资，排名0%
  技术部	王五	12000	0.5	中间位置，排名50%

3、占比（函数）分析（RATIO_TO_REPORT()）

计算当前行值占窗口内总和的比例（范围0~1），即：“当前值 / 总和”。MySQL 8.0 + 不支持RATIO_TO_REPORT。

• 函数：RATIO_TO_REPORT(目标列)

• 语法：

  RATIO_TO_REPORT(目标列) OVER (
    [PARTITION BY 分组列]  -- 可选，按组计算占比
  )
  

• 应用场景：分析“某部分占整体的比例”，如：“各产品销售额占总销售额的比例”、“各部门成本占总成本的比例”。

• 示例：
  计算各产品销售额占总销售额的比例（按地区分组）：

  SELECT 
    region, product, sales,
    RATIO_TO_REPORT(sales) OVER (PARTITION BY region) AS sales_ratio
  FROM product_sales;
  

  结果（部分）：

  region	product	sales	sales_ratio	说明（华东地区总销售额100万）
  华东	A产品	30万	0.3	A产品占华东地区销售额30%

三、聚合类窗口函数

在保留明细行的同时对窗口内数据进行聚合计算，兼顾明细与统计需求。

1、基础聚合（SUM()/AVG()/COUNT()/MAX()/MIN()）

结合OVER()实现窗口内聚合（区别于GROUP BY，不合并明细行）。

• 函数：SUM()、AVG()、COUNT()、MAX()、MIN()

• 语法：

  聚合函数(目标列) OVER (
    [PARTITION BY 分组列]
    [ORDER BY 排序列]  -- 可选，影响聚合范围
  )
  

• 应用场景：需要同时展示明细和统计值的场景，如：“每行显示员工薪资+部门平均薪资”、“每日销量+当月总销量”。

• 示例：
  显示员工薪资及所在部门的平均薪资：

  SELECT 
    name, dept, salary,
    AVG(salary) OVER (PARTITION BY dept) AS dept_avg_salary
  FROM employees;
  

  结果（部分）：

  name	dept	salary	dept_avg_salary
  张三	技术部	15000	12000

  注：技术部平均薪资1.2万

2、滚动聚合（基于ROWS/RANGE子句）

基于移动窗口计算聚合结果（如：移动平均值、累计求和）。注：ROWS：按 “物理行数” 定义范围（如：6 PRECEDING指前6行），适用于有序且连续的场景（如：日期、序号）。RANGE：按 “值的范围” 定义范围（如：INTERVAL '1' DAY PRECEDING指前1天内的值），适用于数值或时间类型的 “逻辑范围”。

• 函数：SUM()、AVG()等聚合函数 + ROWS/RANGE子句

• 语法：

  聚合函数(目标列) OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列
    ROWS BETWEEN 范围起点 AND 范围终点  -- 定义滚动窗口范围
  )
  

  常用范围：

• UNBOUNDED PRECEDING：从第一行开始
• CURRENT ROW：当前行
• n PRECEDING：前n行
• n FOLLOWING：后n行

• 应用场景：时间序列分析，如：“7天移动平均销量”、“累计到当日的年度销售额”、“近3个月用户平均活跃天数”。

• 示例：
  计算每日销量及过去7天的滚动平均销量：

  SELECT 
    date, sales,
    AVG(sales) OVER (
      ORDER BY date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 包含当前行及前6行（共7天）
    ) AS rolling_avg_7days
  FROM daily_sales;
  

四、取值类窗口函数

获取窗口内特定位置的行数据，用于对比或提取关键位置的值。

1、偏移取值（LAG()/LEAD()）

LAG()获取当前行之前第n行的值，LEAD()获取当前行之后第n行的值。

• 函数：LAG()、LEAD()

• 语法：

  LAG(目标列, 偏移量, 默认值) OVER (  -- 向前偏移
    [PARTITION BY 分组列]
    ORDER BY 排序列
  )
  
  LEAD(目标列, 偏移量, 默认值) OVER (  -- 向后偏移
    [PARTITION BY 分组列]
    ORDER BY 排序列
  )
  

  注：偏移量默认1，默认值默认NULL

• 应用场景：对比相邻数据，如：“当日销量与前一日销量对比”、“本月业绩与下月业绩预估”、“用户当前登录与上一次登录时间差”。

• 示例：
  显示每日销量及前一日销量：

  SELECT 
    date, sales,
    LAG(sales, 1, 0) OVER (ORDER BY date) AS prev_day_sales
  FROM daily_sales;
  

  结果（部分）：

  date	sales	prev_day_sales
  2023-10-01	1000	0
  2023-10-02	1200	1000

  注：第一天无前置数据，显示默认值0；第二天显示10月1日销量）。

2、首尾取值（FIRST_VALUE()/LAST_VALUE()）

FIRST_VALUE()获取窗口内第一行的值，LAST_VALUE()获取窗口内最后一行的值。

• 函数：FIRST_VALUE()、LAST_VALUE()
• 语法：

  FIRST_VALUE(目标列) OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列
    [ROWS BETWEEN 范围起点 AND 范围终点]  -- 建议指定范围，避免默认范围问题
  )
  
  LAST_VALUE(目标列) OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列
    [ROWS BETWEEN 范围起点 AND 范围终点]  -- 建议指定范围，避免默认范围问题
  )
  

  注：窗口函数的默认范围是：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• 应用场景：提取窗口内的首尾关键值，如：“每月第一天/最后一天的销量”、“部门内薪资最高/最低的员工”。
• 示例：
  获取各部门薪资最高（第一行）和最低（最后一行）的员工姓名：

  SELECT 
    dept, name, salary,
    FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS highest_salary_emp,
    LAST_VALUE(name) OVER (
      PARTITION BY dept 
      ORDER BY salary DESC 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 指定完整范围
    ) AS lowest_salary_emp
  FROM employees;
  

3、条件取值（NTH_VALUE()）

获取窗口内第n行的值（n从1开始）。

• 函数：NTH_VALUE()

• 语法：

  NTH_VALUE(目标列, n) OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列
    [ROWS BETWEEN 范围起点 AND 范围终点]  -- 建议指定范围，避免默认范围问题
  )
  

• 应用场景：提取特定排名的值，如：“部门内薪资第3高的员工”、“每月销量第2天的数据”。

• 示例：
  获取各部门薪资第3高的员工薪资：

  SELECT 
    dept, name, salary,
    NTH_VALUE(salary, 3) OVER (
      PARTITION BY dept 
      ORDER BY salary DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS third_highest_salary
  FROM employees;
  

五、分桶类窗口函数

将数据按规则划分为固定数量的组（桶），用于数据分层或抽样。

1、数据分桶（NTILE(n)）

将窗口内数据平均分配到n个桶，返回每行所在桶的编号（1~n），若无法均分则前几个桶多1行。

• 函数：NTILE(n)

• 语法：

  NTILE(n) OVER (
    [PARTITION BY 分组列]
    ORDER BY 排序列 [ASC/DESC]
  )
  

• 应用场景：数据分层或抽样，如：“将客户按消费金额分为5个等级”、“将员工按绩效分为3组（优秀/普通/待改进）”。

• 示例：
  将员工按薪资分为4个等级（1级最高，4级最低）：

  SELECT 
    name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_level
  FROM employees;
  

  结果（部分）：

  name	salary	salary_level
  张三	15000	1
  李四	14000	1

  注：1级：薪资最高的25%。

总结

综上所述，5大类12小类的SQL窗口函数各有分工：排名类解决排序定位问题，分布类量化数据相对重要性，聚合类平衡明细与统计，取值类实现跨行数据提取，分桶类完成数据分层。在实际应用中，我们需结合场景选择函数，不用死记硬背，比如：算销售TOP3就用ROW_NUMBER，算某产品占总销量多少就用RATIO_TO_REPORT。关键是要记住：窗口函数能在不合并数据的前提下做统计，这比反复用GROUP BY再关联要高效得多。多练习几次，我们很快就能上手。熟练运用窗口函数可显著简化SQL逻辑，提升数据分析的深度与效率。

阅读原文：原文链接