SQL-窗口函数

如果你还在为 “分组后保留明细行”“计算每行与平均值的差异”“实现连续排名” 等需求写多层子查询，那么掌握窗口函数会让你的 SQL 效率和可读性翻倍。本文将从基础概念出发，带你系统性掌握 SQL 中最常用的窗口函数。

一、先搞懂：什么是窗口函数？

1. 窗口函数的定义

窗口函数（Window Function）也叫 “分析函数”，核心是 “窗口” 二字 —— 它会先将数据划分成若干个 “窗口”（即数据子集），然后对每个窗口内的数据执行计算，最终返回的结果与原数据行数一致（每个行对应其所在窗口的计算结果）。

2. 与传统聚合函数的区别

传统聚合函数（如 SUM()、AVG()、MAX()）会将 “满足条件的所有行” 聚合成一行，而窗口函数不会压缩行数，而是为每行生成一个 “窗口内的计算结果”。

举个简单例子：计算每个员工的薪资与部门平均薪资的差异。

• 用传统聚合函数：需要先分组计算部门平均薪资，再与原表关联，至少 2 步；
• 用窗口函数：一行代码即可完成，直接在原表中添加 “部门平均薪资” 列，再计算差异。

3. 窗口函数的通用语法

所有窗口函数都遵循以下结构，关键是 OVER() 子句 —— 它定义了 “窗口” 的划分规则：

窗口函数名(参数) OVER (
    [PARTITION BY 分组字段1, 分组字段2, ...]  -- 可选：按字段划分窗口（类似 GROUP BY）
    [ORDER BY 排序字段1 [ASC/DESC], 排序字段2 [ASC/DESC], ...]  -- 可选：窗口内数据的排序规则
    [ROWS/RANGE BETWEEN 边界1 AND 边界2]  -- 可选：定义窗口的物理范围（如“前3行到当前行”）
) AS 结果列别名

• PARTITION BY：窗口分组，将数据按指定字段分成多个窗口（不写则整个结果集为一个窗口）；
• ORDER BY：窗口内排序，对每个窗口内的数据按指定字段排序（仅对需要排序的窗口函数生效，如排名函数）；
• ROWS/RANGE：窗口范围，精细控制窗口的物理行数（如 “当前行及前 2 行”），大部分场景用默认值即可。

二、必学！SQL 中 4 类常用窗口函数

根据功能，常用窗口函数可分为 排名类、聚合类、取值类、分布类，其中前两类是日常开发中使用频率最高的，必须掌握。

第一类：排名类窗口函数 —— 解决 “排名” 需求

排名类函数用于对数据进行排序并生成排名，核心区别在于 “如何处理相同值的排名”。常用的有 3 个：ROW_NUMBER()、RANK()、DENSE_RANK()。

1. 函数对比与示例

假设有员工薪资表 employees，数据如下：

我们按 “部门分组，薪资降序” 排名，对比 3 个函数的结果：

SELECT 
    employee_id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,  -- 唯一排名
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk,        -- 跳跃排名
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drk  -- 连续排名
FROM employees;

结果如下：

2. 函数特点总结

第二类：聚合类窗口函数 —— 解决 “窗口内统计” 需求

聚合类窗口函数是将传统聚合函数（SUM、AVG、MAX、MIN、COUNT）用在 OVER() 子句中，实现 “窗口内的聚合计算”。

1. 常用函数与示例

需求：计算每个员工的薪资、部门内薪资总和、部门内薪资平均值、部门内最高薪资，以及当前部门的员工数量。

SELECT 
    employee_id,
    name,
    department,
    salary,
    -- 部门内薪资总和
    SUM(salary) OVER (PARTITION BY department) AS dept_salary_total,
    -- 部门内薪资平均值（保留2位小数）
    ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_salary_avg,
    -- 部门内最高薪资
    MAX(salary) OVER (PARTITION BY department) AS dept_salary_max,
    -- 部门内员工数量
    COUNT(*) OVER (PARTITION BY department) AS dept_emp_count
FROM employees;

结果（技术部部分）：

2. 进阶：累计聚合（带 ORDER BY 的聚合）

如果在聚合窗口函数中添加 ORDER BY，会实现 “累计聚合”—— 即按排序顺序，计算 “从窗口起始行到当前行” 的聚合结果。

需求：按薪资降序，计算技术部员工的 “累计薪资占比”：

