这一章不教新语法,教的是看到题先问什么。留存、漏斗、复购、同环比、去重取最新,
五类题翻来覆去考的都是同一件事:分母是谁、窗口多长、按什么去重。
同一份数据,分母选错,结论就能从“用户很忠诚”变成“用户全跑了”。
本章用青柠优选的 2,000 个用户、20,000 笔订单和 16,932 条行为,把 12 道高频题拆成
题目 → 思路 → SQL → 结果 → 易错点,再留 20 道练习。做完这一章,你看到新题应该先画口径,而不是先敲 SELECT。
面试和日常取数,题目通常只有一句话。真正决定答案的,是你补出来的那三四个口径。 这一节先把五类题型的“口径清单”立起来,后面 12 道题都回头引用它。
留存题先定三件事:基准人群(谁算“第一天来了”)、回访定义(什么行为算“回来”)、 时间窗口(只看第 N 天,还是第 1 到 N 天都算)。三件事有一个没写清,数字就没法比较。
以 6 月 1 日的活跃用户为基准:当天去重后有 280 人。6 月 2 日仍活跃的有 40 人, 第 1 日留存率 = 40 ÷ 280 = 14.29%。6 月 3 日当天回访 44 人,第 3 日留存率 15.71%; 但 6 月 2 日到 4 日这三天里只要来过一次就算,累计 112 人,3 日内留存率 40.00%; 放宽到 6 月 2 日到 8 日,累计 200 人,7 日内留存率 71.43%。
| 口径 | 分子(人) | 分母(人) | 留存率 |
|---|---|---|---|
| 第 1 日(6/2 当天) | 40 | 280 | 14.29% |
| 第 3 日(6/3 当天) | 44 | 280 | 15.71% |
| 3 日内(6/2–6/4) | 112 | 280 | 40.00% |
| 7 日内(6/2–6/8) | 200 | 280 | 71.43% |
这张图是本章第一个反直觉点:第 1 日只有 14.29%,7 日内却有 71.43%。 如果你在周报里只写“留存 14%”,老板会以为用户全跑了;写“7 日内留存 71%”,结论完全相反。 所以留存率后面必须跟窗口,就像长度后面必须跟单位。
基准日也一样:选注册日,D0 可能一笔订单都没有;选首次活跃日或活动开始日,D0 一定有行为。 两套口径不能混着比——注册日看“激活速度”,活跃日看“回访黏性”。
310 ÷ 280 = 110.71%,留存率超过 100%,数学上不可能。
40 ÷ 280 = 14.29%,分子必须是分母的子集。
基准人群决定分母:是“某日活跃用户”“某日注册用户”,还是“某活动曝光用户”。 回访定义决定分子:是任意事件、登录、下单,还是支付。 时间窗口决定分子怎么数:第 N 日只数第 N 天,N 日内数第 1 到 N 天的并集。 三句话写不出来,这道题就还没读懂。
漏斗的本质是有序步骤,不是四个互不相干的计数。浏览 → 加购 → 下单 → 支付, 每一步的人必须“已经通过上一步”。按用户去重后,四步人数是 2,000 → 1,318 → 387 → 111,整体转化率 111 ÷ 2,000 = 5.55%。
| 步骤 | 人数 | 单步转化 | 累计转化 |
|---|---|---|---|
| 浏览 | 2,000 | — | 100.00% |
| 加购 | 1,318 | 65.90% | 65.90% |
| 下单 | 387 | 29.36% | 19.35% |
| 支付 | 111 | 28.68% | 5.55% |
同一批行为,如果按事件数数,是 8,822 → 5,816 → 1,793 → 501,转化率 65.93% → 30.83% → 27.94% → 5.68%。
和按人数的结果很接近,但含义不同:事件数回答“发生了多少次”,人数回答“有多少人”。
漏斗题默认问“多少人”,所以必须 COUNT(DISTINCT user_id)。
8,822 → 5,816 → 1,793 → 501
2,000 → 1,318 → 387 → 111
再往下还有两个口径:顺序和窗口。只要求“都做过”,支付是 111 人; 要求“加购之后才下单”,下单降到 377 人、支付 102 人,整体转化 5.10%。 再加时间窗口:加购后 1 分钟内下单只有 127 人(9.64%),1 小时内 374 人(28.38%), 不限时间 377 人(28.60%)。窗口越窄,转化越低——这不是数据变了,是口径变严了。
步骤从哪一步开始、到哪一步结束;计数按用户还是按事件;事件是否要求先后顺序; 两步之间允许多长时间。四问里最容易被漏掉的是“顺序”,其次是“窗口”。 只写“浏览到支付转化率 5.55%”,等于什么都没说清。
复购率的定义只有一句话:窗口内下单 ≥ 2 次的用户 ÷ 窗口内下单 ≥ 1 次的用户。 分母不是全部注册用户,也不是全部订单。6 月有 698 个买家,其中 138 人下了 2 单及以上, 6 月复购率 = 138 ÷ 698 = 19.77%。
换一个窗口,数字会吓你一跳:把 2023 年 1 月到 2024 年 6 月整段 18 个月当成窗口, 2,000 个买家里有 1,997 人至少下过两单,复购率 99.85%。 同一个库、同一句 SQL,只改了时间范围,复购率从 19.77% 变成 99.85%。 所以复购率不写窗口,等于耍流氓。
| 观察窗口 | 买家 | 复购买家 | 复购率 |
|---|---|---|---|
| 2024 年 6 月 | 698 | 138 | 19.77% |
| 2023-01 至 2024-06 | 2,000 | 1,997 | 99.85% |
复购间隔则是另一回事。6 月内共有 154 个“相邻两单”的间隔,平均 10.32 天, 中位数 10 天;放到全期,共 13,176 个间隔,平均 61.27 天,中位数 43 天。 平均值明显大于中位数,说明有一批用户隔了很久才回来,把均值拉长了。 汇报“典型复购间隔”时,中位数比平均值更抗极端值。
先写窗口(月/季/年/自定义),再写分母(窗口内买家,不是注册用户), 最后写间隔(相邻订单日期差,还是首单到第二单)。三件事缺一件,复购率就无法复现。
环比是“这个月和上个月比”,同比是“这个月和去年同月比”。听起来简单,但有两个坑: 天数不一样、缺失的月份不出现。
2024 年 6 月 GMV 是 9,015,366.64,5 月是 8,765,340.49,总 GMV 环比 = 9,015,366.64 ÷ 8,765,340.49 − 1 = +2.85%。但 5 月有 31 天,6 月只有 30 天: 5 月日均 282,752.92,6 月日均 300,512.22,日均环比 +6.28%。 同一个“增长”,按总量看是 2.85%,按日均看是 6.28%。汇报时必须写清用的是哪个。
同比受天数影响小:2024 年 6 月和 2023 年 6 月都是 30 天,GMV 分别是 9,015,366.64 和 8,875,566.96, 同比 +1.58%。
缺失月份更隐蔽。武汉的黑卡用户 2024 年 1 到 5 月每月都有已支付订单,6 月一笔都没有。
直接 GROUP BY 月份,结果只有 5 行,6 月“消失”了;画折线图时,5 月会直接连到 7 月(如果后面有数据),
趋势看起来是连续的。正确做法是先造一张月份日历,再 LEFT JOIN 数据,缺失月补 0。
| 月份 | 1 月 | 2 月 | 3 月 | 4 月 | 5 月 | 6 月 |
|---|---|---|---|---|---|---|
| GMV | 30,000.03 | 55,169.55 | 9,064.06 | 8,624.51 | 39,627.93 | 0 |
WITH RECURSIVE months(m) AS (
SELECT '2024-01-01'
UNION ALL
SELECT date(m, '+1 month') FROM months WHERE m < '2024-06-01'
),
g AS (
SELECT substr(o.order_date, 1, 7) AS ym, ROUND(SUM(o.pay_amount), 2) AS gmv
FROM orders o JOIN users u ON u.user_id = o.user_id
WHERE o.status = '已支付'
AND u.member_level = '黑卡' AND u.city = '武汉'
AND o.order_date >= '2024-01-01' AND o.order_date < '2024-07-01'
GROUP BY substr(o.order_date, 1, 7)
)
SELECT substr(months.m, 1, 7) AS ym, COALESCE(g.gmv, 0) AS gmv
FROM months LEFT JOIN g ON g.ym = substr(months.m, 1, 7)
ORDER BY months.m;
ym | gmv 2024-01 | 30000.03 2024-02 | 55169.55 2024-03 | 9064.06 2024-04 | 8624.51 2024-05 | 39627.93 2024-06 | 0
对齐天数(或改用日均);补齐缺失日期;确认两个时间段的指标定义、过滤条件、状态口径完全一致; 最后解释异常点(大促、缺货、数据延迟)。四步做完,再写“增长/下降”两个字。
“每个用户最新一笔订单”“每个商品最近一次调价”“每个客户最近一次跟进”,都是同一道题:
按分组键分组,按时间倒序编号,取第一行。
SQLite、MySQL 8、PostgreSQL、Hive 都支持窗口函数,所以 ROW_NUMBER 是通用解。
| user_id | order_id | order_date | status | rn |
|---|---|---|---|---|
| U000001 | O24052807554 | 2024-05-28 | 已支付 | 1 |
| U000001 | O24032715331 | 2024-03-27 | 已支付 | 2 |
| U000001 | O24020911037 | 2024-02-09 | 已支付 | 3 |
三个细节决定这题对不对:先过滤(status='已支付' 要写在窗口之前)、
排序键要加决胜键(同一天两单时,用 order_id DESC 保证结果稳定)、
rn=1 要在外层过滤(窗口函数在 WHERE 之后才计算,同一层写 WHERE rn=1 会报错)。
按这个写法,2,000 个用户各取最新一笔,正好 2,000 行。
分组键决定“每个什么”(用户/商品/客户);排序键决定“什么算最新”(时间/版本号/金额); 决胜键决定“并列时选谁”(ID 倒序)。只按时间排序,遇到同一天多单,每次跑出来的结果可能不一样。
下面这道题,先别看答案。按“思路 → 骨架 → 答案”三段,每段用一句话写出来,再对照。
题目:6 月加购用户中,有多少人在加购后 1 分钟内下单?
第一段 · 思路(用人话写口径):分母是 6 月有加购行为的去重用户,共 1,318 人; 分子是这些人里,存在一对“加购 → 下单”且下单时间晚于加购、间隔不超过 1 分钟的用户; 按用户去重,不按事件数。
第二段 · 骨架(把口径翻成 SQL 结构):一个 CTE 取加购用户,一个 CTE 取满足时间条件的用户, 两表按 user_id 关联,外层数人数并除以分母。
| 段 | 你要回答的问题 | 本题的答案 |
|---|---|---|
| 思路 | 分母是谁?分子是什么?窗口多长? | 1,318 个加购用户;加购后 1 分钟内下单;按人去重 |
| 骨架 | 用几个 CTE?怎么 JOIN?在哪过滤? | 加购 CTE + 下单 CTE,按 user_id 关联,时间差在 JOIN 条件里 |
| 答案 | 数字是多少?和口径对得上吗? | 127 人,9.64% |
第三段 · 答案:127 人,转化率 9.64%。完整 SQL 见 16.3 第 5 题。 如果你上来就写 SQL,很可能会漏掉“下单时间必须晚于加购”这个条件,把“先下单后加购”的用户也算进去。
每道题都按五段写:题目、思路、SQL、结果、易错点。SQL 里的列名用英文别名,方便你直接复制运行; 结果都是在本章数据库上实跑出来的,不是示意值。
题目:6 月 1 日活跃用户中,6 月 2 日仍活跃的比例。
思路:D0 取 6/1 去重用户,D1 取 6/2 去重用户,LEFT JOIN 保住没回访的人。
WITH d0 AS (
SELECT DISTINCT user_id FROM events
WHERE date(event_time) = '2024-06-01'
),
d1 AS (
SELECT DISTINCT user_id FROM events
WHERE date(event_time) = '2024-06-02'
)
SELECT COUNT(*) AS d0_users,
COUNT(d1.user_id) AS d1_users,
ROUND(100.0 * COUNT(d1.user_id) / COUNT(*), 2) AS d1_rate
FROM d0 LEFT JOIN d1 ON d1.user_id = d0.user_id;
d0_users | d1_users | d1_rate 280 | 40 | 14.29
INNER JOIN 会丢掉 240 个没回访的人,分母变成 40,留存率变成 100%。题目:6/1 基准人群的第 1 日、第 3 日、3 日内、7 日内留存。
思路:“第 N 日”用等值日期,“N 日内”用 BETWEEN;窗口都从 D+1 开始,不含 D0。
WITH base AS (
SELECT DISTINCT user_id FROM events
WHERE date(event_time) = '2024-06-01'
),
act AS (
SELECT DISTINCT user_id, date(event_time) AS d FROM events
)
SELECT COUNT(DISTINCT b.user_id) AS d0,
COUNT(DISTINCT CASE WHEN a.d = '2024-06-02' THEN b.user_id END) AS day1,
COUNT(DISTINCT CASE WHEN a.d = '2024-06-03' THEN b.user_id END) AS day3,
COUNT(DISTINCT CASE WHEN a.d BETWEEN '2024-06-02' AND '2024-06-04' THEN b.user_id END) AS in3,
COUNT(DISTINCT CASE WHEN a.d BETWEEN '2024-06-02' AND '2024-06-08' THEN b.user_id END) AS in7
FROM base b LEFT JOIN act a ON a.user_id = b.user_id;
d0 | day1 | day3 | in3 | in7 280 | 40 | 44 | 112 | 200
题目:按注册月分组,注册后第 1 个月仍有已支付订单的用户比例。
思路:cohort 表按 register_date 取月,行为表按 order_date 取月;月份加 1 用 date(m||'-01','+1 month'),不要做字符串加法。
WITH coh AS (
SELECT user_id, substr(register_date,1,7) AS m FROM users
),
act AS (
SELECT DISTINCT user_id, substr(order_date,1,7) AS ym
FROM orders WHERE status = '已支付'
)
SELECT c.m AS cohort,
COUNT(DISTINCT c.user_id) AS users,
COUNT(DISTINCT CASE WHEN a.ym = strftime('%Y-%m', date(c.m || '-01', '+1 month'))
THEN c.user_id END) AS m1_users,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.ym = strftime('%Y-%m', date(c.m || '-01', '+1 month'))
THEN c.user_id END)
/ COUNT(DISTINCT c.user_id), 2) AS m1_rate
FROM coh c
LEFT JOIN act a ON a.user_id = c.user_id
WHERE c.m IN ('2023-01', '2023-06', '2024-01')
GROUP BY c.m
ORDER BY c.m;
cohort | users | m1_users | m1_rate 2023-01 | 124 | 34 | 27.42 2023-06 | 107 | 36 | 33.64 2024-01 | 108 | 43 | 39.81
INNER JOIN 会丢掉观察期内一单都没有的 cohort 用户,分母变小;如果只 JOIN M1 的数据,分母会从 124 变成 34,留存率变成 100%。月份加 1 用 date(m||'-01','+1 month'),2023-12 才不会变成 2023-13。题目:6 月浏览 → 加购 → 下单 → 支付的用户漏斗。
思路:每个用户一行,用 MAX(条件) 打四个 0/1 标记,再对标记求和。这样不用四次 JOIN。
WITH s AS (
SELECT user_id,
MAX(event_type = '浏览') AS v,
MAX(event_type = '加购') AS c,
MAX(event_type = '下单') AS o,
MAX(event_type = '支付') AS p
FROM events
GROUP BY user_id
)
SELECT SUM(v) AS view_users, SUM(c) AS cart_users,
SUM(o) AS order_users, SUM(p) AS pay_users,
ROUND(100.0 * SUM(c) / SUM(v), 2) AS view_to_cart,
ROUND(100.0 * SUM(o) / SUM(c), 2) AS cart_to_order,
ROUND(100.0 * SUM(p) / SUM(o), 2) AS order_to_pay,
ROUND(100.0 * SUM(p) / SUM(v), 2) AS overall_rate
FROM s;
view_users | cart_users | order_users | pay_users | view_to_cart | cart_to_order | order_to_pay | overall_rate 2000 | 1318 | 387 | 111 | 65.90 | 29.36 | 28.68 | 5.55
题目:6 月加购用户中,加购后 1 分钟内下单的人数与转化率。
思路:自连接两张事件表,条件里同时写“下单时间晚于加购”和“时间差 ≤ 1 分钟”,最后按用户去重。
WITH a AS (
SELECT DISTINCT user_id FROM events WHERE event_type = '加购'
),
b AS (
SELECT DISTINCT e1.user_id
FROM events e1
JOIN events e2 ON e2.user_id = e1.user_id
WHERE e1.event_type = '加购'
AND e2.event_type = '下单'
AND e2.event_time > e1.event_time
AND (julianday(e2.event_time) - julianday(e1.event_time)) * 1440 <= 1
)
SELECT (SELECT COUNT(*) FROM a) AS cart_users,
COUNT(*) AS order_in_1min,
ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM a), 2) AS rate
FROM b;
cart_users | order_in_1min | rate 1318 | 127 | 9.64
把窗口换成 1 小时和不限时间:1 小时 374 人(28.38%),不限时间 377 人(28.60%)。 1 分钟到 1 小时之间,转化从 9.64% 跳到 28.38%,说明大部分下单发生在加购后 1 小时内。
julianday() 的差单位是天,要乘 1440 才是分钟;漏写 > 会把“先下单后加购”也算进来。题目:6 月内下过单的用户中,下过 2 单及以上的比例。
思路:先按用户聚合出 6 月订单数,再在聚合结果上算比例。分母是 698 个买家,不是 2,000 个注册用户。
WITH u AS (
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE status = '已支付'
AND user_id IS NOT NULL
AND order_date >= '2024-06-01' AND order_date < '2024-07-01'
GROUP BY user_id
)
SELECT COUNT(*) AS buyers,
SUM(cnt >= 2) AS repeat_buyers,
ROUND(100.0 * SUM(cnt >= 2) / COUNT(*), 2) AS repeat_rate
FROM u;
buyers | repeat_buyers | repeat_rate 698 | 138 | 19.77
substr(order_date,1,7)='2024-06' 会让索引失效,日期边界也不如 >= 月初 AND < 次月初 清楚。题目:6 月复购用户的平均和中位复购间隔。
思路:按用户、按日期排序,用 LAG 取上一单日期,julianday 相减得天數;中位数用排序后取中间一行。
WITH p AS (
SELECT user_id, date(order_date) AS d
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
AND order_date >= '2024-06-01' AND order_date < '2024-07-01'
),
x AS (
SELECT user_id, d,
julianday(d) - julianday(LAG(d) OVER (PARTITION BY user_id ORDER BY d)) AS gap
FROM p
)
SELECT COUNT(*) AS gaps,
ROUND(AVG(gap), 2) AS avg_gap,
(SELECT gap FROM x WHERE gap IS NOT NULL
ORDER BY gap LIMIT 1 OFFSET (SELECT (COUNT(*) - 1) / 2 FROM x WHERE gap IS NOT NULL)) AS median_gap
FROM x
WHERE gap IS NOT NULL;
gaps | avg_gap | median_gap 154 | 10.32 | 10
全期同一口径:13,176 个间隔,平均 61.27 天,中位数 43 天。 平均值被长尾拉高,中位数更能代表“典型用户”。
order_date - LAG(order_date),SQLite 会把日期字符串转成数字,跨月会算出 100 这种怪值;SQLite 没有 DATEDIFF,要用 julianday。题目:每个品类 GMV 前 2 的商品。
思路:先聚合到“品类 × 商品”,再用 ROW_NUMBER 按品类分组编号,最后在外层取 rn <= 2。
WITH g AS (
SELECT p.category, p.product_name, ROUND(SUM(i.amount), 2) AS gmv
FROM order_items i
JOIN products p ON p.product_id = i.product_id
JOIN orders o ON o.order_id = i.order_id AND o.status = '已支付'
GROUP BY p.category, p.product_name
),
r AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY gmv DESC, product_name) AS rn
FROM g
)
SELECT category, product_name, gmv, rn
FROM r WHERE rn <= 2
ORDER BY category, rn;
category | product_name | gmv | rn 家电 | Lumen 家电04 | 2878601.60 | 1 家电 | Lumen 家电01 | 2834923.52 | 2 手机 | Nova 手机10 | 3271821.84 | 1 手机 | Lumen 手机01 | 2649222.48 | 2 服饰 | Vertex 服饰04 | 2704384.53 | 1 服饰 | Nova 服饰02 | 2649644.52 | 2 电脑 | Vertex 电脑08 | 2990660.64 | 1 电脑 | Vertex 电脑06 | 2589258.98 | 2 配件 | Vertex 配件07 | 2723371.14 | 1 配件 | Vertex 配件05 | 2696132.16 | 2
ROW_NUMBER 不能写在同层 WHERE 里;用 LIMIT 2 只取全局前 2;用 RANK 遇到并列会返回超过 2 行。题目:品类 GMV 从高到低,算占比和累计占比,看几个品类能贡献 80%。
思路:窗口求和必须写 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,分母用总额子查询。
WITH g AS (
SELECT p.category, ROUND(SUM(i.amount), 2) AS gmv
FROM order_items i
JOIN products p ON p.product_id = i.product_id
JOIN orders o ON o.order_id = i.order_id AND o.status = '已支付'
GROUP BY p.category
),
t AS (SELECT SUM(gmv) AS total FROM g)
SELECT category, gmv,
ROUND(100.0 * gmv / (SELECT total FROM t), 2) AS pct,
ROUND(100.0 * SUM(gmv) OVER (
ORDER BY gmv DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) / (SELECT total FROM t), 2) AS cum_pct
FROM g
ORDER BY gmv DESC;
category | gmv | pct | cum_pct 手机 | 47570925.64 | 30.13 | 30.13 电脑 | 33756338.97 | 21.38 | 51.51 家电 | 25939444.18 | 16.43 | 67.94 配件 | 25503426.77 | 16.15 | 84.10 服饰 | 25105176.08 | 15.90 | 100.00
累计到第 4 个品类(配件)时达到 84.10%,所以贡献 80% 的是前 4 个品类,不是前 3 个。
ROWS,SQLite 默认 RANGE,遇到并列会把并列行一起加进来;分母用当前行或全表行数,会得到荒唐的占比。题目:2024 年 1–6 月 GMV 环比。
思路:先按月聚合,再用 LAG 取上月;第一行没有上月,结果为 NULL,别用 0 除。
WITH m AS (
SELECT substr(order_date, 1, 7) AS ym, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders
WHERE status = '已支付'
AND order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY substr(order_date, 1, 7)
)
SELECT ym, gmv,
LAG(gmv) OVER (ORDER BY ym) AS prev_gmv,
ROUND(100.0 * (gmv - LAG(gmv) OVER (ORDER BY ym))
/ LAG(gmv) OVER (ORDER BY ym), 2) AS mom_pct
FROM m
ORDER BY ym;
ym | gmv | prev_gmv | mom_pct 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
2024-10 会排在 2024-2 前面;第一行 prev_gmv 为 NULL,除法前要判断;用订单数环比(6 月 +1.97%)代替 GMV 环比(+2.85%),两者不是一回事。题目:2024 年 6 月 GMV 同比。
思路:用条件聚合把两年同月放在一行,日期范围都写成 [月初, 次月初),避免月末时分秒漏算。
SELECT
ROUND(SUM(CASE WHEN order_date >= '2024-06-01' AND order_date < '2024-07-01'
THEN pay_amount END), 2) AS y2024,
ROUND(SUM(CASE WHEN order_date >= '2023-06-01' AND order_date < '2023-07-01'
THEN pay_amount END), 2) AS y2023,
ROUND(100.0 * (
SUM(CASE WHEN order_date >= '2024-06-01' AND order_date < '2024-07-01' THEN pay_amount END)
/ SUM(CASE WHEN order_date >= '2023-06-01' AND order_date < '2023-07-01' THEN pay_amount END)
- 1), 2) AS yoy_pct
FROM orders
WHERE status = '已支付';
y2024 | y2023 | yoy_pct 9015366.64 | 8875566.96 | 1.58
LIKE '2024-06%' 在 SQLite 默认可能走全扫描(第 17 章会讲),更稳的是范围;只写 >= '2024-06-01' 不写上界,会把 7 月也算进来;同比和环比的分母不是同一个月份。题目:每个用户最新一笔已支付订单的订单号、日期、金额。
思路:ROW_NUMBER 按用户分组、按时间倒序,外层取 rn=1;同一天多单用 order_id DESC 决胜。
WITH r AS (
SELECT user_id, order_id, order_date, pay_amount,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
)
SELECT user_id, order_id, order_date, ROUND(pay_amount, 2) AS pay_amount
FROM r
WHERE rn = 1
ORDER BY user_id
LIMIT 5;
user_id | order_id | order_date | pay_amount U000001 | O24052807554 | 2024-05-28 10:18:08 | 14769.19 U000002 | O24061305805 | 2024-06-13 07:45:07 | 1801.28 U000003 | O24061602798 | 2024-06-16 21:45:45 | 21643.72 U000004 | O24042319265 | 2024-04-23 00:22:28 | 8643.97 U000005 | O24030902651 | 2024-03-09 06:09:11 | 5210.86
把 LIMIT 5 去掉,返回 2,000 行,正好每个用户一行。
WHERE rn=1 写在同一层会报错;只按 order_date 排序,同一天两单结果不稳定;用 GROUP BY user_id + MAX(order_date) 只能拿到日期,拿不到对应的订单号和金额。先自己做,再展开答案。每题都基于本章数据库,答案里的数字都是实跑结果。
1. 44 人,15.71%。2. 200 人,71.43%。3. 112 人,40.00%。
4. 43 ÷ 108 = 39.81%。5. 32 ÷ 107 = 29.91%。
6. 1,318 − 387 = 931 人。
7. 127 ÷ 1,318 = 9.64%。8. 138 ÷ 698 = 19.77%。9. 1,997 ÷ 2,000 = 99.85%。
10. 10 天(154 个间隔,平均 10.32 天)。
11. +2.85%(9,015,366.64 vs 8,765,340.49)。12. +1.58%。13. +3.03%(53,577,010.96 vs 52,003,005.24)。
14. 手机,47,570,925.64,占 30.13%。15. 4 个,累计 84.10%。
16. 9,015,366.64 ÷ 881 = 10,233.11。17. 565 ÷ 20,000 = 2.83%。
18. 6/28,316 人。19. 0(不补零则这一行不出现)。20. U001405,37,852.59。
留存/漏斗/复购题做出来“不像人话”,先在这张表里找原因。
| 现象 | 原因 | 解决 |
|---|---|---|
| 留存率 100% | INNER JOIN 丢掉了没回访的人,分母只剩回访者 | 基准表 LEFT JOIN 回访表,分母用基准表人数 |
| 留存率超过 100% | 分子用了“第 N 天全体活跃用户”,不是基准人群的子集 | 分子必须与基准表关联后再 COUNT |
| 漏斗转化率异常高 | 按事件数算,或没有按用户去重 | COUNT(DISTINCT user_id),并确认步骤顺序 |
| 复购率 99% | 观察窗口太长(如 18 个月),几乎人人都会复购 | 写清窗口;月复购看月,年复购看年 |
| 复购间隔是负数 | 没过滤“后一单晚于前一单”,或同日多单排序不稳 | 加时间条件;按 order_date、order_id 排序 |
| 环比第一行报错 | LAG 首行为 NULL,直接做了除法 | 用 NULLIF 或 CASE 跳过首行 |
| 月度趋势少一个月 | 直接 GROUP BY,缺失月不产生行 | 日历 CTE LEFT JOIN,COALESCE 补 0 |
| 最新订单取错 | 只按日期排序,同一天多单没有决胜键 | ORDER BY 时间 DESC, 订单号 DESC |