电商场景：求某天每个商品的下单转化率（曝光→点击→加购→下单）。

考察要点

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

1. 业务逻辑到 SQL 的转换能力：能否将“转化率漏斗”这个典型的业务场景，精确地转化为 SQL 查询逻辑。
2. 复杂查询的构建能力：考察对条件聚合（CASE WHEN）、公共表表达式（CTE）、JOIN 等核心语法的熟练运用。
3. SQL 性能优化意识：是否考虑到大数据量下的查询效率，例如索引设计、避免重复扫描等，这直接体现了候选人的工程经验。

核心概念

面试中，清晰地阐述核心概念是展现技术深度的关键。

1. 漏斗模型（Funnel Analysis）

漏斗模型是分析用户行为路径中各个阶段转化情况的核心工具。在电商场景中，典型的路径是：

 1  曝光 (impression)
 2      |
 3      v
 4    点击 (click)
 5      |
 6      v
 7  加购 (add_to_cart)
 8      |
 9      v
10    下单 (purchase)

我们的目标是计算从上一步到下一步的转化率，以及相对于初始曝光的总转化率。

2. 数据表结构设计（Schema Design）

要解决问题，首先要有一个合理的数据模型。一个通用的用户行为日志表（user_behavior_log）设计如下：

 1CREATE TABLE user_behavior_log (
 2    user_id BIGINT COMMENT '用户ID',
 3    item_id BIGINT COMMENT '商品ID',
 4    behavior_type VARCHAR(20) COMMENT '行为类型: impression, click, add_to_cart, purchase',
 5    event_time DATETIME COMMENT '事件发生时间',
 6    dt VARCHAR(10) COMMENT '日期分区, e.g., 2023-10-26'
 7) PARTITION BY LIST (dt); -- 在生产环境中，通常按天分区

关键点：

• behavior_type：用一个字段枚举所有行为类型，是可扩展且高效的设计。
• dt 分区：对于海量日志数据，按天分区是生命线。查询时带上 WHERE dt = '...' 可以极大减少扫描的数据量。
• 用户去重：计算转化率通常基于独立用户数（UV），而不是行为次数（PV）。因此，COUNT(DISTINCT user_id) 是核心。

3. 条件聚合（Conditional Aggregation）

这是解决此类问题的最高效、最优雅的方式。它允许在一次 GROUP BY 操作中，根据不同条件计算多个指标。

语法：AGG_FUNC(CASE WHEN condition THEN column END)

• 原理：CASE 语句在聚合函数内部执行。当条件不满足时，CASE 表达式返回 NULL。所有聚合函数（COUNT, SUM, AVG 等）在计算时都会忽略 NULL 值。
• 示例：COUNT(DISTINCT CASE WHEN behavior_type = 'click' THEN user_id END) 只会计算行为是 'click' 的独立用户数。

多种解法

假设我们的原始数据 user_behavior_log 如下：

期望输出：

解法 1：条件聚合（最高效，推荐）

这种方法仅需对表进行一次扫描，性能最好，代码也最简洁。

• 适用版本：所有主流数据库版本。
• 思路：利用 GROUP BY 对 dt 和 item_id 进行分组，然后在 SELECT 子句中使用条件聚合分别计算每个行为类型的独立用户数。

 1SELECT
 2    dt,
 3    item_id,
 4    -- 各环节独立用户数 (UV)
 5    COUNT(DISTINCT CASE WHEN behavior_type = 'impression' THEN user_id END) AS impression_uv,
 6    COUNT(DISTINCT CASE WHEN behavior_type = 'click' THEN user_id END) AS click_uv,
 7    COUNT(DISTINCT CASE WHEN behavior_type = 'add_to_cart' THEN user_id END) AS add_to_cart_uv,
 8    COUNT(DISTINCT CASE WHEN behavior_type = 'purchase' THEN user_id END) AS purchase_uv,
 9
10    -- 关键转化率计算 (注意分母为0的情况)
11    -- 点击率: 点击UV / 曝光UV
12    ROUND(
13        COUNT(DISTINCT CASE WHEN behavior_type = 'click' THEN user_id END) /
14        NULLIF(COUNT(DISTINCT CASE WHEN behavior_type = 'impression' THEN user_id END), 0),
15    2) AS click_through_rate,
16
17    -- 加购率: 加购UV / 点击UV
18    ROUND(
19        COUNT(DISTINCT CASE WHEN behavior_type = 'add_to_cart' THEN user_id END) /
20        NULLIF(COUNT(DISTINCT CASE WHEN behavior_type = 'click' THEN user_id END), 0),
21    2) AS add_to_cart_rate,
22
23    -- 下单率: 下单UV / 加购UV
24    ROUND(
25        COUNT(DISTINCT CASE WHEN behavior_type = 'purchase' THEN user_id END) /
26        NULLIF(COUNT(DISTINCT CASE WHEN behavior_type = 'add_to_cart' THEN user_id END), 0),
27    2) AS purchase_conversion_rate
28FROM
29    user_behavior_log
30WHERE
31    dt = '2023-10-26'
32GROUP BY
33    dt,
34    item_id;

