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

在线教育：分析续费用户行为，求续费意愿最高的用户特征。

Online Education: Analyze the behavior of renewing users to identify the characteristics of those with the highest renewal

答案语言

考察要点

这道题面试官想看你是否具备将模糊的业务问题转化为可量化、可执行的数据分析需求的能力。它重点考察你的 SQL 综合运用能力，包括：复杂查询构建（特别是 CTE 或子查询）、多种聚合方式（GROUP BY, 窗口函数）、JOIN 的正确使用，以及对数据分析指标（如续费率）的精确定义和计算。

核心概念

要解决此问题，首先需要将业务术语精确化为数据和计算逻辑。

用户特征 (User Characteristics)：指存储在用户表中的维度信息，如城市、年龄段、性别、注册渠道等。

DDL 示例:

sql

 1CREATE TABLE users (
 2    user_id BIGINT PRIMARY KEY,
 3    city VARCHAR(50),
 4    age_group VARCHAR(20), -- e.g., '18-24', '25-34'
 5    registration_channel VARCHAR(30),
 6    registration_date DATE
 7);

续费行为 (Renewal Behavior)：需要一个明确的定义。最常见的定义是“用户产生了多次购买行为”。如果一个用户在订单表中有超过一笔订单，我们就视其为“续费用户”。

DDL 示例:

sql

 1CREATE TABLE orders (
 2    order_id BIGINT PRIMARY KEY,
 3    user_id BIGINT,
 4    course_id INT,
 5    amount DECIMAL(10, 2),
 6    create_time DATETIME,
 7    INDEX idx_user_createtime (user_id, create_time)
 8);

续费率 (Renewal Rate)：这是衡量续费意愿的核心指标。对于某个用户特征（如特定城市），其续费率计算公式为：
$\text{Renewal Rate} = \frac{\text{该特征下的续费用户数}}{\text{该特征下的总用户数}}$
- 关键点：分母是该特征下的所有用户，而不仅仅是有过订单的用户。这要求我们在计算时必须使用 users 表作为基准，并使用 LEFT JOIN 连接订单信息，以防遗漏从未下过单的用户。
CTE (Common Table Expression)：使用 WITH 关键字定义的临时结果集。在复杂查询中，CTE 能极大地提升代码的可读性和模块化程度。它先于主查询执行，其结果可以被主查询或其他 CTE 多次引用。

多种解法

假设我们分析 city 这一用户特征。

解法 1：使用 `CTE` 和 `GROUP BY`

这是最通用、可读性最强的解法，逻辑清晰，易于维护。

思路：
1. 创建一个 CTE (user_order_stats)，计算每个用户的总订单数。
2. 将 users 表与此 CTE 进行 LEFT JOIN。LEFT JOIN 确保了即使某些用户没有任何订单，他们也会被包含在分母的计算中。
3. 按用户特征（如 city）进行分组，分别计算总用户数和续费用户数（订单数 > 1）。
4. 计算比率并排序。
版本：✅ 所有现代 SQL 数据库（MySQL 8.0+, PostgreSQL, SQL Server, Oracle）

SQL 实现：

sql

 1WITH user_order_stats AS (
 2    -- 步骤1: 统计每个用户的订单数
 3    SELECT
 4        user_id,
 5        COUNT(order_id) AS order_count
 6    FROM orders
 7    GROUP BY user_id
 8)
 9-- 步骤2 & 3 & 4: 关联用户表，按特征分组计算续费率
10SELECT
11    u.city,
12    COUNT(u.user_id) AS total_users,
13    -- 使用 COUNT(CASE...) 或 SUM(CASE...) 计算续费用户数
14    COUNT(CASE WHEN uos.order_count > 1 THEN u.user_id END) AS renewing_users,
15    -- 乘以 1.0 避免整数除法，确保结果为小数
16    COUNT(CASE WHEN uos.order_count > 1 THEN u.user_id END) * 1.0 / COUNT(u.user_id) AS renewal_rate
17FROM users u
18LEFT JOIN user_order_stats uos ON u.user_id = uos.user_id
19GROUP BY u.city
20-- 过滤掉用户数过少的群体，避免小样本偏差
21HAVING COUNT(u.user_id) > 100
22ORDER BY renewal_rate DESC
23LIMIT 10;

