跳到正文
本地阅读 · 窗口函数与窗口框架

窗口函数与窗口框架

计算机基础 · SQL

前置知识：子查询、CTE 与集合操作

本章目录

本章学习目标

  • 区分窗口计算与分组压缩
  • 规定排名并列规则
  • 显式使用 ROWS 并解释累计
前置知识：子查询、CTE 与集合操作

窗口保留明细

GROUP BY user_id 把多笔订单压成一行；SUM(amount) OVER(PARTITION BY user_id) 为每笔订单附上用户总金额，订单行数保持不变。PARTITION BY 定义独立组，不写则全表一个组。窗口在分组后的结果上计算，因此可以先按日聚合，再对每日收入求累计，前提是明确此时输入粒度已经变成一天一行。

OVER 内 ORDER BY 定义运算顺序，不等于最终显示顺序，末尾仍需 ORDER BY。排名别名不能直接在同级 WHERE 使用，筛选早于窗口计算。用 CTE 计算排名再外层筛选即可。MySQL 没有 QUALIFY，本课程不会把其他方言的语法混入可运行示例。

三种排名与并列

金额一百、一百、八十的 ROW_NUMBER 是一、二、三，RANK 是一、一、三，DENSE_RANK 是一、一、二。取恰好两条记录用前者，取前两档金额并保留并列用后者。ROW_NUMBER 应按时间与唯一编号确定次序，否则相同时间的两笔订单可能互换序号。

排名并列取决于整个排序键。若在 RANK 或 DENSE_RANK 内加入唯一编号，同金额就不再并列。要保留金额并列，仅在窗口按金额排序，最终显示再按编号排序。面试遇到“前几名”时需明确保留并列还是限制行数，否则正确语法也可能回答错误问题。

ROWS 和 RANGE 的演算

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 从第一行累计到当前行；ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 取当前与上一行。缺失一天时前一行并非昨天，所以行窗口不是自然日窗口。所有示例显式写框架，避免依赖默认行为。

按金额排列十、十、二十，RANGE 到当前值包括同值伙伴，前两行累计都为二十；ROWS 配合唯一顺序依次得到十、二十、四十。带排序的聚合窗口默认框架可能就是 RANGE。LAST_VALUE 若框架只到当前行，取得当前框架的末行，不能直接解释成全分区最后一行。

LAG 与信息时点

LAG 返回组内上一行，首行没有前驱得到 NULL。行情收益计算要按股票分区和日期排序，先解决重复股票日期；有节假日或缺失时，上一观测不等于昨天。订单示例列名写 previous_net，表示上一笔订单净额，不能写成昨日收入。

当前订单占整个用户收入的比例会用到未来订单，适合事后报告，却不适合实时预测特征。模型与量化研究需要检查窗口是否只覆盖当时已经可用的信息。若预测当前订单，应将累计截止前一行，使用 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING，并对首行空值作明确解释。

从结果到验证

阅读一段查询时，先写下一行结果代表什么，再列出来源表的一行代表什么。把过滤、连接、聚合和排序分别检查，比仅凭输出看起来合理更可靠。对于金额应同时保留订单数量和用户数量，以便发现连接引起的重复累计；对于比率应同时展示分子与分母，以便发现筛选范围不同。结果少并不等于正确，结果多也不等于错误，判断依据是预先确定的业务口径。

先用纸笔构造三个用户、两三个订单的小表，再用数据库运行对应查询。至少包含没有订单的用户、同一用户多笔订单、一个未知值，以及筛选后没有记录的情形。用这些反例解释每个子句造成的变化，再扩大到课程数据。未写 ORDER BY 的结果没有稳定顺序；需要核对输出时总要明确排序。课程默认 MySQL 8.4，开启严格分组规则，不要通过关闭规则掩盖数据粒度问题。

完成练习后，先自行解释结果再查看答案。记录的检查标准应是行数、键是否唯一、是否有空值、金额是否守恒，以及日期边界是否符合定义，而不只是查询没有语法错误。答案中的每个条件都应能追溯到题目口径；如果口径改变，先调整定义再改查询。这样形成的推理过程可以直接用于面试中解释思路和实际工作中复核分析报告。

交互演示：观察机制

完整示例与实际输出