• 时间复杂度：$O(N)$，其中 N 是 dt = '2023-10-26' 的数据行数。只需一次全表（或分区）扫描。
• 空间复杂度：$O(M)$，其中 M 是分组后的 (dt, item_id) 组合数，用于存储 GROUP BY 的中间结果。
• 索引依赖：在 (dt, item_id, behavior_type) 上建立复合索引，可以极大地提升性能，甚至可能实现覆盖索引。

解法 2：公共表表达式 (CTE) + LEFT JOIN

这种方法更具结构化，将每一步的计算分解到单独的 CTE 中，可读性好，便于调试复杂的业务逻辑。

• 适用版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server；❌ MySQL 5.7 不支持 CTE（但可用子查询模拟）。
• 思路：为每个行为类型创建一个 CTE，分别计算其 UV，然后通过 LEFT JOIN 将它们串联起来。必须使用 LEFT JOIN 以确保即使某个环节没有用户，前面的环节数据也不会丢失。

 1WITH
 2impression_counts AS (
 3    SELECT dt, item_id, COUNT(DISTINCT user_id) AS uv
 4    FROM user_behavior_log
 5    WHERE dt = '2023-10-26' AND behavior_type = 'impression'
 6    GROUP BY dt, item_id
 7),
 8click_counts AS (
 9    SELECT dt, item_id, COUNT(DISTINCT user_id) AS uv
10    FROM user_behavior_log
11    WHERE dt = '2023-10-26' AND behavior_type = 'click'
12    GROUP BY dt, item_id
13),
14add_to_cart_counts AS (
15    SELECT dt, item_id, COUNT(DISTINCT user_id) AS uv
16    FROM user_behavior_log
17    WHERE dt = '2023-10-26' AND behavior_type = 'add_to_cart'
18    GROUP BY dt, item_id
19),
20purchase_counts AS (
21    SELECT dt, item_id, COUNT(DISTINCT user_id) AS uv
22    FROM user_behavior_log
23    WHERE dt = '2023-10-26' AND behavior_type = 'purchase'
24    GROUP BY dt, item_id
25)
26SELECT
27    imp.dt,
28    imp.item_id,
29    imp.uv AS impression_uv,
30    COALESCE(clk.uv, 0) AS click_uv,
31    COALESCE(cart.uv, 0) AS add_to_cart_uv,
32    COALESCE(pur.uv, 0) AS purchase_uv,
33    -- 转化率计算
34    ROUND(COALESCE(clk.uv, 0) / NULLIF(imp.uv, 0), 2) AS click_through_rate,
35    ROUND(COALESCE(cart.uv, 0) / NULLIF(clk.uv, 0), 2) AS add_to_cart_rate,
36    ROUND(COALESCE(pur.uv, 0) / NULLIF(cart.uv, 0), 2) AS purchase_conversion_rate
37FROM
38    impression_counts imp
39LEFT JOIN click_counts clk ON imp.dt = clk.dt AND imp.item_id = clk.item_id
40LEFT JOIN add_to_cart_counts cart ON imp.dt = cart.dt AND imp.item_id = cart.item_id
41LEFT JOIN purchase_counts pur ON imp.dt = pur.dt AND imp.item_id = pur.item_id;

• 时间复杂度：理论上是 $O(k \cdot N)$，k 是 CTE 的数量（这里是 4），因为可能涉及多次扫描。但现代数据库的查询优化器通常能识别出这种模式，并可能优化为一次扫描，性能接近解法 1。
• 空间复杂度：$O(k \cdot M)$，每个 CTE 都会产生中间结果集。
• 索引依赖：同样，在 (dt, behavior_type, item_id) 上的索引对每个 CTE 的性能都至关重要。

常见低分回答

• 误用 INNER JOIN：在解法 2 中使用 INNER JOIN 会导致只有走完所有流程的商品才被统计，数据严重失真。
• 不处理除零错误：直接使用 click_uv / impression_uv，当 impression_uv 为 0 时，查询会报错。正确做法是使用 NULLIF(impression_uv, 0)。
• 混淆 COUNT(*)/COUNT(col) 和 COUNT(DISTINCT col)：计算转化率必须基于独立用户数（UV），使用 COUNT(*) 会统计行为次数（PV），导致结果错误。例如，一个用户点击 5 次，COUNT(*) 计为 5，COUNT(DISTINCT user_id) 计为 1。
• 在 WHERE 子句中使用函数：例如 WHERE DATE_FORMAT(event_time, '%Y-%m-%d') = '2023-10-26'。这会导致 event_time 列上的索引失效，演变为全表扫描。正确的做法是使用 WHERE event_time >= '2023-10-26 00:00:00' AND event_time < '2023-10-27 00:00:00'，或者直接使用分区字段 dt。
• 效率低下的关联子查询：在 SELECT 中为每个指标写一个子查询，这会导致对表的多次重复扫描，性能极差。
  sql

  ⧉⛶

   1-- 反例
   2SELECT
   3    t1.item_id,
   4    (SELECT COUNT(DISTINCT t2.user_id) FROM user_behavior_log t2 WHERE t2.item_id = t1.item_id AND t2.behavior_type = 'impression') AS impression_uv,
   5    ...
   6FROM user_behavior_log t1
   7GROUP BY t1.item_id;

