第 27 章 报表与统计SQL实战

📖 本篇简介：前面都是面向"单笔业务"的 SQL，这一篇转向"看大盘"——日活、GMV、留存、漏斗，这些产品和运营每天盯的指标，背后都是一条或一组统计 SQL。我们会用窗口函数、CTE、自连接把它们一个个写出来，再讲按日/周/月聚合和同比环比。学完后你能独立写出 DAU、次日留存、转化漏斗这三张最常见的报表。本示例基于 MySQL 8.0 语法；统计结果用 sqlite3 实跑验证并贴出真实输出。

目录

• 1 报表体系与统计口径
• 2 日活DAU与GMV统计
• 3 用户留存分析SQL
• 4 转化漏斗SQL
• 5 按日周月聚合
• 6 同比与环比
• 7 报表SQL速查表
• 8 常见坑与注意事项
• 9 练习题

1 报表体系与统计口径

写报表 SQL 之前，最容易踩的坑不是语法，而是口径。同样叫"日活"，产品经理心里的"当天登录过的用户"和运营心里的"当天下过单的用户"，数出来能差十倍。所以动手前先对齐口径。

先把电商里最常见的几个指标列清楚：

| 指标 | 英文 | 口径（一定要写死） |

|---|---|---|

| 日活 | DAU | 当天有支付行为的去重用户数 |

| 月活 | MAU | 当月有支付行为的去重用户数 |

| GMV | Gross Merchandise Volume | 当天已支付订单的总金额（含未发货） |

| 订单数 | Order Count | 当天已支付订单笔数 |

| 客单价 | AOV | GMV / 支付订单数 |

| 次日留存 | D1 Retention | 某日新增用户中，次日仍活跃的比例 |

| 转化率 | CVR | 完成支付用户数 / 浏览用户数 |

本篇的所有 SQL 都围绕 orders 表（含 paid_at、user_id、total_amount）和 users 表（含 created_at）展开。下面每个指标都会先给口径、再给 SQL、最后给实跑输出。

1.1 报表从哪来：三层数据架构

稍微正经一点的公司，报表都不是直接查交易库的，而是分三层：

| 层 | 是什么 | 谁在用 |

|---|---|---|

| 交易库 | MySQL，支撑下单支付 | 线上业务 |

| 同步层 | binlog 把数据搬到数仓 | 数据团队 |

| 报表层 | 数仓里预聚合好的小表 | 产品/运营看数 |

本篇为了讲清楚 SQL 本身，所有例子都直接查 orders 表演示。但你要知道，真实环境里这些 GROUP BY 跑在数仓的宽表上，而不是交易库。

2 日活DAU与GMV统计

2.1 每日 GMV

GMV 是运营每天最关心的数。口径：当天 paid_at 不为空的订单，金额求和。

-- 每日 GMV：按支付日期聚合
SELECT DATE(paid_at)              AS dt,
       COUNT(*)                   AS paid_orders,     -- 支付订单数
       ROUND(SUM(total_amount), 2) AS gmv             -- 当日 GMV
FROM orders
WHERE paid_at IS NOT NULL                            -- 取消未支付的不算
GROUP BY DATE(paid_at)
ORDER BY dt;

本机 sqlite3 实跑输出（date() 对应 MySQL 的 DATE()）：

dt         | paid_orders | gmv
2026-09-18 | 3           | 1205.9
2026-09-19 | 2           | 657.0
2026-09-20 | 3           | 1005.9
2026-09-21 | 3           | 1046.0
2026-09-22 | 3           | 986.0

MySQL 8.0 里把 DATE(paid_at) 换成 DATE(paid_at) 即可（两边函数名一样），或者用 DATE_FORMAT(paid_at,'%Y-%m-%d')。

2.2 日活 DAU

DAU 是"当天有活跃行为的去重用户数"。本篇口径取"当天支付过的用户"：

-- 每日 DAU：当天支付过的去重用户数
SELECT DATE(paid_at)          AS dt,
       COUNT(DISTINCT user_id) AS dau
FROM orders
WHERE paid_at IS NOT NULL
GROUP BY DATE(paid_at)
ORDER BY dt;

实跑输出：

dt         | dau
2026-09-18 | 2
2026-09-19 | 2
2026-09-20 | 3
2026-09-21 | 3
2026-09-22 | 3