下载 sql-window.sql ↓
WITH ranked AS (
  SELECT order_id,user_id,order_date,amount-refund_amount net_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date,order_id) rn,
    SUM(amount-refund_amount) OVER (PARTITION BY user_id ORDER BY order_date,order_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_net,
    LAG(amount-refund_amount) OVER (PARTITION BY user_id ORDER BY order_date,order_id) previous_net
  FROM orders WHERE status IN ('paid','refunded') AND user_id IS NOT NULL
) SELECT * FROM ranked ORDER BY user_id,order_date,order_id;
WITH t AS (SELECT 1 id,10 value UNION ALL SELECT 2,10 UNION ALL SELECT 3,20)
SELECT id,value,SUM(value) OVER (ORDER BY value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) range_total,
  SUM(value) OVER (ORDER BY value,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rows_total
FROM t ORDER BY value,id;

实际运行通过Actual MySQL 8.4.11 execution; strict grouping mode.

order_id	user_id	order_date	net_amount	rn	running_net	previous_net
1	1	2026-01-02 01:00:00	48.00	1	48.00	NULL
25	1	2026-01-06 01:00:00	48.00	2	96.00	48.00
49	1	2026-01-10 01:00:00	48.00	3	144.00	48.00
50	2	2026-01-01 02:00:00	240.00	1	240.00	NULL
2	2	2026-01-03 02:00:00	240.00	2	480.00	240.00
26	2	2026-01-07 02:00:00	240.00	3	720.00	240.00
51	3	2026-01-03 03:00:00	12.50	1	12.50	NULL
3	3	2026-01-05 03:00:00	12.50	2	25.00	12.50
52	4	2026-01-04 04:00:00	48.00	1	48.00	NULL
4	4	2026-01-06 04:00:00	48.00	2	96.00	48.00
28	4	2026-01-10 04:00:00	48.00	3	144.00	48.00
53	5	2026-01-06 05:00:00	240.00	1	240.00	NULL
5	5	2026-01-08 05:00:00	240.00	2	480.00	240.00
29	5	2026-01-12 05:00:00	240.00	3	720.00	240.00
30	6	2026-01-03 06:00:00	12.50	1	12.50	NULL
6	6	2026-01-09 06:00:00	12.50	2	25.00	12.50
31	7	2026-01-05 07:00:00	48.00	1	48.00	NULL
55	7	2026-01-09 07:00:00	24.00	2	72.00	48.00
7	7	2026-01-11 07:00:00	48.00	3	120.00	24.00
32	8	2026-01-06 08:00:00	240.00	1	240.00	NULL
56	8	2026-01-10 08:00:00	240.00	2	480.00	240.00
8	8	2026-01-12 08:00:00	240.00	3	720.00	240.00
33	9	2026-01-08 09:00:00	6.25	1	6.25	NULL
57	9	2026-01-12 09:00:00	12.50	2	18.75	6.25
10	10	2026-01-05 10:00:00	48.00	1	48.00	NULL
34	10	2026-01-09 10:00:00	48.00	2	96.00	48.00
58	10	2026-01-13 10:00:00	48.00	3	144.00	48.00
11	11	2026-01-07 11:00:00	120.00	1	120.00	NULL
35	11	2026-01-11 11:00:00	240.00	2	360.00	120.00
59	11	2026-01-15 11:00:00	240.00	3	600.00	240.00
12	12	2026-01-08 12:00:00	12.50	1	12.50	NULL
13	13	2026-01-10 13:00:00	48.00	1	48.00	NULL
37	13	2026-01-14 13:00:00	48.00	2	96.00	48.00
14	14	2026-01-11 14:00:00	240.00	1	240.00	NULL
38	14	2026-01-15 14:00:00	240.00	2	480.00	240.00
15	15	2026-01-13 15:00:00	12.50	1	12.50	NULL
39	15	2026-01-17 15:00:00	12.50	2	25.00	12.50
40	16	2026-01-08 16:00:00	48.00	1	48.00	NULL
16	16	2026-01-14 16:00:00	48.00	2	96.00	48.00
41	17	2026-01-10 17:00:00	240.00	1	240.00	NULL
17	17	2026-01-16 17:00:00	240.00	2	480.00	240.00
42	18	2026-01-11 18:00:00	12.50	1	12.50	NULL
43	19	2026-01-13 19:00:00	48.00	1	48.00	NULL
19	19	2026-01-19 19:00:00	48.00	2	96.00	48.00
20	20	2026-01-10 20:00:00	240.00	1	240.00	NULL
44	20	2026-01-14 20:00:00	120.00	2	360.00	240.00
21	21	2026-01-12 21:00:00	12.50	1	12.50	NULL
22	22	2026-01-13 22:00:00	24.00	1	24.00	NULL
46	22	2026-01-17 22:00:00	48.00	2	72.00	24.00
23	23	2026-01-15 23:00:00	240.00	1	240.00	NULL
47	23	2026-01-19 23:00:00	240.00	2	480.00	240.00
24	24	2026-01-16 00:00:00	12.50	1	12.50	NULL
48	24	2026-01-20 00:00:00	12.50	2	25.00	12.50

id	value	range_total	rows_total
1	10	20	10
2	10	20	20
3	20	40	40

五道自测：先回答，再核对

01 · 窗口 SUM 会减少行数吗？

查看提示

比较分组。

查看参考答案

不会，窗口附加值，GROUP BY 才压缩行。

02 · 100、100、80 的 RANK？

查看提示

并列后跳号。

查看参考答案

一、一、三；DENSE_RANK 是一、一、二。

03 · ROW_NUMBER 为何追加唯一编号？

查看提示

相同时间。

查看参考答案

打破并列，给出可复现的唯一顺序。

04 · ROWS 1 PRECEDING 是昨天吗？

查看提示

观测与日历不同。

查看参考答案

不是，是上一行；自然日需检查日期间隔或补日历。

05 · WHERE rn=1 为何不能直接使用窗口别名？

查看提示

阶段顺序。

查看参考答案

WHERE 早于窗口；放入 CTE 后外层筛选。

两项动手任务

任务 1 · 每用户取最早支付订单，时间同则按编号。

提示

ROW_NUMBER。

完整参考解答与检查方式WITH r AS (SELECT order_id,user_id,order_date,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date,order_id) rn FROM orders WHERE status IN ('paid','refunded') AND user_id IS NOT NULL) SELECT order_id,user_id,order_date FROM r WHERE rn=1 ORDER BY user_id; 编号确定并列次序。

任务 2 · 按日累计支付净额。

提示

先一天一行。

完整参考解答与检查方式WITH d AS (SELECT DATE(order_date) day,SUM(amount-refund_amount) net FROM orders WHERE status IN ('paid','refunded') GROUP BY DATE(order_date)) SELECT day,net,SUM(net) OVER(ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumulative_net FROM d ORDER BY day; 日聚合后累计不会出现同日多行。

官方资料与掌握状态

完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。

动手任务记录

掌握状态与复习

关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。

记录尽量保存在当前浏览器，建议定期导出备份。