SQL 实战编程题（GitHub 高频题） · 复杂业务分析

外卖红包：分析红包领取与核销率。

Food Delivery Coupons: Analyze claim and redemption rates.

答案语言

考察要点

这道题面试官想看你是否懂：

复杂业务逻辑的 SQL 翻译能力：能否将“红包领取”和“核销”两个业务行为，准确地转化为数据表之间的关联和计算逻辑。
JOIN 的正确使用：特别是 LEFT JOIN 和 INNER JOIN 的语义差别，以及在“有无”场景下为何必须使用 LEFT JOIN。
聚合函数与条件聚合：如何使用 GROUP BY 进行分组统计，并结合 CASE WHEN 或 COUNT(column) 的 NULL 特性实现单次扫描内的多维度聚合，这是衡量 SQL 熟练度的关键指标。

核心概念

要解决此问题，首先需要清晰地理解以下几个核心概念，并构建出对应的数据模型。

1. 数据模型（Schema）

通常，红包的领取和使用会记录在不同的表中。一个简化的模型如下：

红包领取记录表 (tb_red_packet_record): 记录了哪个用户在何时领取了哪个红包。

字段名类型描述
packet_id BIGINT 红包唯一 ID (主键)
user_id BIGINT 领取用户 ID
get_time DATETIME 领取时间
source VARCHAR 红包来源 (e.g., '活动A', '签到')
订单表 (tb_order): 记录了用户下单信息，如果使用了红包，会关联红包 ID。

字段名类型描述
order_id BIGINT 订单唯一 ID (主键)
user_id BIGINT 下单用户 ID
order_time DATETIME 下单时间
packet_id BIGINT 使用的红包 ID (可为 NULL)

字段名	类型	描述
`packet_id`	`BIGINT`	红包唯一 ID (主键)
`user_id`	`BIGINT`	领取用户 ID
`get_time`	`DATETIME`	领取时间
`source`	`VARCHAR`	红包来源 (e.g., '活动A', '签到')

字段名	类型	描述
`order_id`	`BIGINT`	订单唯一 ID (主键)
`user_id`	`BIGINT`	下单用户 ID
`order_time`	`DATETIME`	下单时间
`packet_id`	`BIGINT`	使用的红包 ID (可为 NULL)

2. `LEFT JOIN`

LEFT JOIN 是本题的关键。我们需要统计所有已领取的红包，看其中有多少被使用。

查询逻辑: 以红包领取记录表 (tb_red_packet_record) 为左表，订单表 (tb_order) 为右表。
关联条件: tb_red_packet_record.packet_id = tb_order.packet_id。
结果:
- 如果一个红包被使用了，LEFT JOIN 会找到匹配的订单记录。
- 如果一个红包未被使用，LEFT JOIN 仍然会保留左表（红包领取记录）的行，而右表（订单表）对应的所有列将为 NULL。

示例： tb_red_packet_record

packet_id	user_id	get_time
101	1	2023-10-01 10:00
102	1	2023-10-01 11:00

tb_order

order_id	user_id	packet_id
901	1	101

执行 ... FROM tb_red_packet_record r LEFT JOIN tb_order o ON r.packet_id = o.packet_id 后的中间结果：

r.packet_id	r.user_id	o.order_id	o.packet_id
101	1	901	101
102	1	NULL	NULL

3. 条件聚合

利用 LEFT JOIN 产生的 NULL 值，我们可以巧妙地进行聚合统计。

领取数: COUNT(r.packet_id)。统计左表的主键，结果就是总领取红包数。
核销数: COUNT(o.order_id)。COUNT(column) 会忽略 NULL 值。因此，只统计右表中非 NULL 的列（如 order_id），就能得到被核销的红包数。
核销率: 核销数 / 领取数。即 COUNT(o.order_id) / COUNT(r.packet_id)。

多种解法

假设题目要求：按天统计红包的领取数、核销数和核销率。

解法 1：`LEFT JOIN` + `GROUP BY` (最优解)

这是最标准、最高效的解法，通过一次表关联和聚合完成计算。

适用版本: ✅ 所有 SQL 数据库版本。
思路: 以红包领取表为基础，左连接订单表，然后按领取日期分组，在组内使用 COUNT 进行条件聚合。

sql

 1SELECT
 2    DATE(r.get_time) AS dt,
 3    COUNT(r.packet_id) AS received_count,
 4    COUNT(o.order_id) AS used_count,
 5    -- 使用 NULLIF 防止除零错误
 6    COUNT(o.order_id) / NULLIF(COUNT(r.packet_id), 0) AS redemption_rate
 7FROM
 8    tb_red_packet_record r
 9LEFT JOIN
10    tb_order o ON r.packet_id = o.packet_id
11GROUP BY
12    dt
13ORDER BY
14    dt;

时间复杂度: $O(N \log N + M \log M)$ 或 $O(N+M)$ ，取决于 JOIN 策略（Nested Loop Join 或 Hash Join）和 GROUP BY 的实现。如果 get_time 和 packet_id 有索引，性能会很高。
空间复杂度: $O(K)$ ，其中 K 是分组后的天数，用于存储聚合结果。
索引依赖:
- tb_red_packet_record 表需要 (get_time) 或 (get_time, packet_id) 索引，以优化 GROUP BY 和 JOIN。
- tb_order 表需要 (packet_id) 索引，以加速 JOIN 过程。

