数据分析师训练营/ 第三篇 SQL/ 第十六章
第十六章 · SQL 分析题型

分析题型集训

这一章不教新语法,教的是看到题先问什么。留存、漏斗、复购、同环比、去重取最新, 五类题翻来覆去考的都是同一件事:分母是谁、窗口多长、按什么去重。 同一份数据,分母选错,结论就能从“用户很忠诚”变成“用户全跑了”。

本章用青柠优选的 2,000 个用户、20,000 笔订单和 16,932 条行为,把 12 道高频题拆成 题目 → 思路 → SQL → 结果 → 易错点,再留 20 道练习。做完这一章,你看到新题应该先画口径,而不是先敲 SELECT。

理论 : 实操 = 3 : 7 约 10 学时 前置 第 15 章

16.1原理:五类题型的口径

面试和日常取数,题目通常只有一句话。真正决定答案的,是你补出来的那三四个口径。 这一节先把五类题型的“口径清单”立起来,后面 12 道题都回头引用它。

16.1.1留存:第 N 日留存 vs N 日内留存

留存题先定三件事:基准人群(谁算“第一天来了”)、回访定义(什么行为算“回来”)、 时间窗口(只看第 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 当天)4028014.29%
第 3 日(6/3 当天)4428015.71%
3 日内(6/2–6/4)11228040.00%
7 日内(6/2–6/8)20028071.43%
图 16.1.1 同一批用户,“第 N 日”和“N 日内”能差出 5 倍

这张图是本章第一个反直觉点:第 1 日只有 14.29%,7 日内却有 71.43%。 如果你在周报里只写“留存 14%”,老板会以为用户全跑了;写“7 日内留存 71%”,结论完全相反。 所以留存率后面必须跟窗口,就像长度后面必须跟单位。

基准日也一样:选注册日,D0 可能一笔订单都没有;选首次活跃日或活动开始日,D0 一定有行为。 两套口径不能混着比——注册日看“激活速度”,活跃日看“回访黏性”。

分母最容易错。6 月 2 日的活跃用户是 310 人,不是 40 人。拿 310 当分子、 280 当分母,会算出 110.71% 的留存率——数学上就不可能。分子必须是“基准人群里回来的人”, 不是“第二天所有活跃的人”。
错误:分子取 6/2 全体活跃

310 ÷ 280 = 110.71%,留存率超过 100%,数学上不可能。

正确:分子只取基准人群的回访者

40 ÷ 280 = 14.29%,分子必须是分母的子集。

1

留存三要素:基准人群、回访定义、时间窗口

基准人群决定分母:是“某日活跃用户”“某日注册用户”,还是“某活动曝光用户”。 回访定义决定分子:是任意事件、登录、下单,还是支付。 时间窗口决定分子怎么数:第 N 日只数第 N 天,N 日内数第 1 到 N 天的并集。 三句话写不出来,这道题就还没读懂。

16.1.2漏斗:事件序列、按用户去重、时间窗口、严格/宽松

漏斗的本质是有序步骤,不是四个互不相干的计数。浏览 → 加购 → 下单 → 支付, 每一步的人必须“已经通过上一步”。按用户去重后,四步人数是 2,000 → 1,318 → 387 → 111,整体转化率 111 ÷ 2,000 = 5.55%。

步骤人数单步转化累计转化
浏览2,000—100.00%
加购1,31865.90%65.90%
下单38729.36%19.35%
支付11128.68%5.55%
图 16.1.2 按用户去重的四步漏斗,流失人数最多的是“加购 → 下单”(931 人)

同一批行为,如果按事件数数,是 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%)。窗口越窄,转化越低——这不是数据变了,是口径变严了。

2

漏斗四问:步骤、去重、顺序、窗口

步骤从哪一步开始、到哪一步结束;计数按用户还是按事件;事件是否要求先后顺序; 两步之间允许多长时间。四问里最容易被漏掉的是“顺序”,其次是“窗口”。 只写“浏览到支付转化率 5.55%”,等于什么都没说清。

16.1.3复购:复购率、复购间隔、观察窗口

复购率的定义只有一句话:窗口内下单 ≥ 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 月69813819.77%
2023-01 至 2024-062,0001,99799.85%
图 16.1.3 窗口越长,复购率越高;跨窗口比较必须先对齐窗口

复购间隔则是另一回事。6 月内共有 154 个“相邻两单”的间隔,平均 10.32 天, 中位数 10 天;放到全期,共 13,176 个间隔,平均 61.27 天,中位数 43 天。 平均值明显大于中位数,说明有一批用户隔了很久才回来,把均值拉长了。 汇报“典型复购间隔”时,中位数比平均值更抗极端值。

窗口截断:6 月只有 30 天,你不可能观察到 60 天的间隔。短窗口算出来的“平均间隔”天然偏短, 这不是用户变勤快了,是长间隔被窗口切掉了。
3

复购三件事:窗口、分母、间隔口径