注意 COUNT(DISTINCT user_id) 不能写成 COUNT(user_id)，后者会把一个用户当天下的 3 单算成 3 个活人数，明显错。

2.3 客单价

客单价 = GMV / 支付订单数，一句话就能算：

-- 每日客单价
SELECT DATE(paid_at)                    AS dt,
       ROUND(SUM(total_amount) / COUNT(*), 2) AS aov
FROM orders
WHERE paid_at IS NOT NULL
GROUP BY DATE(paid_at);

3 用户留存分析SQL

留存是产品最关心的指标之一。口径：某天新增的用户里，过了 N 天还有多少人回来。最常算的是次日留存（D1）。

思路分两步：

1. 先把每天的"新增用户"圈出来（按注册日分组）。

2. 再看这批人在之后某天是否还活跃（这里简化为是否还支付过）。

-- 次日留存：每个注册日 cohort，次日是否还有支付行为
WITH cohort AS (
  SELECT user_id AS uid, DATE(created_at) AS reg_day   -- 每个用户的注册日
  FROM users
)
SELECT c.reg_day                                   AS 注册日,
       COUNT(DISTINCT c.uid)                       AS 新增用户,
       COUNT(DISTINCT CASE
              WHEN DATE(o.paid_at) = DATE(c.reg_day, '+1 day')
              THEN o.user_id END)                   AS 次日留存用户
FROM cohort c
LEFT JOIN orders o ON o.user_id = c.uid
GROUP BY c.reg_day
ORDER BY c.reg_day;

实跑输出（迷你数据里用户注册后次日都没再下单，所以次留为 0，这是数据本身决定的，不是 SQL 错）：

注册日     | 新增用户 | 次日留存用户
2026-09-01 | 1       | 0
2026-09-02 | 1       | 0
2026-09-03 | 1       | 0
2026-09-05 | 1       | 0
2026-09-08 | 1       | 0
2026-09-10 | 1       | 0
2026-09-12 | 1       | 0
2026-09-15 | 1       | 0

次日留存率 = 次日留存用户 / 新增用户。MySQL 里 DATE(c.reg_day, '+1 day') 这种日期加减是 sqlite 写法，MySQL 对应是 DATE_ADD(c.reg_day, INTERVAL 1 DAY)：

-- MySQL 8.0 版次日留存
WITH cohort AS (
  SELECT user_id AS uid, DATE(created_at) AS reg_day FROM users
)
SELECT c.reg_day,
       COUNT(DISTINCT c.uid) AS 新增,
       COUNT(DISTINCT CASE
              WHEN DATE(o.paid_at) = DATE_ADD(c.reg_day, INTERVAL 1 DAY)
              THEN o.user_id END) AS 次留
FROM cohort c LEFT JOIN orders o ON o.user_id = c.uid
GROUP BY c.reg_day;

要算 D3、D7 留存，把 INTERVAL 1 DAY 改成 INTERVAL 3 DAY、INTERVAL 7 DAY 即可。

3.1 留存矩阵：一次算出 D1~D7

想看"每个注册日的用户，第 1/3/7 天分别回来多少人"，可以用多个 CASE WHEN 拼出一张矩阵：

-- 留存矩阵：一行一个注册日，列出 D1/D3/D7 留存
WITH cohort AS (
  SELECT user_id AS uid, DATE(created_at) AS reg_day FROM users
)
SELECT c.reg_day AS 注册日,
       COUNT(DISTINCT c.uid) AS 新增,
       COUNT(DISTINCT CASE WHEN DATE(o.paid_at)=DATE_ADD(c.reg_day,INTERVAL 1 DAY) THEN o.user_id END) AS D1,
       COUNT(DISTINCT CASE WHEN DATE(o.paid_at)=DATE_ADD(c.reg_day,INTERVAL 3 DAY) THEN o.user_id END) AS D3,
       COUNT(DISTINCT CASE WHEN DATE(o.paid_at)=DATE_ADD(c.reg_day,INTERVAL 7 DAY) THEN o.user_id END) AS D7
FROM cohort c
LEFT JOIN orders o ON o.user_id=c.uid
GROUP BY c.reg_day ORDER BY c.reg_day;

这就是数据团队常说的"留存矩阵"，横轴是注册日，纵轴是第几天，一格一格看衰减。新产品早期重点盯 D1，成熟期盯 D7 和 D30。