解法 2：`CTE` / 子查询 + 分步聚合

此方法将逻辑拆分，先分别计算领取和核销，再将结果合并。代码更模块化，但通常性能稍逊于解法 1。

适用版本: ✅ MySQL 8.0+ / PostgreSQL (使用 CTE)；✅ 所有版本 (使用子查询)。
思路:
1. 用一个 CTE (或子查询) received 按天统计红包领取量。
2. 用另一个 CTE (或子查询) used 统计已核销红包的领取日期和数量。
3. 将这两个结果 LEFT JOIN 起来，计算最终指标。

sql

 1-- 使用 CTE (Common Table Expressions) 的版本
 2WITH received AS (
 3    SELECT
 4        DATE(get_time) AS dt,
 5        COUNT(packet_id) AS received_cnt
 6    FROM
 7        tb_red_packet_record
 8    GROUP BY
 9        dt
10),
11used AS (
12    SELECT
13        DATE(r.get_time) AS dt,
14        COUNT(o.order_id) AS used_cnt
15    FROM
16        tb_red_packet_record r
17    JOIN -- 这里用 INNER JOIN 效率更高，因为我们只关心已核销的
18        tb_order o ON r.packet_id = o.packet_id
19    GROUP BY
20        dt
21)
22SELECT
23    r.dt,
24    r.received_cnt AS received_count,
25    COALESCE(u.used_cnt, 0) AS used_count,
26    COALESCE(u.used_cnt, 0) / NULLIF(r.received_cnt, 0) AS redemption_rate
27FROM
28    received r
29LEFT JOIN
30    used u ON r.dt = u.dt
31ORDER BY
32    r.dt;

时间复杂度: 通常需要两次对 tb_red_packet_record 表的扫描/聚合，可能比解法 1 慢。但如果聚合后的结果集很小，最终 JOIN 的开销可以忽略不计。
空间复杂度: $O(K_1 + K_2)$ ，其中 $K_1, K_2$ 分别是两个 CTE 聚合后的行数。
索引依赖:
- tb_red_packet_record(get_time)
- tb_order(packet_id)
- tb_red_packet_record(packet_id)

何时使用: 当逻辑非常复杂，需要拆分成多个独立步骤以增强可读性时，或者当数据库优化器能很好地处理多个 CTE 时。对于此问题，解法 1 更优。

常见低分回答

使用 INNER JOIN:
sql
```
 1-- 错误示例
 2SELECT COUNT(r.packet_id), COUNT(o.order_id) ...
 3FROM tb_red_packet_record r INNER JOIN tb_order o ON r.packet_id = o.packet_id
```
问题: INNER JOIN 只会保留被核销的红包，导致 received_count 和 used_count 的值永远相等，核销率恒为 100%，完全错误。
在 LEFT JOIN 后错误地使用 COUNT(*):
sql
```
 1-- 错误示例
 2SELECT COUNT(*) AS received_count, COUNT(o.order_id) AS used_count ...
 3FROM tb_red_packet_record r LEFT JOIN tb_order o ON r.packet_id = o.packet_id
```
问题: COUNT(*) 会计算 LEFT JOIN 之后的所有行，包括那些右侧为 NULL 的行。这导致 received_count 的计算是正确的，但命名具有误导性，应该用 COUNT(r.packet_id) 来明确表示统计的是红包数。

使用效率低下的相关子查询:

sql

 1-- 效率极低，应避免
 2SELECT
 3    DATE(get_time) AS dt,
 4    COUNT(packet_id) AS received_count,
 5    (SELECT COUNT(*)
 6     FROM tb_order o
 7     WHERE o.packet_id IN (
 8        SELECT r_inner.packet_id
 9        FROM tb_red_packet_record r_inner
10        WHERE DATE(r_inner.get_time) = DATE(r_outer.get_time)
11     )
12    ) AS used_count
13FROM tb_red_packet_record r_outer
14GROUP BY dt;

问题: 对于外层查询的每一天，都会执行一次复杂的子查询，这会导致查询性能随着时间跨度的增加而急剧下降，是典型的 $N+1$ 查询问题。

不处理除零错误:
sql
```
 1-- 潜在风险
 2SELECT COUNT(o.order_id) / COUNT(r.packet_id) ...
```
问题: 如果在某个筛选条件下（例如 WHERE source = '一个新活动'），某天没有领取任何红包，COUNT(r.packet_id) 会是 0，导致数据库抛出“division by zero”错误。应使用 NULLIF(divisor, 0) 或 CASE WHEN divisor > 0 THEN ... END 来避免。

工程实践与优化