先写窗口(月/季/年/自定义),再写分母(窗口内买家,不是注册用户), 最后写间隔(相邻订单日期差,还是首单到第二单)。三件事缺一件,复购率就无法复现。

16.1.4同环比:日期对齐、缺失月份补零

环比是“这个月和上个月比”,同比是“这个月和去年同月比”。听起来简单,但有两个坑: 天数不一样、缺失的月份不出现。

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 月
GMV30,000.0355,169.559,064.068,624.5139,627.930
图 16.1.4 武汉黑卡用户 6 月无单,补零后趋势才不会“跳月”
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
4

同环比四步:对齐、补零、同口径、解释异常

对齐天数(或改用日均);补齐缺失日期;确认两个时间段的指标定义、过滤条件、状态口径完全一致; 最后解释异常点(大促、缺货、数据延迟)。四步做完,再写“增长/下降”两个字。

16.1.5去重取最新:ROW_NUMBER 万能解

“每个用户最新一笔订单”“每个商品最近一次调价”“每个客户最近一次跟进”,都是同一道题: 按分组键分组,按时间倒序编号,取第一行。 SQLite、MySQL 8、PostgreSQL、Hive 都支持窗口函数,所以 ROW_NUMBER 是通用解。

user_idorder_idorder_datestatusrn
U000001O240528075542024-05-28已支付1
U000001O240327153312024-03-27已支付2
U000001O240209110372024-02-09已支付3
图 16.1.5 先过滤“已支付”,再按时间倒序编号,rn=1 就是最新一单

三个细节决定这题对不对:先过滤(status='已支付' 要写在窗口之前)、 排序键要加决胜键(同一天两单时,用 order_id DESC 保证结果稳定)、 rn=1 要在外层过滤(窗口函数在 WHERE 之后才计算,同一层写 WHERE rn=1 会报错)。 按这个写法,2,000 个用户各取最新一笔,正好 2,000 行。

5

去重取最新三件套:分组键、排序键、决胜键

分组键决定“每个什么”(用户/商品/客户);排序键决定“什么算最新”(时间/版本号/金额); 决胜键决定“并列时选谁”(ID 倒序)。只按时间排序,遇到同一天多单,每次跑出来的结果可能不一样。

16.2分析:一道题的三段式提示

下面这道题,先别看答案。按“思路 → 骨架 → 答案”三段,每段用一句话写出来,再对照。

题目:6 月加购用户中,有多少人在加购后 1 分钟内下单?

第一段 · 思路(用人话写口径):分母是 6 月有加购行为的去重用户,共 1,318 人; 分子是这些人里,存在一对“加购 → 下单”且下单时间晚于加购、间隔不超过 1 分钟的用户; 按用户去重,不按事件数。

第二段 · 骨架(把口径翻成 SQL 结构):一个 CTE 取加购用户,一个 CTE 取满足时间条件的用户, 两表按 user_id 关联,外层数人数并除以分母。

段你要回答的问题本题的答案
思路分母是谁?分子是什么?窗口多长?1,318 个加购用户;加购后 1 分钟内下单;按人去重
骨架用几个 CTE?怎么 JOIN?在哪过滤?加购 CTE + 下单 CTE,按 user_id 关联,时间差在 JOIN 条件里
答案数字是多少?和口径对得上吗?127 人,9.64%
图 16.2.1 三段式提示:先写口径,再搭骨架,最后才填数

第三段 · 答案:127 人,转化率 9.64%。完整 SQL 见 16.3 第 5 题。 如果你上来就写 SQL,很可能会漏掉“下单时间必须晚于加购”这个条件,把“先下单后加购”的用户也算进去。

16.3题型精讲:12 道

每道题都按五段写:题目、思路、SQL、结果、易错点。SQL 里的列名用英文别名,方便你直接复制运行; 结果都是在本章数据库上实跑出来的,不是示意值。

16.3.1次日留存

题目: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
易错点:用 6/2 的 310 个活跃用户当分子,会得到 110.71%;用 INNER JOIN 会丢掉 240 个没回访的人,分母变成 40,留存率变成 100%。

16.3.2第 N 日 vs N 日内

题目: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
易错点:把“3 日内”写成 6/1–6/3,会把 D0 当天算进去;把“第 3 日”写成“3 日内”,15.71% 会被误报成 40.00%。

16.3.3同期群留存

题目:按注册月分组,注册后第 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。

16.3.4漏斗(按用户去重)

题目: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
易错点:用 8,822 / 5,816 / 1,793 / 501 这些事件数当人数,会算出 65.93% / 30.83% / 27.94% / 5.68%,和按人的结果接近但含义不同。漏斗题默认按人。

16.3.5漏斗时间窗口

题目: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 才是分钟;漏写 > 会把“先下单后加购”也算进来。

16.3.6复购率

题目: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
易错点:分母写成 2,000,会得到 6.90%;把“已退款”算进复购,会虚高;只写 substr(order_date,1,7)='2024-06' 会让索引失效,日期边界也不如 >= 月初 AND < 次月初 清楚。

