SQL学习笔记6

SQL SQL 窗口函数

发布时间 : 2026-07-19 10:00

字数:4.3k 阅读 :

SQL学习笔记6：窗口函数
13. 窗口函数基础
14. 排名函数
15. 聚合窗口函数
16. 偏移函数
17. 实战场景
18. 常见陷阱与最佳实践
结语
SQL学习笔记六：窗口函数
13. 窗口函数基础
14. 排名函数
15. 聚合窗口函数
16. 偏移函数
17. 实战场景
18. 常见陷阱与最佳实践
结语

SQL学习笔记6：窗口函数

窗口函数是 SQL 进阶的分水岭——面试必考、数据分析必备。它和普通聚合函数最大的区别是：聚合函数把多行压成一行，窗口函数保留每一行，同时还能做跨行计算。

13. 窗口函数基础

13.1 什么是窗口函数

-- 普通聚合：每个部门只返回一行
SELECT dept, AVG(salary)
FROM employees
GROUP BY dept;

-- 窗口函数：每个员工都还在，但旁边多了部门平均工资
SELECT
    name,
    dept,
    salary,
    AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

name	dept	salary	dept_avg
张三	技术	15000	14000
李四	技术	13000	14000
王五	销售	12000	11000
赵六	销售	10000	11000

每个人都在，多了一列”部门平均薪资”——这就是窗口函数的核心价值。

13.2 基本语法

函数名() OVER (
    PARTITION BY 列名      -- 分区：按什么分组计算
    ORDER BY 列名          -- 排序：分区内按什么排序
    窗口帧子句             -- 范围：取分区内的哪几行
)

PARTITION BY 可以理解为 GROUP BY 的窗口版，但它不合并行，只是划定计算范围。

13.3 窗口函数分类

类别	函数	用途
排名	`ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`	排序、分组编号
聚合	`SUM`, `AVG`, `COUNT`, `MAX`, `MIN`	移动求和、累计值
偏移	`LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`	前后行对比
分析	`CUME_DIST`, `PERCENT_RANK`	百分比排名

14. 排名函数

14.1 ROW_NUMBER / RANK / DENSE_RANK 对比

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_num
FROM students;

name	score	row_num	rank_num	dense_num
张三	95	1	1	1
李四	95	2	1	1
王五	88	3	3	2
赵六	88	4	3	2
孙七	72	5	5	3

三者的区别一目了然：

函数	同分处理	编号连续性
`ROW_NUMBER`	随机排先后	连续（1, 2, 3, 4）
`RANK`	相同排名	跳号（1, 1, 3, 3, 5）
`DENSE_RANK`	相同排名	不跳号（1, 1, 2, 2, 3）

💡 面试常见题：”查每个部门工资最高的员工”——用 ROW_NUMBER + PARTITION BY dept ORDER BY salary DESC，然后取 row_num = 1。

14.2 分组内排名

-- 每个部门内按工资排名
SELECT
    name,
    dept,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

14.3 NTILE：分桶

-- 把学生按成绩分成 4 组（四分位）
SELECT
    name,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM students;

常用场景：按消费金额把用户分成高/中/低价值群体。

15. 聚合窗口函数

15.1 累计求和（Running Total）

SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales
ORDER BY date;

date	amount	running_total
01-01	100	100
01-02	200	300
01-03	150	450
01-04	300	750

每一行的 running_total 都是从开头到当前行的总和。

15.2 移动平均

-- 近 3 天的移动平均
SELECT
    date,
    amount,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma_3
FROM sales;

date	amount	ma_3
01-01	100	100
01-02	200	150
01-03	150	150
01-04	300	216.7

移动平均用于平滑波动，是销售预测、异常检测的基础。

15.3 各部门占比

SELECT
    name,
    dept,
    salary,
    salary * 100.0 / SUM(salary) OVER (PARTITION BY dept) AS dept_pct
FROM employees;

15.4 窗口帧子句详解

ROWS BETWEEN {起点} AND {终点}

写法	含义
`UNBOUNDED PRECEDING`	分区第一行
`n PRECEDING`	当前行前 n 行
`CURRENT ROW`	当前行
`n FOLLOWING`	当前行后 n 行
`UNBOUNDED FOLLOWING`	分区最后一行

常用组合：

-- 累计：从开头到当前
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 滑动窗口：前 2 行 + 当前 + 后 2 行
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING

-- 累计到分区末尾（少见）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

💡 聚合窗口函数如果不写 ORDER BY，默认是整个分区；如果写了 ORDER BY 但不写窗口帧子句，默认从分区开头到当前行。

16. 偏移函数

16.1 LAG：向前看

取当前行前面第 n 行的值。最经典的场景是环比计算。

SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;

month	revenue	prev_month_revenue	growth
1月	100	NULL	NULL
2月	150	100	+50
3月	130	150	-20
4月	180	130	+50

LAG(列名, 偏移量, 默认值) OVER (ORDER BY ...)
-- 默认值用于第一行（前面没有行时填充）

16.2 LEAD：向后看

取当前行后面第 n 行的值。

SELECT
    date,
    price,
    LEAD(price, 1) OVER (ORDER BY date) AS next_price,
    LEAD(price, 1) OVER (ORDER BY date) - price AS price_change
FROM stock_prices;

16.3 FIRST_VALUE / LAST_VALUE

取分区内第一个或最后一个值。