索引是生命线:
- tb_red_packet_record 表必须在 (packet_id) 上有主键索引，在 (get_time) 上有普通索引。如果查询经常按 source 过滤，可以考虑建立 (source, get_time) 的复合索引。
- tb_order 表必须在 (packet_id) 上有索引，以极大地加速 JOIN 操作。
执行计划分析 (EXPLAIN):
- 对解法 1 执行 EXPLAIN，理想情况下，tb_red_packet_record 的 type 应为 range (如果 WHERE 有时间范围) 或 index (如果利用索引完成 GROUP BY)，tb_order 的 type 应为 ref，key 指向 packet_id 的索引。
- Extra 列不应出现 Using temporary; Using filesort，这表示 GROUP BY 无法利用索引，需要创建临时表并进行文件排序，是性能瓶颈。
大数据量下的考量 (亿级/十亿级):
- OLAP vs OLTP: 这种全量聚合分析不应该在生产环境的 OLTP 数据库（如 MySQL 主库）上实时运行。它会消耗大量 IO 和 CPU，可能影响线上交易。应在只读从库或数据仓库 (如 Hive, Spark, ClickHouse, BigQuery) 中执行。
- 预聚合/物化视图: 这是最高级的优化。创建一个每日任务 (ETL Job)，在凌晨业务低峰期计算好每天的核销率等核心指标，并存入一张结果表 daily_red_packet_summary。业务方查询时，直接从这张小得多的结果表中读取，实现毫秒级响应。
时间窗口问题: 注意业务口径，例如“7日核销率”（领取后7天内使用都算核销），JOIN 条件需要相应调整：ON r.packet_id = o.packet_id AND o.order_time <= DATE_ADD(r.get_time, INTERVAL 7 DAY)。这会使 JOIN 变得更复杂。

常见追问与延伸题目

追问：如果数据量增长 100 倍，达到百亿级别，你刚才的 SQL 还能跑吗？怎么优化？
- 回答要点 1 (指出问题)：不能。在百亿级别的数据上直接执行 JOIN 和 GROUP BY 会导致查询超时或拖垮数据库。MySQL 这类 OLTP 数据库不擅长处理如此大规模的分析。
- 回答要点 2 (解决方案)：必须采用大数据技术栈。首先，数据通过 ETL 工具（如 DataX, Flink CDC）同步到数据仓库（如 Hive, MaxCompute）。然后，使用分布式计算引擎（如 Spark, Flink）来执行。SQL 逻辑本身（解法 1）依然有效，但在 Spark SQL 或 Hive QL 中执行。最终，为了支持高并发查询，我们会将计算结果预聚合到一张天/小时粒度的指标表中，存储在 ClickHouse, Doris 或 HBase 中，供上层应用查询。
追问：如果要分析“新用户”和“老用户”的红包核销率差异，该如何修改查询？
- 回答要点 1 (数据依赖)：首先需要定义“新用户”。这通常需要一张用户表 tb_user，其中有 register_time 字段。或者，我们可以根据用户的首次下单时间来动态判断。
- 回答要点 2 (SQL 实现)：假设我们能关联到用户的注册时间。查询需要先 JOIN 用户表，然后使用 CASE WHEN 来打上“新/老用户”标签，最后在 GROUP BY 中增加这个标签维度。
  sql
```
 1SELECT
 2    DATE(r.get_time) AS dt,
 3    CASE WHEN r.get_time < DATE_ADD(u.register_time, INTERVAL 7 DAY) THEN '新用户' ELSE '老用户' END AS user_segment,
 4    COUNT(r.packet_id) AS received_count,
 5    COUNT(o.order_id) AS used_count
 6    -- ...
 7FROM
 8    tb_red_packet_record r
 9JOIN
10    tb_user u ON r.user_id = u.user_id
11LEFT JOIN
12    tb_order o ON r.packet_id = o.packet_id
13GROUP BY
14    dt, user_segment;
```
追问：刚才的查询是按“领取日”统计的，如果业务想按“核销日”统计核销率，SQL 怎么写？
- 回答要点 1 (思路转变)：统计口径变了，分析的主体从“领了多少”变成了“用了多少”。我们应该从订单表出发，JOIN 红包领取表来获取领取信息。
- 回答要点 2 (SQL 实现)：这个需求其实有点模糊，“核销率”的分母（领取量）如何界定？一种合理的解释是“核销当日，这些被核销的红包是在哪个渠道领的”，或者“核销当日的核销金额占总领取金额的比例”。一个更明确的指标是“每日核销的红包，平均生命周期是多久”。例如计算平均核销时长：
  sql
```
 1SELECT
 2    DATE(o.order_time) AS usage_dt,
 3    COUNT(o.order_id) AS used_count_on_day,
 4    AVG(TIMESTAMPDIFF(HOUR, r.get_time, o.order_time)) AS avg_usage_hours
 5FROM
 6    tb_order o
 7JOIN -- 必须是 INNER JOIN，因为我们只关心已核销的
 8    tb_red_packet_record r ON o.packet_id = r.packet_id
 9WHERE
10    o.packet_id IS NOT NULL
11GROUP BY
12    usage_dt
13ORDER BY
14    usage_dt;
```
- 关键点：向面试官澄清模糊的业务口径，并给出一种或多种合理的解读和对应的 SQL 实现，能体现出色的业务理解和沟通能力。