工程实践与优化

• 数据预处理和数仓分层：在生产环境中，这种查询不会直接跑在原始日志表（ODS 层）上。通常会通过离线/实时计算任务（如 Spark/Flink）每天生成一个预聚合的商品指标宽表（DWS/ADS 层），包含所有转化率指标。前端看板查询的是这个结果表，响应速度是毫秒级的。
• 利用执行计划（EXPLAIN）：面试时可以说：“我会用 EXPLAIN 来分析这条 SQL 的执行计划”。关注 type（理想是 ref, range, 最差是 ALL），key（是否用到了预期索引），rows（预估扫描行数），Extra（是否有 Using filesort, Using temporary, Using index）。
• 索引设计：对于这个查询，最佳索引是 (dt, item_id, behavior_type, user_id)。dt 和 item_id 用于快速过滤和分组，behavior_type 和 user_id 在索引中，MySQL 8.0+ 可以利用 "Index Skip Scan" 或直接成为覆盖索引，避免回表。
• 处理数据倾斜：如果某个爆款商品（item_id）的数据量远超其他商品，GROUP BY 时可能导致单个 worker/reducer 负载过重。在分布式系统（如 Hive/Spark）中，可以通过加盐（GROUP BY item_id, a_random_salt）再二次聚合的方式解决。在单机数据库中，主要是考验数据库的并行处理能力。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到千亿级别，这条 SQL 性能怎样？怎么优化？

   • 回答要点 1 (瓶颈分析)：在千亿级别，即使有分区和索引，直接在 OLTP 数据库（如 MySQL）上执行这种聚合查询也会导致 IO 和 CPU 耗尽，甚至锁住线上业务，是不可接受的。查询会非常慢或直接超时。
   • 回答要点 2 (架构级优化)：必须采用数据仓库/数据湖的方案。
     • 离线数仓：将原始日志导入到如 Hive/MaxCompute 的离线数仓中。使用 Spark 或 MapReduce 每天 T+1 计算出结果，写入到结果表（如 MySQL 或 ClickHouse）供查询。这是最成熟、成本最低的方案。
     • 实时数仓：使用 Flink + Kafka 消费实时日志流，在 Flink 内部进行开窗聚合，将结果实时写入到支持快速查询的 OLAP 数据库（如 ClickHouse, Doris）或 KV 存储（如 Redis）中。可以实现准实时的转化率监控。

2. 追问：刚才我们是按天统计的，如果想看用户在“30分钟内”的转化路径，该怎么实现？

   • 回答要点 1 (Session 划分)：这个问题引入了“会话（Session）”的概念。我们需要先对用户的行为进行会话切分。通常使用窗口函数 LAG 或 LEAD 来计算一个用户相邻两个行为的时间差，如果时间差超过一个阈值（如 30 分钟），则认为是一个新的会札。
   • 回答要点 2 (SQL 实现)：可以使用窗口函数为每个行为打上 session_id。首先用 LAG 计算与上一条记录的时间差，然后用一个累加的 SUM 窗口函数来生成会话 ID。之后，所有 GROUP BY 操作都应在 user_id 和 session_id 上进行。这考察了对高级窗口函数的掌握。

3. 追问：如果换成 PostgreSQL 或 ClickHouse，答案有什么不同？

   • 回答要点 1 (PostgreSQL)：PG 的 SQL 标准支持度非常好。解法 1 和解法 2 都可以无缝运行。PG 提供了更丰富的聚合函数和统计函数，例如 FILTER 子句可以作为条件聚合的替代写法，更符合 SQL 标准，可读性更强：COUNT(DISTINCT user_id) FILTER (WHERE behavior_type = 'click') AS click_uv。
   • 回答要点 2 (ClickHouse)：ClickHouse 是一个为 OLAP 设计的列式存储数据库，非常擅长此类聚合查询。
     • 性能：在同等硬件下，ClickHouse 的查询速度会比 MySQL 快几个数量级，因为列式存储只需读取用到的列（item_id, behavior_type, user_id），压缩比高，且利用向量化执行引擎。
     • 函数：ClickHouse 提供了强大的聚合函数，比如 -uniq 系列（如 uniqCombined 对应 COUNT(DISTINCT))，性能更高。它也有专门的漏斗分析函数 windowFunnel，可以用一个函数直接完成漏斗计算，极大简化 SQL。例如：SELECT windowFunnel(3600)(event_time, behavior_type = 'impression', behavior_type = 'click', behavior_type = 'purchase') FROM ...。提到这一点能充分展示你的技术广度。