SELECT
    name,
    dept,
    salary,
    FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS highest_paid,
    LAST_VALUE(name) OVER (
        PARTITION BY dept
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid
FROM employees;

⚠️ LAST_VALUE 的默认窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这意味着它返回的是”到当前行为止的最后一行”而非分区的最后一行。想取分区最后一行，必须显式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。这是最常见的坑。

17. 实战场景

17.1 连续登录天数

表结构：

CREATE TABLE login_log (
    user_id INT,
    login_date DATE
);

WITH grouped AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
    FROM (SELECT DISTINCT user_id, login_date FROM login_log) t
)
SELECT
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3  -- 至少连续 3 天
ORDER BY consecutive_days DESC;

💡 核心思路：如果日期是连续的，那么 login_date - ROW_NUMBER() 得到的值是相同的，这就形成了一个分组标识。

17.2 留存率计算

WITH first_visit AS (
    SELECT
        user_id,
        MIN(visit_date) AS first_date
    FROM visits
    GROUP BY user_id
),
cohort AS (
    SELECT
        v.user_id,
        f.first_date,
        v.visit_date,
        DATEDIFF(v.visit_date, f.first_date) AS day_offset
    FROM visits v
    JOIN first_visit f ON v.user_id = f.user_id
)
SELECT
    first_date,
    COUNT(DISTINCT CASE WHEN day_offset = 0 THEN user_id END) AS day0_users,
    COUNT(DISTINCT CASE WHEN day_offset = 1 THEN user_id END) * 100.0 /
        COUNT(DISTINCT CASE WHEN day_offset = 0 THEN user_id END) AS day1_retention,
    COUNT(DISTINCT CASE WHEN day_offset = 7 THEN user_id END) * 100.0 /
        COUNT(DISTINCT CASE WHEN day_offset = 0 THEN user_id END) AS day7_retention
FROM cohort
GROUP BY first_date
ORDER BY first_date;

17.3 同时在线峰值

-- 直播、游戏等场景 → 计算任意时刻的最大同时在线人数
WITH events AS (
    SELECT enter_time AS event_time, 1 AS delta FROM online_log
    UNION ALL
    SELECT leave_time AS event_time, -1 AS delta FROM online_log
)
SELECT
    event_time,
    SUM(delta) OVER (ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS concurrent_users
FROM events
ORDER BY concurrent_users DESC
LIMIT 1;

💡 不用 GROUP BY，不用自连接——SQL 就是这么优雅。

18. 常见陷阱与最佳实践

陷阱 🕳️

RANK 和 DENSE_RANK 搞混

面试中问”怎么取排名前 10”，用 RANK 还是 DENSE_RANK？
- 如果成绩相同占用名额 → DENSE_RANK
- 如果允许并列导致超过 10 人 → RANK（取 rank <= 10）
LAST_VALUE 的窗口帧问题

如前所述，不指定窗口帧会得到错误结果。建议直接用 FIRST_VALUE(... ORDER BY ... DESC) 代替。

ORDER BY 在窗口函数和查询中的区别

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees
ORDER BY name ASC;  -- 这里的 ORDER BY 只影响最终结果的显示顺序，不影响窗口函数

窗口函数不能用在 WHERE 中

-- ❌ 错误
SELECT * FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;

-- ✅ 正确：用子查询或 CTE 包一层
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn <= 10;

💡 窗口函数在 SQL 执行顺序中位于 WHERE 之后、ORDER BY 之前，所以不能在 WHERE 里直接过滤。

PARTITION BY + ORDER BY 忘了写，窗口变成全表

-- 没写 PARTITION BY → 整个表是一个窗口
SUM(salary) OVER ()  -- 全表总和

-- 写了 PARTITION BY → 每个部门独立窗口
SUM(salary) OVER (PARTITION BY dept)  -- 部门总和

最佳实践 ✅

建议	说明
复杂查询拆成 CTE 逐步调试	窗口函数嵌套难以一眼看出结果
`ROW_NUMBER` + 子查询组合拳	取 Top N、去重、分页都很实用
优先用 `LAG`/`LEAD` 做环比	比自连接清晰太多
`PARTITION BY` 别太多列	分区太多几乎没有实用价值
生产环境注意数据量	窗口函数需要排序 + 内存，大表注意性能

SQL 执行顺序（完整版）

FROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → DISTINCT → ORDER BY → LIMIT

记住：窗口函数在 HAVING 之后，SELECT 之前。所以不能在 WHERE/GROUP BY/HAVING 中使用窗口函数的结果。

结语

窗口函数是 SQL 从”能用”到”好用”的关键一步。掌握了它，以前要写几十行子查询、临时表的复杂统计，现在几行 SQL 搞定。

这套 SQL 学习笔记系列到此涵盖了：

笔记	内容
笔记一	建库建表、字符类型
笔记二	IF EXISTS / IF NOT EXISTS
笔记三	WHERE、JOIN、GROUP BY、子查询、视图、索引、事务
笔记四	存储过程
笔记五	触发器、自定义函数
笔记六	窗口函数

从基础到进阶的 SQL 知识体系基本完整了。后续如果遇到特定场景（性能调优、分库分表等），再单独开篇。

——
title: SQL学习笔记六
date: 2026-07-19 10:00:00
categories:

SQL
tags:
SQL
窗口函数

SQL学习笔记六：窗口函数