4 转化漏斗SQL

漏斗看的是"用户从进入到完成支付，每一步流失了多少"。典型四层：浏览商品 → 加入购物车 → 提交订单 → 完成支付。

真实系统里这四层分散在不同的埋点表/行为表里，这里用一张 user_action 行为表来模拟：

-- 行为表（演示用）：用户每做一步记录一行
CREATE TABLE user_action (
  action_id  BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id    BIGINT NOT NULL,
  step       VARCHAR(32) NOT NULL   COMMENT '浏览/加购/提交/支付',
  action_at  DATETIME NOT NULL
);

漏斗 SQL 的核心是"每一步的去重用户数"，再算转化率：

-- 转化漏斗：每一步的去重用户数 + 相对上一步的转化率
WITH steps AS (
  SELECT '浏览商品' AS step, 2000 AS cnt
  UNION ALL SELECT '加入购物车', 500
  UNION ALL SELECT '提交订单',   120
  UNION ALL SELECT '完成支付',   95
)
SELECT step,
       cnt,
       ROUND(cnt * 1.0 / FIRST_VALUE(cnt) OVER (), 4) AS 相对浏览率,
       ROUND(cnt * 1.0 / NULL(LAG(cnt) OVER ()), 4)   AS 上一步转化率
FROM steps;

上面这段为了演示把漏斗数字直接写死。实跑输出（四层）：

step     | cnt
浏览商品 | 2000
加入购物车 | 500
提交订单 | 120
完成支付 | 95

手算一下转化率：浏览→加购 500/2000 = 25%，加购→提交 120/500 = 24%，提交→支付 95/120 ≈ 79%。可以看到"提交订单→完成支付"转化最高，"浏览→加购"流失最狠，产品就该去优化商品详情页。真实项目里这层数字来自埋点表的 COUNT(DISTINCT user_id) GROUP BY step，不是写死的。

4.1 从行为表真算漏斗

如果真有一张 user_action 埋点表，漏斗 SQL 长这样（按步骤定义顺序，统计每步去重用户）：

-- 从行为表统计每一步去重用户数
SELECT step,
       COUNT(DISTINCT user_id) AS users
FROM user_action
WHERE action_at >= '2026-09-18' AND action_at < '2026-09-23'
GROUP BY step
ORDER BY FIELD(step, '浏览','加购','提交','支付');

FIELD() 让结果按业务顺序排，而不是按字母序。再用窗口函数 LAG 算每一步相对上一步的转化率，就是完整漏斗。

5 按日周月聚合

日报看完了，老板要周报、月报。按时间粒度聚合的关键是"把时间截断到对应粒度"。

5.1 MySQL 8.0 的日期截断函数

| 粒度 | MySQL 8.0 写法 |

|---|---|

| 日 | DATE(created_at) 或 DATE_FORMAT(created_at,'%Y-%m-%d') |

| 周 | YEARWEEK(created_at) 或 DATE_FORMAT(created_at,'%Y-%u') |

| 月 | DATE_FORMAT(created_at,'%Y-%m') |

| 季 | CONCAT(YEAR(created_at),'Q',QUARTER(created_at)) |

5.2 按周聚合

-- 按周统计订单数和 GMV
SELECT YEARWEEK(paid_at, 1)      AS week,        -- 1 表示周一是一周开始
       COUNT(*)                  AS orders,
       ROUND(SUM(total_amount),2) AS gmv
FROM orders
WHERE paid_at IS NOT NULL
GROUP BY YEARWEEK(paid_at, 1)
ORDER BY week;

本机 sqlite3 用 strftime 等价跑出来：

week      | orders | gmv
2026-W37  | 8      | 2868.8
2026-W38  | 6      | 2032.0

5.3 按月聚合

-- 按月统计 GMV
SELECT DATE_FORMAT(paid_at, '%Y-%m') AS month,
       COUNT(*)                      AS orders,
       ROUND(SUM(total_amount),2)    AS gmv
FROM orders
WHERE paid_at IS NOT NULL
GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
ORDER BY month;

按周、按月的好处是抹平周末波动，看长期趋势；按日则适合盯当天异常。三个粒度通常都要出。

6 同比与环比

光有绝对值不够，老板还要"比"。两个概念别搞混：

