SQL 窗口函数

一、什么是窗口函数

窗口函数也叫OLAP 函数，可以对一组数据做计算，但不会像 group by 那样压缩行数，原表多少行，结果就多少行。

语法：

sql

 

 

函数() OVER (
    PARTITION BY 分组字段
    ORDER BY 排序字段
    ROWS/RANGE 范围
)

 

核心特点：

1. PARTITION BY：分组（相当于 group by，窗口范围）
2. ORDER BY：组内排序
3. OVER () 括号里为空：整个表是一个大窗口

二、五大类常用窗口函数

1. 排序类（面试最常考）

（1）ROW_NUMBER()

组内连续序号，不重复、不跳号

sql

 

 

ROW_NUMBER() OVER(PARTITION BY 部门 ORDER BY 工资 DESC)

 

示例：1,2,3,4

（2）RANK()

并列排名会跳号

 

示例：1,1,3,4

（3）DENSE_RANK()

并列不跳号

 

示例：1,1,2,3

2. 聚合窗口函数

把聚合函数放在窗口里，不合并行：

 

SUM()、AVG()、MAX()、MIN()、COUNT()

示例：求每个部门工资累计和

sql

 

 

SUM(工资) OVER(PARTITION BY 部门 ORDER BY 工资) AS 累计工资

 

3. 偏移类函数（取上一条 / 下一条）

LAG (字段，偏移量，默认值)

取当前行上 N 行数据

sql

 

 

LAG(工资,1) OVER(PARTITION BY 部门 ORDER BY 入职时间) AS 上一人工资

 

LEAD()

取当前行下 N 行数据

4. 首尾取值

FIRST_VALUE(字段)：分组内排序后第一条值

 

LAST_VALUE(字段)：分组内排序后最后一条值

5. 分桶函数

NTILE(n)：把每组数据均匀分成 n 桶，给每行打上桶编号

 

常用于：取前 20% 高薪员工

三、窗口范围（ROWS 精细控制窗口）

默认：ORDER BY 后窗口是第一行～当前行

sql

 

 

-- 从分组第一行到当前行（默认）
SUM(sal) OVER(PARTITION BY dept ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 当前行 + 前1行 + 后1行 三行求和
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- 分组所有行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

 

四、经典面试场景（必练）

场景 1：每个部门薪资排名

sql

 

 

SELECT
    *,
    RANK() OVER(PARTITION BY dept ORDER BY salary DESC) rk
FROM emp;

 

场景 2：每个部门薪资前 3 名

先用窗口函数排名，再套一层子查询筛选

sql

 

 

SELECT * FROM (
    SELECT
        *,
        DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) rk
    FROM emp
) t WHERE t.rk <= 3;

 

场景 3：环比计算（本月 vs 上月销售额）

sql

 

 

SELECT
    month,
    sales,
    LAG(sales,1) OVER(ORDER BY month) last_month_sales
FROM sale;

 

场景 4：分组内平均工资，每个人对比部门均值

sql

 

 

SELECT
    *,
    AVG(salary) OVER(PARTITION BY dept) dept_avg
FROM emp;

 

五、三大排序函数区别速记

表格

 

函数	重复并列	是否跳号
ROW_NUMBER	不重复	不跳
RANK	重复	跳号
DENSE_RANK	重复	不跳号

 

六、初始化测试表（MySQL8.0 / Hive / SparkSQL 通用）

1. 建表语句

sql

 

 

CREATE TABLE emp (
    id INT,
    name VARCHAR(20),
    dept VARCHAR(20),  -- 部门
    salary DECIMAL(10,2), -- 薪资
    hire_date DATE     -- 入职时间
);

-- 插入测试数据
INSERT INTO emp VALUES
(1,'张三','研发部',8000,'2020-01-15'),
(2,'李四','研发部',12000,'2019-05-20'),
(3,'王五','研发部',12000,'2019-06-10'),
(4,'赵六','研发部',9500,'2021-03-08'),
(5,'小明','市场部',7500,'2020-07-12'),
(6,'小红','市场部',9500,'2019-11-03'),
(7,'小刚','市场部',9500,'2020-02-18'),
(8,'小丽','财务部',6800,'2021-05-22');

 

七、题目 1：三种排序函数对比（高频面试）

需求

查询每个部门员工薪资降序，分别用 ROW_NUMBER/RANK/DENSE_RANK 展示排名。

参考答案

sql

 

 

SELECT
    id,
    name,
    dept,
    salary,
    ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) rn,
    RANK() OVER(PARTITION BY dept ORDER BY salary DESC) rk,
    DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) drk
FROM emp;

 

结果说明

• ROW_NUMBER：同薪资也依次 1、2、3，无并列
• RANK：并列 1、1，下一个直接跳 3
• DENSE_RANK：并列 1、1，下一个是 2，不跳号

八、题目 2：求每个部门薪资前 3 的员工