复杂度：
- 时间复杂度：主要取决于 orders 表的 GROUP BY 和后续的 JOIN 及聚合。若 orders.user_id 和 users.user_id 有索引，复杂度约等于 $O(N_{orders} + M_{users})$ ，其中 N 和 M 分别是两个表的行数。Hash Join 和 Hash Aggregation 是线性时间复杂度的。
- 空间复杂度： $O(U_{distinct})$ ，其中 $U_{distinct}$ 是 orders 表中独立用户的数量，用于存储 CTE 的中间结果。
索引依赖：
- orders(user_id)：加速 GROUP BY。
- users(user_id)：加速 LEFT JOIN。
- users(city)：理论上可以加速 GROUP BY u.city，但通常优化器会选择在 Join 后的结果上进行 Hash Aggregation，所以此索引不是必须的。

解法 2：使用窗口函数

这种解法更为精巧，可以在一个 CTE 内完成用户是否续费的判断，适合熟悉窗口函数的开发者。

思路：
1. 使用窗口函数 COUNT(*) OVER (PARTITION BY user_id) 直接在 orders 表的每一行上标注出该用户的总订单数。
2. 对上一步的结果按 user_id 去重，得到每个用户的续费状态（订单数 > 1）。
3. 后续步骤同解法 1。
版本：✅ MySQL 8.0+ / PostgreSQL 等支持窗口函数的数据库。❌ MySQL 5.7 不支持。

SQL 实现：

sql

 1WITH user_renewal_flag AS (
 2    -- 步骤1 & 2: 使用窗口函数判断每个用户是否为续费用户
 3    SELECT DISTINCT
 4        user_id,
 5        (COUNT(order_id) OVER (PARTITION BY user_id)) > 1 AS is_renewer
 6    FROM orders
 7)
 8-- 步骤3: 关联用户表，计算续费率
 9SELECT
10    u.city,
11    COUNT(u.user_id) AS total_users,
12    -- AVG 是一个计算比率的技巧
13    AVG(CASE WHEN urf.is_renewer = TRUE THEN 1.0 ELSE 0.0 END) AS renewal_rate
14FROM users u
15LEFT JOIN user_renewal_flag urf ON u.user_id = urf.user_id
16GROUP BY u.city
17HAVING COUNT(u.user_id) > 100
18ORDER BY renewal_rate DESC
19LIMIT 10;

复杂度：
- 时间复杂度：窗口函数的计算通常需要对数据进行排序或分区，复杂度可能为 $O(N_{orders} \log N_{orders})$ 。后续 Join 和聚合与解法 1 类似。整体性能与解法 1 相当，具体取决于数据库的实现。
- 空间复杂度： $O(N_{orders})$ ，因为窗口函数需要保留分区内的数据。DISTINCT 操作也会消耗额外内存。
索引依赖：
- orders(user_id)：对 PARTITION BY 子句至关重要，可以避免全表扫描和排序。
- users(user_id)：加速 LEFT JOIN。

常见低分回答

分母错误，使用 INNER JOIN：这是最致命的错误。INNER JOIN 会直接排除掉所有没有订单的用户，导致分母（总用户数）变小，计算出的续费率完全错误。
sql
```
 1-- 错误示例：分母只包含了有订单的用户
 2FROM users u
 3INNER JOIN user_order_stats uos ON u.user_id = uos.user_id
```
续费用户数计算错误：在 LEFT JOIN 后，对于没有订单的用户，order_count 会是 NULL。使用 SUM(CASE WHEN uos.order_count > 1 THEN 1 ELSE 0 END) 是正确的，但如果逻辑写错，比如 COUNT(uos.user_id)，则会把所有有过订单的用户都统计进去，而不是仅统计续费用户。

整数除法导致结果为 0 或 1：在很多 SQL 方言中，两个整数相除会得到一个整数。

sql

 1-- 错误示例： 5 / 10 = 0
 2SELECT 5 / 10;
 3-- 正确做法：将其中一个数转为浮点数
 4SELECT 5 * 1.0 / 10; -- 结果为 0.5

不考虑小样本偏差：不对分组后的群体数量做筛选（如 HAVING COUNT(*) > 100），可能导致一个只有 2 个用户、其中 2 个都续费的群体（续费率 100%）排在最前面，这在业务上是没有意义的。
对问题定义不清就开始写代码：不先明确“续费”和“续费率”的定义，直接凭感觉写，导致逻辑混乱，无法向面试官解释清楚自己的思路。

工程实践与优化

