找出过去 30 天内点赞 → 评论 → 关注的转化率。

考察要点

这道题的核心是考察候选人对 用户行为路径分析（Funnel Analysis） 的 SQL 实现能力。面试官想看你是否能深刻理解业务需求，并将其转化为精确的 SQL 逻辑，具体考察点包括：

1. 复杂逻辑表达：能否正确处理行为的 先后顺序 和 时间窗口，这是漏斗分析的关键。
2. SQL 核心技能：对 GROUP BY 结合条件聚合（CASE WHEN）、窗口函数（LAG/LEAD）、CTE（公用表表达式）的熟练运用。
3. 性能优化意识：在海量数据背景下，是否能写出高效、可扩展、能利用索引的 SQL。

核心概念

在解决问题前，需要清晰定义以下几个核心概念：

1. 漏斗模型 (Funnel Model)：这是一种分析用户在特定业务流程中逐步流失情况的模型。本题的漏斗是三步：点赞 → 评论 → 关注。

   text

   ⧉⛶

    1+----------------------+
    2| 1. 点赞 (Like)       |  <-- 100% 的基数
    3+----------------------+
    4         |
    5         v (转化率 a)
    6+----------------------+
    7| 2. 评论 (Comment)    |  <-- 完成步骤1的用户中有 a% 完成了步骤2
    8+----------------------+
    9         |
   10         v (转化率 b)
   11+----------------------+
   12| 3. 关注 (Follow)     |  <-- 完成步骤2的用户中有 b% 完成了步骤3
   13+----------------------+

2. 转化率计算公式：

   • 点赞→评论转化率 = (在30天内先点赞后评论的用户数) / (在30天内点赞过的用户数)
   • 评论→关注转化率 = (在30天内按顺序完成点赞、评论、关注的用户数) / (在30天内先点赞后评论的用户数)

3. 数据表结构 (DDL)：假设我们有一个用户行为日志表 user_actions。

   sql

   ⧉⛶

    1CREATE TABLE user_actions (
    2    user_id BIGINT,
    3    action_type VARCHAR(20), -- 'like', 'comment', 'follow'
    4    event_time DATETIME,
    5    INDEX idx_event_time (event_time),
    6    INDEX idx_user_action_time (user_id, action_type, event_time)
    7);

4. 数据示例：

   user_id	action_type	event_time
   101	like	2023-10-15 10:00:00
   101	comment	2023-10-15 11:00:00
   101	follow	2023-10-15 12:00:00
   102	like	2023-10-16 09:00:00
   102	follow	2023-10-16 09:30:00
   103	like	2023-10-18 14:00:00
   103	comment	2023-10-18 13:00:00

   • user_id=101: 完成了完整的 like -> comment -> follow 路径。
   • user_id=102: 完成了 like，但跳过了 comment 直接 follow，不计入 comment -> follow 转化。
   • user_id=103: comment 时间早于 like，不符合顺序，不计入 like -> comment 转化。

多种解法

解法 1：条件聚合 (Conditional Aggregation)

这是最通用、兼容性最好的解法，性能通常也很高。思路是按用户分组，计算出每个用户完成各行为的 最早时间，然后通过比较时间来判断是否满足漏斗顺序。

• 版本：✅ 所有主流数据库版本（MySQL 5.7+, PostgreSQL, Oracle, SQL Server）
• SQL 代码：
  sql

  ⧉⛶

   1WITH user_action_times AS (
   2    SELECT
   3        user_id,
   4        MIN(CASE WHEN action_type = 'like' THEN event_time END) AS first_like_time,
   5        MIN(CASE WHEN action_type = 'comment' THEN event_time END) AS first_comment_time,
   6        MIN(CASE WHEN action_type = 'follow' THEN event_time END) AS first_follow_time
   7    FROM
   8        user_actions
   9    WHERE
  10        event_time >= NOW() - INTERVAL 30 DAY
  11        AND action_type IN ('like', 'comment', 'follow')
  12    GROUP BY
  13        user_id
  14),
  15funnel_stages AS (
  16    SELECT
  17        -- 阶段1：只要点赞过
  18        COUNT(DISTINCT user_id) AS like_users,
  19        -- 阶段2：点赞过，且首次评论时间晚于首次点赞时间
  20        COUNT(DISTINCT CASE WHEN first_comment_time > first_like_time THEN user_id END) AS comment_users,
  21        -- 阶段3：满足阶段2，且首次关注时间晚于首次评论时间
  22        COUNT(DISTINCT CASE WHEN first_comment_time > first_like_time AND first_follow_time > first_comment_time THEN user_id END) AS follow_users
  23    FROM
  24        user_action_times
  25)
  26SELECT
  27    like_users,
  28    comment_users,
  29    follow_users,
  30    -- 点赞 -> 评论 转化率
  31    comment_users / like_users AS like_to_comment_rate,
  32    -- 评论 -> 关注 转化率
  33    follow_users / comment_users AS comment_to_follow_rate
  34FROM
  35    funnel_stages;