需求

筛选各部门薪资 TOP3 的员工，允许并列上榜

参考答案

sql

 

 

SELECT * FROM (
    SELECT
        *,
        DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) drk
    FROM emp
) t
WHERE t.drk <= 3;

 

九、题目 3：偏移函数 LAG、LEAD（环比场景）

需求

按入职时间升序，查询每个员工的上一位同事薪资、下一位同事薪资

参考答案

sql

 

 

SELECT
    name,
    hire_date,
    salary,
    LAG(salary, 1) OVER(ORDER BY hire_date) prev_sal,
    LEAD(salary, 1) OVER(ORDER BY hire_date) next_sal
FROM emp;

 

拓展：按部门分组，取部门内上一个入职员工薪资

sql

 

 

SELECT
    name,
    dept,
    hire_date,
    salary,
    LAG(salary,1,0) OVER(PARTITION BY dept ORDER BY hire_date) dept_prev_sal
FROM emp;

 

十、题目 4：聚合窗口函数：部门平均薪资 + 累计薪资

需求

1. 展示每个人薪资、所在部门平均薪资
2. 部门内按薪资升序，计算部门内薪资累计和

参考答案

sql

 

 

SELECT
    name,
    dept,
    salary,
    AVG(salary) OVER(PARTITION BY dept) dept_avg_sal,
    SUM(salary) OVER(PARTITION BY dept ORDER BY salary) dept_sum_sal
FROM emp;

 

十一、题目 5：NTILE 分桶函数：每个部门薪资分成 2 组，标记高低薪

需求

每个部门员工按薪资降序平均分为 2 桶，1 为高薪组，2 为低薪组

参考答案

sql

 

 

SELECT
    name,
    dept,
    salary,
    NTILE(2) OVER(PARTITION BY dept ORDER BY salary DESC) bucket
FROM emp;

 

十二、题目 6：FIRST_VALUE / LAST_VALUE 组内最值

需求

查询每个部门薪资最高的员工薪资、当前排序下到当前行最低薪资

参考答案

sql

 

 

SELECT
    name,
    dept,
    salary,
    FIRST_VALUE(salary) OVER(PARTITION BY dept ORDER BY salary DESC) dept_max_sal,
    LAST_VALUE(salary) OVER(
        PARTITION BY dept ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) cur_min_sal
FROM emp;

 

十三、题目 7：自定义窗口范围（滑动窗口）

需求

全局按入职时间排序，统计当前员工 + 前 1 位、后 1 位三人的平均薪资

参考答案

sql

 

 

SELECT
    name,
    hire_date,
    salary,
    AVG(salary) OVER(
        ORDER BY hire_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) slide_avg_sal
FROM emp;

 

十四、进阶练习题（电商业务场景，面试真题）

建销售表

sql

 

 

CREATE TABLE sales(
    sale_date DATE,
    product VARCHAR(20),
    sales INT
);

INSERT INTO sales VALUES
('2025-01-01','手机',200),
('2025-01-02','手机',150),
('2025-01-03','手机',300),
('2025-01-01','电脑',120),
('2025-01-02','电脑',180);

 

业务题 1：求每个商品每日累计销售额

sql

 

 

SELECT
    sale_date,
    product,
    sales,
    SUM(sales) OVER(PARTITION BY product ORDER BY sale_date) total_acc
FROM sales;

 

业务题 2：计算每个商品环比销量（当日 vs 前一日）

sql

 

 

SELECT
    sale_date,
    product,
    sales,
    LAG(sales) OVER(PARTITION BY product ORDER BY sale_date) last_day_sales
FROM sales;

 

需要我把所有题目整理成 Word 版结构化题库（含易错点解析）吗？

易错点解析

SQL 窗口函数高频易错点详细解析

一、三大排序函数易错点（面试最容易踩坑）

1. ROW_NUMBER / RANK / DENSE_RANK 混淆

易错场景

相同薪资想并列排名，误用ROW_NUMBER导致并列数据排名不一样。

• ROW_NUMBER：无论值是否相同，序号连续不重复 1,2,3,4
• RANK：相同值同排名，后面序号直接跳跃 1,1,3,4
• DENSE_RANK：相同值同排名，序号连续不跳跃 1,1,2,3

错误写法

需求：取部门薪资前三（允许并列）

sql

 

 

-- 错误：同工资会被挤下去，漏人
ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) rn
WHERE rn <=3

 

正确写法

并列上榜必须用DENSE_RANK。

2. 忘记加 PARTITION BY 导致全局排名

错误

只写ORDER BY，没分组，所有部门在一起排名，不是部门内排名。

sql

 

 

RANK() OVER(ORDER BY salary DESC) rn

 

正确

需要组内排名必须带上 PARTITION BY 分组字段。

二、OVER 子句中 ORDER BY 引发的窗口范围陷阱（超级高频坑）

