上一章为了筛出“高于平均的城市”,我们套了两层派生表;为了算占比,又得把结果 JOIN 回原表。
这类“既要明细、又要汇总”的需求,用 GROUP BY 写总是别扭——因为它会把 15,615 行压成 8 行。
窗口函数解决的正是这件事:它在每一行后面加一列汇总值,行数一行不少。
排名、占比、累计、环比、复购间隔,SQL 篇里最难也最值钱的五类问题,全靠它。
本章 6 分讲原理、4 分做实验。原理部分必须吃透三件事:OVER 的三个部件、三种排名的差别、
以及窗口帧到底框住了哪几行。这三件事错一件,结果就会静默地错。
窗口函数的名字来自一个画面:把数据按某个维度切成几个“窗口”,每个窗口内单独算一遍, 算完把结果贴回原来的行上。窗口不合并行,这是它与 GROUP BY 唯一但决定性的差别。
同样一句“按城市算 GMV”,两种写法输出完全不同。GROUP BY 把 15,615 行折叠成 8 行, 每行代表一个城市;窗口函数输出 15,615 行,每行还是原来那笔订单,只是末尾多了一列 “它所在城市的 GMV”。
SELECT city, COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv FROM orders WHERE status = '已支付' GROUP BY city ORDER BY gmv DESC;
city | n | gmv 杭州 | 2050 | 20508489.75 武汉 | 2009 | 20318374.79 上海 | 1949 | 20009620.63 广州 | 1937 | 19707537.02 深圳 | 1951 | 19691841.98 西安 | 1929 | 19296448.47 成都 | 1919 | 19191271.10 北京 | 1871 | 18639796.40
SELECT order_id, city, ROUND(pay_amount, 2) AS amount,
ROUND(SUM(pay_amount) OVER (PARTITION BY city), 2) AS city_gmv
FROM orders
WHERE status = '已支付'
ORDER BY order_id
LIMIT 5;
order_id | city | amount | city_gmv O23010103782 | 深圳 | 2015.47 | 19691841.98 O23010103925 | 北京 | 5323.01 | 18639796.40 O23010104230 | 广州 | 22966.56 | 19707537.02 O23010105366 | 成都 | 3237.72 | 19191271.10 O23010105785 | 武汉 | 15102.29 | 20318374.79
| 写法 | 输入行数 | 输出行数 | 每行是什么 | 多出的一列 |
|---|---|---|---|---|
| GROUP BY | 15,615 | 8 | 一个城市 | — |
| 窗口函数 | 15,615 | 15,615 | 一笔订单 | 所在城市 GMV |
这个差别决定了它们的用途。只关心汇总(8 个城市谁高谁低)用 GROUP BY; 既要明细又要汇总(每一单占本城 GMV 多少、城市内排第几)只能上窗口函数。 硬用 GROUP BY 做后者,就得把结果 JOIN 回明细表,多写一层还可能踩粒度陷阱。
函数() OVER (...) 的作用是“在每行上贴一个值”,所以它是在 SELECT 阶段算出来的,
不会改变结果集的行数。行数只由 FROM、WHERE、GROUP BY 决定。
如果不这样会怎样:把“每个城市 GMV 占比”写成
SUM(pay_amount) / SUM(SUM(pay_amount)) 再 GROUP BY city,分子分母都是聚合值,
结果是对的;但一旦你还想保留订单号、下单时间,这条 SQL 就跑不通——
你被迫写两层查询,而窗口函数一层就够。
括号里的内容决定了“和谁一起算”。三个部件各管一件事,缺一个就用默认值,而默认值往往是坑。
SELECT order_id, order_date, ROUND(pay_amount, 2) AS amount,
ROUND(SUM(pay_amount) OVER (
PARTITION BY city -- ① 分窗:同城放一个窗口
ORDER BY order_date -- ② 窗内排序:决定累计方向
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- ③ 帧:前两行到当前行
), 2) AS last3
FROM orders
WHERE status = '已支付' AND city = '杭州'
ORDER BY order_date
LIMIT 5;
order_id | order_date | amount | last3 O23010116593 | 2023-01-01 00:06:40 | 5988.49 | 5988.49 O23010106029 | 2023-01-01 15:05:39 | 17288.69 | 23277.18 O23010209230 | 2023-01-02 04:38:58 | 26603.92 | 49881.10 O23010214679 | 2023-01-02 05:40:14 | 7738.12 | 51630.73 O23010201029 | 2023-01-02 06:11:39 | 11542.66 | 45884.70
看最后两行:都是 1 月 2 日,last3 是 49,881.10 和 51,630.73。 因为 ROWS 严格按行滑动,每往下一行就减掉最早的一笔、加上新的一笔。 如果把帧换成默认的 RANGE,同一天的三行会被当成一个整体,last3 会一模一样。
| 部件 | 作用 | 不写会怎样 |
|---|---|---|
| PARTITION BY | 把数据切成互不干扰的窗口 | 整张表一个窗口,所有行一起算 |
| ORDER BY | 窗内排序,排名和累计都依赖它 | 没有顺序,累计和排名都失去意义 |
| 窗口帧 | 指定参与计算的行的范围 | 默认“从窗口第一行到当前行”,会变成累计 |
最容易误解的是默认帧。写了 ORDER BY 却不写帧时,SQL 默认用
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——注意是 RANGE 不是 ROWS。
两者的区别在于:RANGE 会把排序键相同的行一起算进当前行。如果按日期累计,
同一天的两笔订单会同时看到“含对方”的累计值;按 ROWS 才是严格逐行累加。
三个部件是正交的:换 PARTITION 换比较范围,换 ORDER BY 换方向,换帧换参与计算的行数。 写窗口函数时,先在心里把这三句话说完整,再落笔。
如果不这样会怎样:求“3 个月移动平均”时只写 ORDER BY ym 不写帧,
得到的是“从 2023 年 1 月累计到当月”的累计值,不是移动平均。
2024-06 会算出 1.57 亿,而正确的三月均值是 903 万,差 17 倍。数字大得离谱,
但 SQL 不会报错。
三个函数的参数一模一样,都是 OVER (ORDER BY ...),差别只在遇到并列值时怎么办:
| 函数 | 并列时的行为 | 典型用途 |
|---|---|---|
ROW_NUMBER() | 强行编号 1、2、3、4,从不并列 | 取固定条数的 TopN、分页 |
RANK() | 并列同名次,下一名跳号(1、2、2、4) | 并列都保留,且名次要体现“占了几个位置” |
DENSE_RANK() | 并列同名次,下一名不跳(1、2、2、3) | 分档位、分等级 |
本库用户的下单次数天然有大量并列:17 次 1 人、16 次 4 人、15 次 9 人、14 次 28 人。 拿这四组做对照,三种排名的差别一目了然。
| 下单次数 | 人数 | ROW_NUMBER 编号 | RANK | DENSE_RANK |
|---|---|---|---|---|
| 17 | 1 | 1 | 1 | 1 |
| 16 | 4 | 2 – 5 | 2 | 2 |
| 15 | 9 | 6 – 14 | 6 | 3 |
| 14 | 28 | 15 – 42 | 15 | 4 |
看第 3 行:15 次的那 9 个人,RANK 给的是 6(前面已经占了 5 个位置),DENSE_RANK 给的是 3 (前面只有 2 个档位)。这就是“跳号”和“不跳号”的全部区别。
WITH u AS (
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
)
SELECT user_id, cnt,
ROW_NUMBER() OVER (ORDER BY cnt DESC, user_id) AS rn,
RANK() OVER (ORDER BY cnt DESC) AS rk,
DENSE_RANK() OVER (ORDER BY cnt DESC) AS drk
FROM u
ORDER BY cnt DESC, user_id
LIMIT 6;
user_id | cnt | rn | rk | drk U000162 | 17 | 1 | 1 | 1 U000147 | 16 | 2 | 2 | 2 U000796 | 16 | 3 | 2 | 2 U001118 | 16 | 4 | 2 | 2 U001672 | 16 | 5 | 2 | 2 U000019 | 15 | 6 | 6 | 3
选哪个不取决于喜好,取决于业务:要“发 10 张券给前 10 名”,必须是 ROW_NUMBER, 否则并列会让名额超出;要“前 3 个档位”,用 DENSE_RANK;要“所有达到第 3 名成绩的人”,用 RANK。
如果不这样会怎样:用 RANK 取“下单次数前 3 名”,会拿到 1 + 4 + 9 = 14 个人; 预算按 3 个人批的,实际要发 14 份。反过来用 ROW_NUMBER 取“所有并列第一”, 只会留下一个人,另外 3 个被无声丢掉。
把 SUM、AVG、COUNT 加上 OVER,它们就从“折叠”变成了“贴值”。
三种最常见的用法,区别只在帧:
WITH m AS (
SELECT substr(order_date, 1, 7) AS ym, SUM(pay_amount) AS gmv
FROM orders
WHERE status = '已支付' AND order_date >= '2024-01-01'
GROUP BY ym
)
SELECT ym, ROUND(gmv, 2) AS gmv,
ROUND(SUM(gmv) OVER (ORDER BY ym
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cum
FROM m
ORDER BY ym;
ym | gmv | cum 2024-01 | 9085918.82 | 9085918.82 2024-02 | 8345370.02 | 17431288.84 2024-03 | 9033174.82 | 26464463.66 2024-04 | 9331840.17 | 35796303.83 2024-05 | 8765340.49 | 44561644.32 2024-06 | 9015366.64 | 53577010.96
最后的 53,577,010.96 就是 2024 上半年的 GMV,一分不差。把帧换成
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW,同一个 SUM 立刻变成“近三月合计”,
再除以 3 就是移动平均;把 PARTITION BY 去掉、帧写成整个窗口,就得到占比的分母。
占比的写法值得单独记:SUM(x) / SUM(SUM(x)) OVER ()。分子是分组后的值,
分母是全部分组值的总和——窗口里套一个聚合,这是窗口函数最常用的组合技。
| 需求 | 帧写法 | 结果含义 |
|---|---|---|
| 累计(至今) | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | 从第一行加到当前行 |
| 近 3 行移动 | ROWS BETWEEN 2 PRECEDING AND CURRENT ROW | 前两行 + 当前行 |
| 整窗合计 | OVER (),不写 ORDER BY | 分区内全部行 |
| 前后各 1 行 | ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING | 居中平滑 |
帧到底“框住”了哪几行?把 2024 上半年的六个月排成一列,计算 2024-04 的三月移动平均时, 帧只覆盖 2、3、4 三个月——琥珀色那三行。1 月和 5、6 月虽然也在分区里,但不参与这一次计算。
| 月份 | 月 GMV | 在 2024-04 的帧内? |
|---|---|---|
| 2024-01 | 9,085,918.82 | 否 |
| 2024-02 | 8,345,370.02 | 是 · 前 2 行 |
| 2024-03 | 9,033,174.82 | 是 · 前 1 行 |
| 2024-04 | 9,331,840.17 | 是 · 当前行 |
| 2024-05 | 8,765,340.49 | 否 |
| 2024-06 | 9,015,366.64 | 否 |
当前行往下走一行,框就整体下滑一行。所以同一个 AVG,从第 3 行起分母都是 3, 但参与的三行不同——这就是“移动平均”比“整体平均”更能反映趋势的原因。
| 当前行 | 参与计算的三行 | 合计 | ÷ 3 |
|---|---|---|---|
| 2024-03 | 01 + 02 + 03 | 26,464,463.66 | 8,821,487.89 |
| 2024-04 | 02 + 03 + 04 | 26,710,385.01 | 8,903,461.67 |
| 2024-05 | 03 + 04 + 05 | 27,130,355.48 | 9,043,451.83 |
不要背三个公式,只记一件事:函数不变,帧变,含义就变。 “从第一行到当前行”是累计,“前 N 行到当前行”是移动,“整个分区”是占比的分母。
如果不这样会怎样:占比写成 SUM(gmv) / SUM(gmv) 却忘了 OVER,
分母被 GROUP BY 限制成当前城市自己,每一行都算出 100%。八个城市全是 100%,
加起来 800%,报表一眼假,但 SQL 一句警告都没有。
排名和累计都是“窗口内的聚合”,LAG 和 LEAD 则是“跨行取值”:取窗口内上一行或下一行的某个字段。
完整写法是 LAG(列, 偏移量, 默认值) OVER (PARTITION BY ... ORDER BY ...),
后两个参数可以省略,默认偏移 1 行、默认值 NULL。
环比就是“本月 − 上月,再除以上月”。上月这个值,正好是 LAG 一行:
WITH m AS (
SELECT substr(order_date, 1, 7) AS ym, SUM(pay_amount) AS gmv
FROM orders
WHERE status = '已支付' AND order_date >= '2024-01-01'
GROUP BY ym
)
SELECT ym, ROUND(gmv, 2) AS gmv,
ROUND(LAG(gmv) OVER (ORDER BY ym), 2) AS prev,
ROUND((gmv - LAG(gmv) OVER (ORDER BY ym)) * 100.0
/ LAG(gmv) OVER (ORDER BY ym), 2) AS mom
FROM m
ORDER BY ym;
ym | gmv | prev | mom 2024-01 | 9085918.82 | | 2024-02 | 8345370.02 | 9085918.82 | -8.15 2024-03 | 9033174.82 | 8345370.02 | 8.24 2024-04 | 9331840.17 | 9033174.82 | 3.31 2024-05 | 8765340.49 | 9331840.17 | -6.07 2024-06 | 9015366.64 | 8765340.49 | 2.85
第一行的 prev 和 mom 都是空,因为“上个月”不存在。注意这里是 NULL 而不是 0——
NULL 参与除法仍然是 NULL,不会算出 0%。如果你希望它显示 0,用
COALESCE(LAG(gmv) OVER (ORDER BY ym), 0),但那样 1 月的环比就变成了“增长 100%”,
反而更误导。空着,才是诚实的。
换一种列,LAG 还能算“间隔”。把 order_date 按用户分窗排序,上一笔订单的时间就在上一行,
两个日期相减就是复购间隔:
WITH o AS (
SELECT user_id, order_date,
LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS prev
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
)
SELECT user_id, prev, order_date,
ROUND(julianday(order_date) - julianday(prev), 1) AS gap_days
FROM o
WHERE prev IS NOT NULL AND user_id = 'U000001'
ORDER BY order_date;
user_id | prev | order_date | gap_days U000001 | 2023-02-23 21:46:22 | 2023-05-15 13:27:36 | 80.7 U000001 | 2023-05-15 13:27:36 | 2023-07-06 09:31:14 | 51.8 U000001 | 2023-07-06 09:31:14 | 2023-07-17 05:43:23 | 10.8 U000001 | 2023-07-17 05:43:23 | 2023-07-26 18:50:50 | 9.5 U000001 | 2023-07-26 18:50:50 | 2024-02-09 21:06:10 | 198.1 U000001 | 2024-02-09 21:06:10 | 2024-03-27 15:47:33 | 46.8 U000001 | 2024-03-27 15:47:33 | 2024-05-28 10:18:08 | 61.8
prev IS NOT NULL 必须放在外层。
如果直接写在同一层 WHERE 里,窗口还没算完,prev 这个列根本不存在——这正是下一小节要讲的问题。
SQL 的执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。 窗口函数属于 SELECT 阶段,也就是说,WHERE 在运行时,窗口值还不存在。 所以下面这句必然失败:
-- 错误:把 RANK 写进 HAVING
SELECT city, ROUND(SUM(pay_amount), 2) AS gmv,
RANK() OVER (ORDER BY SUM(pay_amount) DESC) AS rk
FROM orders
WHERE status = '已支付'
GROUP BY city
HAVING RANK() OVER (ORDER BY SUM(pay_amount) DESC) <= 3;
Result: misuse of window function rank()
正确的做法是“套一层”:先在子查询里把排名算出来,外层再把 rk 当普通列过滤。 外层的 WHERE 执行时,子查询已经算完,rk 是一个实实在在的列。
SELECT city, gmv, rk
FROM (
SELECT city, ROUND(SUM(pay_amount), 2) AS gmv,
RANK() OVER (ORDER BY SUM(pay_amount) DESC) AS rk
FROM orders
WHERE status = '已支付'
GROUP BY city
) t
WHERE rk <= 3
ORDER BY rk;
city | gmv | rk 杭州 | 20508489.75 | 1 武汉 | 20318374.79 | 2 上海 | 20009620.63 | 3
记住这条流水线:子查询算窗口 → 外层筛窗口值。CTE 也同理,
WITH t AS (SELECT ..., RANK() OVER ... AS rk ...) SELECT * FROM t WHERE rk <= 3
是同一个结构,只是更易读。
如果不这样会怎样:SQLite 报 misuse of window function rank(),
MySQL 报 You can't use window functions here。更麻烦的是别名版本:
在 HAVING 里写 rk <= 3,SQLite 报
misuse of aliased window function rk——错得更隐蔽,因为看起来只是“引用了别名”。
把三种排名放进同一条 SELECT,本库下单次数最多的 10 个用户长这样:
WITH u AS (
SELECT user_id, COUNT(*) AS cnt, SUM(pay_amount) AS gmv
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
)
SELECT user_id, cnt, ROUND(gmv, 2) AS gmv,
ROW_NUMBER() OVER (ORDER BY cnt DESC, user_id) AS rn,
RANK() OVER (ORDER BY cnt DESC) AS rk,
DENSE_RANK() OVER (ORDER BY cnt DESC) AS drk
FROM u
ORDER BY cnt DESC, user_id
LIMIT 10;
user_id | cnt | gmv | rn | rk | drk U000162 | 17 | 140426.63 | 1 | 1 | 1 U000147 | 16 | 193692.70 | 2 | 2 | 2 U000796 | 16 | 163421.98 | 3 | 2 | 2 U001118 | 16 | 123808.69 | 4 | 2 | 2 U001672 | 16 | 140098.68 | 5 | 2 | 2 U000019 | 15 | 149064.02 | 6 | 6 | 3 U000042 | 15 | 179414.09 | 7 | 6 | 3 U000314 | 15 | 214519.94 | 8 | 6 | 3 U000700 | 15 | 148429.38 | 9 | 6 | 3 U000883 | 15 | 153255.14 | 10 | 6 | 3
读这张表,重点看三个位置:
| 问题 | 该用哪个 | 理由 |
|---|---|---|
| 发 10 张券给前 10 名 | ROW_NUMBER | 必须正好 10 个人,并列也要分出先后 |
| 前 3 名都发(并列全发) | RANK | 并列的人都发,宁可超预算 |
| 分成 3 个等级 | DENSE_RANK | 等级要连号,不能出现“第 4 级空缺” |
还有一个隐藏问题:排名不稳定。ORDER BY cnt DESC 只规定了次数,
16 次的那 4 个人谁排 2、谁排 5,数据库可以每次给你不同的答案。
这就是为什么 rn 那一列我写的是 ORDER BY cnt DESC, user_id——
加一个唯一键做“决胜局”,结果才可复现。考试、报表、A/B 实验分组,只要排名结果会被存下来,
就必须有 tie-breaker。
8 个步骤,从最简单的城市排名走到分组 TopN 和复购间隔。TopN 那两步专门放了 “错误写法 / 正确写法”的对照,因为窗口函数最坑的地方不是写不出来, 而是写出来了、也跑通了,结果是错的。
SUM(SUM(pay_amount)) OVER ()——外层 SUM 是窗口,内层 SUM 是分组聚合。少了 OVER,分母就只剩当前城市。SELECT city, ROUND(SUM(pay_amount), 2) AS gmv,
ROW_NUMBER() OVER (ORDER BY SUM(pay_amount) DESC) AS rn,
ROUND(SUM(pay_amount) * 100.0
/ SUM(SUM(pay_amount)) OVER (), 2) AS pct
FROM orders
WHERE status = '已支付'
GROUP BY city
ORDER BY gmv DESC;
每个城市都是 100%:分母忘了写 OVER (),被 GROUP BY 限制在当前城市内。
报 misuse of aggregate:把 SUM(SUM(x)) OVER () 写成了 SUM(x) OVER (),窗口里没有聚合,粒度对不上。
把 15.2 的 SQL 跑一遍,只看 cnt、rn、rk、drk 四列。
rn=6:它是第 6 个被数到的用户;rk=6:前面已经占了 5 个位置,它从第 6 位开始;drk=3:它属于第 3 个档位(17 次、16 次、15 次)。
-- 错误:只想要 3 行,不是每城 3 行 SELECT o.city, p.category, ROUND(SUM(oi.amount), 2) AS gmv FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id WHERE o.status = '已支付' GROUP BY o.city, p.category ORDER BY gmv DESC LIMIT 3;
因为 GROUP BY 先把 40 个“城市 × 品类”算完,ORDER BY 再全局排序,LIMIT 砍掉第 4 行以后的所有内容。想要“每城 3 行”,必须先在每个城市内部排名——这正是窗口函数的活。
SELECT city, category, gmv, rn
FROM (
SELECT o.city, p.category, ROUND(SUM(oi.amount), 2) AS gmv,
ROW_NUMBER() OVER (PARTITION BY o.city
ORDER BY SUM(oi.amount) DESC) AS rn
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = '已支付'
GROUP BY o.city, p.category
) t
WHERE rn <= 3
ORDER BY city, rn;
这里的分工值得记住:子查询负责“算排名”,外层负责“筛排名”。
如果把 WHERE rn <= 3 硬塞进子查询的 WHERE,会报
misuse of aliased window function rn。
手机、电脑是刚需,各城都排前二;第三名取决于本地偏好和供给。杭州、武汉的服饰更强,北京、成都的配件更强,其余城市是家电。这正是“分组 TopN”能看出来的结构信息,全局 Top3 永远看不到。
用 15.1.4 的 SQL,把月份限制在 2024 上半年。
SELECT SUM(pay_amount) ... AND order_date >= '2024-01-01' 的结果一致。累计值远大于总额:没写帧,或者把 2023 年的数据也算了进来。
累计每一步都等于当月值:帧写成了 CURRENT ROW 单行。
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 框住三行。两者都不改变行数。WITH m AS (
SELECT substr(order_date, 1, 7) AS ym, SUM(pay_amount) AS gmv
FROM orders
WHERE status = '已支付' AND order_date >= '2024-01-01'
GROUP BY ym
)
SELECT ym, ROUND(gmv, 2) AS gmv,
ROUND(LAG(gmv) OVER (ORDER BY ym), 2) AS prev,
ROUND((gmv - LAG(gmv) OVER (ORDER BY ym)) * 100.0
/ LAG(gmv) OVER (ORDER BY ym), 2) AS mom,
ROUND(AVG(gmv) OVER (ORDER BY ym
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma3
FROM m
ORDER BY ym;
1 月只有 1 行参与,2 月只有 2 行,帧“不满 3 行”时 AVG 按实际行数除。这是正常现象,做图时要么从前三个月之后开始画,要么在标题里注明“起始月样本不足”。
PARTITION BY user_id ORDER BY order_date 决定了“同一个人、按时间排”,换成全表排序就变成“全站相邻两单”了。WITH o AS (
SELECT user_id, order_date,
LAG(order_date) OVER (PARTITION BY user_id
ORDER BY order_date) AS prev
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
)
SELECT COUNT(*) AS intervals,
COUNT(DISTINCT user_id) AS users,
ROUND(AVG(julianday(order_date) - julianday(prev)), 2) AS avg_gap
FROM o
WHERE prev IS NOT NULL;
同一天下了两单(甚至两单相隔几分钟),相减就是 0 或 0.1。全库有 107 个“用户 × 日期”出现过一天多单。如果业务上按“天”定义复购,要先按天去重再算间隔。
439 是已支付的游客订单,被 user_id IS NOT NULL 挡在窗口之外——和上一章游客订单 565 笔(含未支付)是同一批数据。2,000 是每个用户的第一单,上一行不存在,prev 为 NULL,被外层 WHERE 过滤掉。
对不上时,先查这两个数,再查窗口有没有写错。
三个数,对应三个最容易写错的部件:帧、LAG、分组 TopN。
不看正文,凭第一反应选。
SUM(x) OVER (ORDER BY ym) 求累计,默认帧是什么?ROW_NUMBER() OVER (...) AS rn 直接写进同一层的 WHERE,会发生什么?misuse of aliased window function rn。必须套一层子查询或 CTE,把 rn 变成实体列再筛。窗口函数的错误分两类:报错的(好办)和不报错但结果错的(危险)。
| 现象 | 原因 | 解决 |
|---|---|---|
| misuse of window function | 窗口写进了 WHERE 或 HAVING | 套一层子查询或 CTE,外层筛别名 |
| misuse of aliased window function rn | 同一层 WHERE 引用了窗口别名 | 同上;别名不能在同一层 WHERE 使用 |
| 累计末值远大于总额 | 没写帧,或 PARTITION 漏了 | 显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| 占比每一行都是 100% | 分母忘了 OVER () | 写 SUM(SUM(x)) OVER () |
| TopN 只返回 N 行 | 用了全局 ORDER BY + LIMIT | PARTITION BY 分组键 + ROW_NUMBER,再筛 rn |
| 排名每次跑都不一样 | ORDER BY 不唯一,并列顺序随机 | 加唯一键做 tie-breaker,如 ORDER BY cnt DESC, user_id |
| LAG 第一行是空 | 上一行不存在,正常 | 保留 NULL,或用 COALESCE 给默认值 |
| 复购间隔出现 0 | 同一天多单 | 先按天去重,或改用 DATE(order_date) |
SUM(SUM(x)) OVER ()。1. 杭州 13.03%,武汉 12.91%,上海 12.72%,广州 12.52%,深圳 12.51%,西安 12.26%,成都 12.20%,北京 11.85%。八城合计 100.00%。
2. 15 行(5 个品类 × 3 个城市)。手机第一名是杭州 6,307,763.87,第二广州 6,293,041.98,第三武汉 6,012,621.92。
3. 增幅最大是 3 月 +8.24%(9,033,174.82 对 8,345,370.02);跌幅最大是 2 月 −8.15%(8,345,370.02 对 9,085,918.82)。
4. 30 天以内的间隔 5,024 个,占 13,176 个间隔的 38.13%。写法:SUM(CASE WHEN gap <= 30 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)。