• 时间/空间复杂度：时间复杂度主要在 GROUP BY 阶段。如果使用 Hash Aggregate，则为 $O(N)$，其中 N 是 30 天内的记录数。空间复杂度为 $O(U)$，U 是 30 天内的活跃用户数。
• 索引依赖：WHERE event_time >= ... 可以有效利用 idx_event_time 索引，大大减少扫描的数据量。

解法 2：窗口函数 (Window Functions)

这种解法更适合处理复杂的、多步的、严格有序的路径分析。通过 LAG 或 LEAD 函数，可以在每一行数据上直接看到上一步或下一步的行为，逻辑更直观。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server
• SQL 代码：
  sql

  ⧉⛶

   1WITH user_journey AS (
   2    SELECT
   3        user_id,
   4        action_type,
   5        event_time,
   6        -- 获取同一个用户按时间排序的下一个行为
   7        LEAD(action_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS next_action
   8    FROM
   9        user_actions
  10    WHERE
  11        event_time >= NOW() - INTERVAL 30 DAY
  12        AND action_type IN ('like', 'comment', 'follow')
  13),
  14funnel_counts AS (
  15    SELECT
  16        -- 统计总的点赞用户数
  17        COUNT(DISTINCT user_id) AS like_users,
  18        -- 统计发生了 "like -> comment" 序列的用户数
  19        COUNT(DISTINCT CASE WHEN action_type = 'like' AND next_action = 'comment' THEN user_id END) AS like_to_comment_users,
  20        -- 统计发生了 "comment -> follow" 且之前有 "like" 的用户数
  21        -- (这里逻辑较复杂，用解法1的聚合思路更清晰，此解法更适合找固定序列)
  22        -- 为了简化，我们这里也用聚合逻辑，但可以基于窗口函数的结果
  23        -- 一个更严谨的窗口函数解法是构建完整的路径，但会更复杂
  24        -- 这里我们混合一下思路，展示窗口函数的用法
  25        COUNT(DISTINCT CASE WHEN action_type = 'like' AND next_action = 'comment' THEN user_id END) AS stage2_users,
  26        COUNT(DISTINCT CASE WHEN action_type = 'comment' AND next_action = 'follow' THEN user_id END) AS stage3_candidates
  27        -- 注意：这种简单 LEAD 无法保证 stage3_candidates 之前一定有过 like，所以解法1更鲁棒
  28)
  29-- 由于纯窗口函数解法在此场景下反而更复杂，我们推荐解法1。
  30-- 这里仅作思路展示，实际面试中请强调解法1的鲁棒性。
  31-- 一个更完整的窗口函数解法需要多次自连接或递归CTE，性能可能更差。

  面试官点评：虽然窗口函数很强大，但对于“只要A发生后B发生即可”（不要求紧邻）的场景，条件聚合通常更简洁高效。如果题目要求是“严格的相邻三步操作”，窗口函数则是最佳选择。

常见低分回答

1. 忽略行为顺序：简单地用 COUNT(DISTINCT ...) 统计做过三种操作的用户，但没有用时间戳判断先后。

   sql

   ⧉⛶

    1-- 错误示例：只统计了用户是否做过这些动作，没管顺序
    2SELECT
    3    COUNT(DISTINCT CASE WHEN action_type = 'like' THEN user_id END) AS like_users,
    4    COUNT(DISTINCT CASE WHEN action_type = 'comment' THEN user_id END) AS comment_users,
    5    COUNT(DISTINCT CASE WHEN action_type = 'follow' THEN user_id END) AS follow_users
    6FROM user_actions;

2. 错误的 JOIN：试图用 JOIN 连接代表不同行为的子查询，但这会导致结果膨胀且难以处理时间顺序。

   sql

   ⧉⛶

    1-- 错误示例：JOIN 无法正确处理一对多的情况和时间顺序
    2SELECT COUNT(DISTINCT l.user_id)
    3FROM (SELECT user_id, event_time FROM user_actions WHERE action_type = 'like') l
    4JOIN (SELECT user_id, event_time FROM user_actions WHERE action_type = 'comment') c
    5  ON l.user_id = c.user_id AND c.event_time > l.event_time; -- 逻辑不严谨，会产生笛卡尔积

3. 在 WHERE 子句中对列使用函数：这会导致索引失效，在海量数据下是灾难性的。

   sql

   ⧉⛶

    1-- 错误示例：函数包裹列，导致索引失效
    2WHERE DATE_FORMAT(event_time, '%Y-%m-%d') >= '2023-10-01'
    3-- 正确写法
    4WHERE event_time >= '2023-10-01 00:00:00'

4. 混淆事件数和用户数：题目要求计算用户转化率，应该始终对 user_id 进行 COUNT(DISTINCT ...)，而不是 COUNT(*)。

工程实践与优化

• 索引策略：对于这张 user_actions 表，WHERE 条件是 event_time，GROUP BY 是 user_id，CASE WHEN 里是 action_type。

  • INDEX(event_time)：最高效地过滤出30天内的数据。
  • INDEX(user_id, event_time) 或 INDEX(user_id, action_type, event_time)：对于窗口函数中的 PARTITION BY user_id ORDER BY event_time 是绝佳的覆盖索引，可以避免文件排序（filesort）。对于条件聚合，也能在一定程度上帮助 GROUP BY。

• 执行计划分析 (EXPLAIN)：

  • 关注 type 列：理想情况是 range（使用了 event_time 索引）。最差是 ALL（全表扫描）。
  • 关注 key 列：显示实际使用的索引。
  • 关注 Extra 列：
    • Using index: 表示使用了覆盖索引，无需回表，性能极好。
    • Using where: 表示在存储引擎层过滤后，服务层还需再次过滤。
    • Using temporary; Using filesort: 表示需要创建临时表并进行文件排序来满足 GROUP BY，这是性能瓶颈，应通过优化索引来避免。

• 数据倾斜与物化：

  • 在字节这种体量下，直接在 OLTP 库（如 MySQL）上跑这种查询是不可接受的。数据通常会同步到数据仓库（如 Hive, ClickHouse, StarRocks）中。
  • 对于高频访问的转化率指标，会通过 定时任务（如每日或每小时的 Spark/Flink 任务）预计算好结果，存入结果表（物化视图），前端查询直接读取结果，实现秒级响应。

• 采样分析：对于临时的、探索性的分析，可以对用户进行采样（例如 WHERE user_id % 100 = 0 取 1% 的用户），在保证统计显著性的前提下，将计算量降低 100 倍。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到万亿级别，你的 SQL 方案需要做什么调整？

   • 回答要点 1 (架构层面)：在线查询不可行。必须采用离线/实时计算架构。数据从业务库实时同步到消息队列（如 Kafka），再由实时计算平台（如 Flink）或批处理平台（如 Spark）消费。
   • 回答要点 2 (计算层面)：计算逻辑会从 SQL 迁移到 Spark/Flink 程序中。程序会维护每个用户的状态（State），例如 (user_id, has_liked, first_like_time, has_commented_after_like, ...)。当新的行为事件到来时，更新状态并计算转化。结果最终写入 OLAP 数据库（如 ClickHouse/Doris）或 KV 存储（如 Redis）供查询。
   • 回答要点 3 (存储层面)：数据仓库中的原始日志表会按天或小时进行分区（Partition），查询时指定分区可极大减少扫描范围。

2. 追问：如果要计算的漏斗是 “点赞 -> 1小时内评论 -> 24小时内关注”，SQL 该如何修改？

   • 回答要点：这要求在时间比较上增加更严格的约束。以解法1为例，只需修改 user_action_times CTE 之后的逻辑即可。
     sql

     ⧉⛶

      1-- 在 funnel_stages CTE 中修改判断条件
      2...
      3SELECT
      4    COUNT(DISTINCT user_id) AS like_users,
      5    COUNT(DISTINCT CASE
      6        WHEN first_comment_time > first_like_time
      7             AND first_comment_time <= first_like_time + INTERVAL 1 HOUR
      8        THEN user_id END) AS comment_users,
      9    COUNT(DISTINCT CASE
     10        WHEN first_comment_time > first_like_time
     11             AND first_comment_time <= first_like_time + INTERVAL 1 HOUR
     12             AND first_follow_time > first_comment_time
     13             AND first_follow_time <= first_comment_time + INTERVAL 24 HOUR
     14        THEN user_id END) AS follow_users
     15FROM user_action_times
     16...

   • 这展示了你对业务逻辑变化的快速响应和 SQL 的灵活运用能力。

3. 追问：如果换成 PostgreSQL，这个查询有什么可以优化的点吗？

   • 回答要点 1 (功能差异)：PostgreSQL 的功能集比 MySQL 更丰富。例如，它支持更强大的 FILTER 子句用于聚合，可以让语法更优雅。
     sql

     ⧉⛶

      1-- PostgreSQL 特有语法
      2SELECT
      3    COUNT(DISTINCT user_id) FILTER (WHERE action_type = 'like') AS like_users,
      4    COUNT(DISTINCT user_id) FILTER (WHERE action_type = 'comment' AND ...) AS comment_users
      5...

   • 回答要点 2 (执行计划)：PostgreSQL 的查询优化器和执行引擎与 InnoDB 不同。例如，它有更多 JOIN 策略（Nested Loop, Hash Join, Merge Join）且优化器选择更智能。在分析 EXPLAIN ANALYZE 的输出时，关注的重点会略有不同，比如 cost 估算、actual time 等。
   • 回答要点 3 (扩展性)：PostgreSQL 社区有 TimescaleDB 这样的时序数据库插件，专门针对 event_time 这类数据进行了极致优化，包括自动分区、列式压缩等。如果这是一个核心场景，使用 TimescaleDB 会比原生 PostgreSQL 或 MySQL 性能好几个数量级。