1. 带 ORDER BY：默认窗口范围

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

 

分组第一行 → 当前行，做累计求和、累计平均。

2. 不带 ORDER BY：默认整个分组所有行

窗口范围：分组全部数据，直接求分组整体聚合值（部门平均、部门总工资）。

易错案例：LAST_VALUE 拿不到分组最后一条数据

sql

 

 

-- 错误：默认只取第一行到当前行，每一行的last_value都是自己
LAST_VALUE(salary) OVER(PARTITION BY dept ORDER BY salary DESC)

 

原因

带 ORDER BY，窗口不断缩小，只会统计到当前行。

正确写法：手动指定窗口范围为整个分组

sql

 

 

LAST_VALUE(salary) OVER(
    PARTITION BY dept ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

 

三、聚合窗口函数易错点

1. GROUP BY 和窗口函数混用误区

• GROUP BY会压缩行数，一组只返回一行；
• 窗口函数不会压缩行数，原表多少行返回多少行；
• 窗口函数不能直接写在 WHERE 条件里，只能放到子查询 / CTE 中过滤。

错误写法

sql

 

 

SELECT *, RANK() OVER() rn FROM emp WHERE rn <=3;

 

报错原因：WHERE 执行早于 SELECT，窗口别名还未生成

正确写法

子查询嵌套 或者 CTE 表达式：

sql

 

 

SELECT * FROM (
    SELECT *, RANK() OVER(PARTITION BY dept ORDER BY salary DESC) rn
    FROM emp
) t WHERE t.rn <= 3;

 

2. 累计求和排序方向错误

ORDER BY salary DESC是从高薪开始累加；

 

ORDER BY salary ASC从低薪开始累加，业务需求要注意排序方向。

四、偏移函数 LAG / LEAD 易错点

1. 忘记分组，跨部门取上一条数据

sql

 

 

-- 错误：会拿到上一个部门的员工数据
LAG(salary) OVER(ORDER BY hire_date)

 

业务需要部门内上一位入职员工，必须加PARTITION BY dept。

2. 第一行 LAG、最后一行 LEAD 返回 NULL

没有上一条 / 下一条记录时默认返回 NULL，容易统计出错。

 

可以设置默认值规避：

sql

 

 

LAG(salary, 1, 0) OVER(PARTITION BY dept ORDER BY hire_date)

 

第三个参数为取不到值时的默认填充值。

3. 偏移量写错

LAG(字段, 偏移量)，偏移量必须是正整数，不能写负数。

五、NTILE 分桶易错点

1. 如果每组行数不能被桶数整除，前面的桶会多分配 1 条数据。
    
   例：5 个人分 2 桶：第 1 桶 3 人，第 2 桶 2 人。
2. NTILE 必须配合ORDER BY使用，否则分桶随机，无业务意义。
3. NTILE 只能均匀分组，无法按数值区间分桶（数值区间用 CASE WHEN）。

六、窗口范围 ROWS/RANGE 易错点

1. ROWS 基于物理行；RANGE 基于逻辑值范围

• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW：上一行 + 当前行
• RANGE 很少用，日期、数值范围匹配，新手尽量只用 ROWS。

2. 滑动窗口上下边界写错

• PRECEDING：往前
• FOLLOWING：往后
   
  边界写反会拿到错误数据。

七、执行顺序易错（高频面试坑）

SQL 执行顺序：

 

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

1. 窗口函数在SELECT阶段执行，WHERE、HAVING 不能直接使用窗口别名；
2. 如果先 WHERE 过滤数据，再执行窗口函数；想对原始全表开窗再过滤，必须子查询。

易错场景

想统计全部门薪资排名，再过滤工资大于 8000 的员工：

 

不能先 WHERE 再开窗，要先开窗再外层 WHERE 过滤。

八、空值、分区字段 NULL 陷阱

如果PARTITION BY的字段存在 NULL，所有 NULL 行会被分到同一个分区内，容易造成统计错误。

 

需要提前清洗 NULL 值，或者用IFNULL替换默认值再分区。

九、Hive/MySQL8.0 兼容性易错

1. MySQL5.x 不支持窗口函数，必须升级到 MySQL8.0 以上；
2. Hive 开窗时，大表建议合理分区，否则数据倾斜导致执行缓慢；
3. Hive 中 ORDER BY 全局排序，数据会落到一个 Reducer，尽量结合 PARTITION BY 使用。

十、业务场景典型易错总结

1. 取 TOPN 并列数据 → 用 DENSE_RANK，禁止 ROW_NUMBER；
2. 部门内最值 FIRST_VALUE/LAST_VALUE → 必须指定全表窗口范围；
3. 环比、同比 → LAG/LEAD 一定要分组 + 时间排序；
4. 分组平均值、总销售额 → OVER 内不要写 ORDER BY；
5. 窗口结果过滤 → 必须子查询，不能 WHERE 直接用别名。