SELECT 
    name,
    salary,
    -- 累计薪资（从第1行到当前行）
    SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS cumulative_salary,
    -- 部门总薪资
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    -- 累计薪资占比（保留2位小数）
    ROUND(
        SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) 
        / SUM(salary) OVER (PARTITION BY department) * 100, 
        2
    ) AS cumulative_ratio
FROM employees
WHERE department = '技术部';

结果：

第三类：取值类窗口函数 —— 解决 “获取相邻行数据” 需求

取值类函数用于获取 “窗口内指定位置的行数据”，比如 “获取上一行的薪资”“获取当前行的下两行数据”，常用的有 LAG() 和 LEAD()。

1. 常用函数说明

• 偏移量：默认是 1（即上一行 / 下一行），可自定义（如 2 表示上两行 / 下两行）；
• 默认值：当偏移后超出窗口范围时，返回默认值（不写则返回 NULL）。

2. 示例：计算员工薪资与上一位 / 下一位的差异

需求：按薪资降序，计算每个员工的薪资与 “上一位员工”“下一位员工” 的薪资差：

SELECT 
    name,
    salary,
    -- 获取上一位员工的薪资（偏移1，默认NULL）
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary,
    -- 获取下一位员工的薪资（偏移1，默认NULL）
    LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary,
    -- 与上一位的薪资差
    salary - LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS diff_prev,
    -- 与下一位的薪资差
    salary - LEAD(salary, 1, 0) OVER (ORDER BY salary DESC) AS diff_next
FROM employees;

结果（部分）：

第四类：分布类窗口函数 —— 解决 “数据分布统计” 需求

分布类函数用于分析数据在整体中的分布情况，比如 “计算当前行的薪资在部门内的百分位”，常用的有 PERCENT_RANK() 和 CUME_DIST()（使用频率较低，但需了解）。

1. 函数说明

2. 示例：分析技术部员工薪资的分布

SELECT 
    name,
    salary,
    -- 薪资排名（连续排名）
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drk,
    -- 百分位排名
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS pr,
    -- 累计分布比例
    CUME_DIST() OVER (PARTITION BY department ORDER BY salary DESC) AS cd
FROM employees
WHERE department = '技术部';

结果：

• pr=0.00：张三、李四的排名是 1，(1-1)/(3-1)=0，说明处于部门薪资的 0% 分位（最高）；
• cd=0.6667：前 2 行（张三、李四）占部门总人数（3 人）的 66.67%，即 66.67% 的员工薪资≥15000。

三、实战场景：用窗口函数解决 3 类经典问题

掌握语法后，关键是能在实际需求中灵活运用。以下是 3 个高频实战场景，覆盖 80% 的窗口函数使用场景。

场景 1：获取每个部门薪资排名前 2 的员工（含并列）

需求：按部门分组，取每个部门薪资排名前 2 的员工，允许并列（如两个第 1 名都保留）。

思路：用 DENSE_RANK() 按部门排名，再筛选排名≤2 的记录（需用子查询或 CTE 包装，因为窗口函数不能直接在 WHERE 中使用）。

WITH ranked_emps AS (
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_sal_rank
    FROM employees
)
SELECT * FROM ranked_emps
WHERE dept_sal_rank <= 2;

结果（技术部会保留张三、李四、王五，因为王五排名是 2；市场部保留孙七、赵六）。

场景 2：计算每个月的销售额同比增长率

需求：有销售表 sales（含 sale_date 日期、amount 销售额），计算每个月销售额同比（与去年同月）的增长率。

思路：

1. 先按 “年 - 月” 分组，计算每月总销售额；
2. 用 LAG() 获取 “去年同月” 的销售额（偏移 12 个月）；
3. 计算增长率：(当月销售额 - 去年同月销售额) / 去年同月销售额 * 100。

WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(sale_date, '%Y-%m') AS year_month,  -- 格式化为“年-月”
        SUM(amount) AS monthly_total  -- 每月总销售额
    FROM sales
    GROUP BY year_month
)
SELECT 
    year_month,
    monthly_total,
    -- 获取去年同月的销售额（偏移12行，因为按年-月排序，12行对应12个月）
    LAG(monthly_total, 12) OVER (ORDER BY year_month) AS last_year_same_month,
    -- 计算同比增长率（保留2位小数，若去年无数据则显示NULL）
    ROUND(
        (monthly_total - LAG(monthly_total, 12) OVER (ORDER BY year_month)) 
        /

四、导图浏览