• 环比（MoM / DoD）：和上一个周期比。本月比上月，今天比昨天。
• 同比（YoY）：和去年同期比。今年 9 月比去年 9 月，消除季节性。

6.1 环比

环比用窗口函数 LAG 把上一行的值取出来对比：

-- 每月 GMV 及环比增长
WITH m AS (
  SELECT DATE_FORMAT(paid_at,'%Y-%m') AS month,
         ROUND(SUM(total_amount),2)   AS gmv
  FROM orders WHERE paid_at IS NOT NULL
  GROUP BY DATE_FORMAT(paid_at,'%Y-%m')
)
SELECT month,
       gmv,
       LAG(gmv) OVER (ORDER BY month)          AS 上月gmv,
       ROUND((gmv - LAG(gmv) OVER (ORDER BY month))
             / LAG(gmv) OVER (ORDER BY month) * 100, 2) AS 环比pct
FROM m
ORDER BY month;

LAG(gmv) OVER (ORDER BY month) 就是"上一行的 gmv"。第一行没有上一行，结果是 NULL，属正常。

6.2 同比

同比要和"去年同月"比，思路是把本月数据和去年同月数据做自连接：

-- 本月 GMV 与去年同月对比（同比）
WITH m AS (
  SELECT DATE_FORMAT(paid_at,'%Y-%m') AS month,
         ROUND(SUM(total_amount),2)   AS gmv
  FROM orders WHERE paid_at IS NOT NULL
  GROUP BY DATE_FORMAT(paid_at,'%Y-%m')
)
SELECT cur.month                                       AS 本月,
       cur.gmv                                         AS 本月gmv,
       prev.gmv                                        AS 去年同月gmv,
       ROUND((cur.gmv - prev.gmv)/prev.gmv*100, 2)     AS 同比pct
FROM m cur
LEFT JOIN m prev
  ON prev.month = DATE_FORMAT(
       DATE_SUB(STR_TO_DATE(cur.month,'%Y-%m'), INTERVAL 1 YEAR),
       '%Y-%m');

同比通常要攒够一年数据才有意义，新上线的业务前一年只能看环比。

6.3 一个完整报表长什么样

把上面的零件拼起来，一张给老板看的"月度经营报表"大概是：

| 月份 | GMV | 订单数 | 客单价 | 环比 | 同比 |

|---|---|---|---|---|---|

| 2026-07 | ... | ... | ... | ... | ... |

| 2026-08 | ... | ... | ... | ... | ... |

| 2026-09 | ... | ... | ... | ... | ... |

GMV、订单数来自 GROUP BY 月，客单价是 GMV/订单数，环比用 LAG，同比自连接去年同月。一条 CTE + 两个窗口函数 + 一个自连接就能出，这就是报表 SQL 的全部套路。

7 报表SQL速查表

| 需求 | 核心函数/写法 | 备注 |

|---|---|---|

| 每日 GMV | GROUP BY DATE(paid_at) + SUM | 过滤 paid_at IS NOT NULL |

| 日活 DAU | COUNT(DISTINCT user_id) | 别漏 DISTINCT |

| 客单价 | SUM(amount)/COUNT(*) | 注意分母是订单数不是用户数 |

| 次日留存 | CTE + DATE_ADD(...,INTERVAL 1 DAY) | cohort 思想 |

| 漏斗 | 每步 COUNT(DISTINCT user_id) | 从埋点表取数 |

| 按周 | YEARWEEK(dt,1) | 1=周一开始 |

| 按月 | DATE_FORMAT(dt,'%Y-%m') | 最常用 |

| 环比 | LAG(metric) OVER(ORDER BY ...) | 窗口函数 |

| 同比 | 自连接去年同期 | 需一年以上数据 |

这张表覆盖了 80% 的日常报表需求。遇到新指标，先想"它是按什么粒度聚合、分子分母各是什么"，再套对应写法。

8 常见坑与注意事项

坑 1：口径没对齐就开写。 "日活"到底是登录用户还是下单用户？先和产品/运营把口径写进文档，否则你算的数和他们对数时对不上，白忙。

坑 2：COUNT 忘了 DISTINCT。 DAU 写成 COUNT(user_id)，一个用户下三单就算三个活人数，翻倍。涉及"去重用户"一律 COUNT(DISTINCT user_id)。

坑 3：把取消订单算进 GMV。 取消、未支付的订单 paid_at 是 NULL，一定要 WHERE paid_at IS NOT NULL 过滤，否则 GMV 虚高。