执行计划分析：对于解法 1，理想的 EXPLAIN 结果应该显示：
1. orders 表：Using index for group-by 或 Using temporary; Using filesort 来完成 GROUP BY user_id。如果有 idx_user_createtime(user_id, create_time) 索引，效率最高。
2. JOIN：type 应该是 eq_ref 或 ref，使用 users 表的主键或 user_id 索引。Extra 字段不应出现 Using join buffer (Block Nested Loop)。
3. 最终聚合：使用 Hash Aggregation。
数据倾斜：如果某个城市的用户数远超其他城市（如“未知城市”），GROUP BY city 可能导致数据倾斜。处理方法包括过滤掉这些异常值，或在分布式计算框架（如 Spark）中使用带盐（salting）的 key 来打散数据。
物化视图/中间表：这种分析型查询在生产环境中可能非常耗时。如果需要频繁查询或在报表中展示，最佳实践是离线计算。每天通过 ETL 任务（如 Airflow + Spark SQL/Hive）将用户的续费状态（is_renewer）计算好，并存入一个用户标签表（user_tags）或一个预聚合的结果表。线上查询直接从这些轻量级的表中读取，实现秒级响应。
查询泛化：在真实业务中，产品经理可能想分析不同维度的续费率。可以将特征列名作为参数，使用动态 SQL 或在应用层构建查询，以复用逻辑。

常见追问与延伸题目

追问：如果“续费”的定义更复杂，比如“上一门课程结束后 30 天内购买新课”，你的 SQL 该怎么写？
- 回答要点：这需要引入课程结束时间的概念，并且要比较一个用户连续两次订单的时间间隔。
  1. 首先，需要一个 courses 表，包含 course_id 和 duration_days (课程时长)。
  2. 在 orders 表上使用窗口函数 LAG(create_time, 1) OVER (PARTITION BY user_id ORDER BY create_time) 来获取该用户上一次的购买时间。
  3. 同样，需要 JOIN courses 表获取上一门课的 duration_days。
  4. 计算时间差 DATEDIFF(o.create_time, LAG(o.create_time) OVER (...)) - c.duration_days。
  5. 筛选出时间差在 (0, 30] 区间内的订单，这些用户就是续费用户。这个逻辑比简单地 COUNT > 1 复杂得多，更能考察窗口函数的熟练度。
追问：如果 users 表和 orders 表都是亿级，你这条 SQL 会有什么问题？如何优化？
- 回答要点：
  1. 性能瓶颈：直接在亿级大表上执行 GROUP BY 和 JOIN 会非常慢，消耗大量 I/O 和 CPU，可能导致数据库主库抖动，影响线上业务。
  2. 优化方案：
    - 读写分离：在只读从库上执行此分析查询，避免影响主库。
    - 离线预计算（首选）：如上文“工程实践”所述，这是处理大数据量分析的最佳方案。使用大数据处理框架（Spark, Flink, Hive）在 T+1 的调度任务中完成计算，将结果存入 ClickHouse, Doris, 或 MySQL 汇总表中。
    - 索引优化：确保 orders(user_id, create_time) 和 users(user_id) 索引存在且最优。复合索引的顺序很重要。
    - 采样分析：如果不需要 100% 精确的结果，可以对用户进行采样（例如 ... FROM users WHERE user_id % 100 = 1），在 1% 的数据上进行计算，快速得到近似结果。
追问：如何一次性分析 city, age_group, registration_channel 等多个维度的续费率，并找出最优的组合？
- 回答要点：
  1. GROUPING SETS / CUBE / ROLLUP：标准 SQL 提供了这些高级聚合功能，可以一次性计算不同维度组合下的指标。例如，GROUP BY GROUPING SETS ((city), (age_group), (city, age_group)) 可以同时得到按城市、按年龄段、按城市和年龄段组合的续费率。MySQL 8.0+ 支持 ROLLUP。
  2. 暴力 GROUP BY：如果数据库不支持 GROUPING SETS，可以 GROUP BY city, age_group, registration_channel，这会计算出最细粒度的组合特征的续费率。
  3. 挑战：维度组合会爆炸式增长（“维度灾难”），很多组合的用户数会非常少，结果不可信。
  4. 延伸：这个问题已经超出了传统 SQL 的范畴，更接近数据挖掘和机器学习。面试官可能想借此考察你的知识广度。可以提及，实际工作中会使用决策树（如 CHAID, C4.5）等算法来自动发现最显著的特征组合，而不是手动 SQL 查询。