16.3.7复购间隔

题目: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。

16.3.8分组 TopN

题目:每个品类 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 行。

16.3.9累计占比(帕累托)

题目:品类 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,遇到并列会把并列行一起加进来;分母用当前行或全表行数,会得到荒唐的占比。

16.3.10环比

题目: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%),两者不是一回事。

16.3.11同比

题目: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 月也算进来;同比和环比的分母不是同一个月份。

16.3.12去重取最新

题目:每个用户最新一笔已支付订单的订单号、日期、金额。

思路: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) 只能拿到日期,拿不到对应的订单号和金额。

16.4练习:20 道 + 6 道选择题

先自己做,再展开答案。每题都基于本章数据库,答案里的数字都是实跑结果。

16.4.120 道练习

  1. 6/1 活跃用户中,6/3 当天回访多少人?留存率多少?
  2. 6/1 基准人群的 7 日内留存率是多少?
  3. 6/1 基准人群的 3 日内留存率是多少?
  4. 2024-01 注册 cohort 的 M1 留存率是多少?
  5. 2023-06 注册 cohort 的 M2 留存率是多少?
  6. 漏斗中“加购 → 下单”流失了多少人?
  7. 6 月加购用户中,1 分钟内下单的比例是多少?
  8. 6 月复购率(≥2 单)是多少?
  9. 全期复购率(≥2 单)是多少?
  10. 6 月复购间隔的中位数是多少天?
  11. 2024-06 GMV 环比是多少?
  12. 2024-06 GMV 同比是多少?
  13. 2024 年上半年 GMV 同比是多少?
  14. 品类 GMV 第一是谁?占比多少?
  15. 品类累计占比达到 80% 需要几个品类?
  16. 2024-06 客单价是多少?
  17. 游客订单占比是多少?
  18. 2024-06 活跃用户数的最大值出现在哪天?是多少?
  19. 武汉黑卡用户 2024-06 的 GMV 是多少?
  20. 每个用户最新一笔已支付订单中,金额最高的是谁?
参考答案(20 题)

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。

16.4.2六道选择题

Q16/1 活跃 280 人,6/2 回访 40 人。第 1 日留存率是多少?
解析:40 ÷ 280 = 14.29%。12.90% 是拿 40 除以 6/2 的 310 个活跃用户,分母错了。
Q2漏斗 2,000 → 1,318 → 387 → 111,整体转化率是多少?
解析:整体转化 = 最后一步 ÷ 第一步 = 111 ÷ 2,000 = 5.55%。28.68% 只是“下单 → 支付”的单步转化。
Q36 月有 698 个买家,其中 138 人复购。复购率是多少?
解析:复购率的分母是窗口内买家(698),不是注册用户(2,000)。138 ÷ 698 = 19.77%。
Q45 月 GMV 8,765,340.49,6 月 9,015,366.64。6 月环比是多少?
解析:9,015,366.64 ÷ 8,765,340.49 − 1 = +2.85%。+1.58% 是同比;+6.28% 是日均环比,因为 5 月 31 天、6 月 30 天。
Q5取每个用户最新一笔已支付订单,哪种写法正确?
解析:窗口函数在 WHERE 之后计算,必须套一层子查询/CTE;GROUP BY + MAX 只能拿到日期,拿不到同一行的订单号和金额。
Q6杭州 6 月有两天没有已支付订单,直接 GROUP BY 日期会怎样?
解析:GROUP BY 只对有数据的行分组,缺失日期不会凭空出现。要先用日历 CTE 造出 30 天,再 LEFT JOIN 并 COALESCE 补 0。
得分:0 / 6

16.4.3数值核对

40 ÷ 280,保留两位小数。
按用户去重后的支付人数。
138 ÷ 698,保留两位小数。

16.5常见问题排查

留存/漏斗/复购题做出来“不像人话”,先在这张表里找原因。

现象原因解决
留存率 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

16.6本章小结

  1. 留存先写窗口。第 1 日 14.29% 和 7 日内 71.43% 说的是两件事,不能混着汇报。
  2. 漏斗先问四件事。步骤、去重、顺序、窗口;按人 111、按事件 501,含义不同。
  3. 复购先定分母。6 月复购 19.77%,全期 99.85%;分母是窗口内买家,不是注册用户。
  4. 同环比先对齐。总量环比 +2.85%,日均环比 +6.28%;缺失月份要补 0。
  5. 去重取最新用 ROW_NUMBER。分组键、排序键、决胜键,一个都不能少。
60 分钟自测:关掉本章,从 16.4 的 20 道题里随机抽 10 道,先写口径再写 SQL。 10 道里对 8 道,这一章就算过关;错的那两道,回到 16.3 找同题型的例题,对照“易错点”重写一遍。
数据分析师训练营 · 第十六章
SQLite 3 · 所有数字均来自 qingning.db 实跑