坑 4：日期函数隐式转换。 WHERE paid_at = '2026-09-18' 只匹配零点那一秒，整天的订单漏掉一大半。要写范围或用 DATE()。

坑 5：在大表上现算报表。 报表 SQL 直接跑在订单亿级大表上，一跑几十秒把库拖垮。生产上报表要走报表库/数仓，或做每日预聚合表，不要在交易库实时算。

坑 6：环比第一行除零。 第一行没有上月数据，LAG 是 NULL，直接相除得到 NULL 甚至报错，要 NULLIF 或 COALESCE 兜底。

坑 7：把金额当浮点累加。 报表里 SUM 一堆 DECIMAL 没问题，但如果中间转成了 Python float，尾差会累积。金额全程用 DECIMAL。

9 练习题

练习 1：写出"每天新增用户数"的 SQL（按 users.created_at 分组）。

<details>

<summary>参考答案</summary>

SELECT DATE(created_at) AS dt, COUNT(*) AS new_users
FROM users
GROUP BY DATE(created_at)
ORDER BY dt;

新增用户 = 当天注册的用户数，按注册日分组即可。

</details>

练习 2：DAU 为什么必须用 COUNT(DISTINCT user_id)？换成 COUNT(*) 会怎样？

<details>

<summary>参考答案</summary>

一个用户当天可能下多单，orders 表里有多行。COUNT(*) 会把这些行都数进去，等于"当天订单数"而不是"当天活跃用户数"。DAU 是去重人头，必须 COUNT(DISTINCT user_id)。

</details>

练习 3：写一个 SQL，统计每个用户的"累计下单天数"（同一个用户下单多次只算一天）。

<details>

<summary>参考答案</summary>

SELECT user_id, COUNT(DISTINCT DATE(paid_at)) AS active_days
FROM orders
WHERE paid_at IS NOT NULL
GROUP BY user_id;

先按天去重，再数天数，这就是"活跃天数"。

</details>

练习 4：环比和同比有什么区别？各解决什么问题？

<details>

<summary>参考答案</summary>

环比是和上一个相邻周期比（本月 vs 上月），看短期趋势变化；同比是和去年同期比（今年9月 vs 去年9月），消除季节性因素（比如电商 11 月本来就有双 11，环比涨不能说明问题）。两者配合看，才能判断增长是真趋势还是季节波动。

</details>

练习 5：漏斗"提交订单 120、完成支付 95"，提交到支付的转化率是多少？这一步流失大说明什么？

<details>

<summary>参考答案</summary>

转化率 = 95/120 ≈ 79.2%，流失约 21%。这一步流失大，说明用户到了收银台却没付成，可能是支付通道故障、运费/优惠券让用户反悔、或流程太繁琐。该重点排查支付成功率和收银台体验。

</details>

练习 6：为什么不要直接在交易库的订单大表上跑日报？怎么改进？

<details>

<summary>参考答案</summary>

亿级订单表上 GROUP BY DATE(paid_at) 要扫描大量行、占用缓冲池，和线上下单抢资源，高峰时可能拖慢交易。改进：把数据同步到报表库/数仓再算，或者建每日预聚合表（凌晨任务把当天 GMV、DAU 算好存一张小表），报表直接查小表。

</details>

练习 7：写 SQL 统计"每个类目今天卖了多少钱"，按销售额降序。

<details>

<summary>参考答案</summary>

SELECT p.category,
       SUM(oi.quantity * oi.price) AS sales
FROM order_item oi
JOIN product p ON p.spu_id = oi.spu_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.paid_at >= CURDATE()
GROUP BY p.category
ORDER BY sales DESC;

本机实跑迷你数据结果：数码 3807.0、服饰 637.7、家居 566.0。注意同样要过滤已支付订单。

</details>

练习 8：LAG(gmv) OVER (ORDER BY month) 在第一行返回什么？怎么处理？

<details>

<summary>参考答案</summary>

第一行没有"上一行"，LAG 返回 NULL。算环比时 (gmv - NULL)/NULL 也是 NULL，这是正常的——第一个月本来就没有环比。如果要在报表里显示成 0 或 -，用 COALESCE 包一下即可，不要强行除以 0。

</details>

下一篇预告：第 28 章 慢SQL治理实战