漏斗转化分析的 SQL？(funnel 步骤间的留存)

考察要点

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

1. 业务逻辑的 SQL 建模能力：如何将“漏斗转化”这一多步骤、有顺序的业务场景，转化为精确的 SQL 查询逻辑。
2. 复杂查询的构建能力：熟练运用子查询（Subquery）、公用表表达式（CTE）、条件聚合（Conditional Aggregation）等技术来分步解决问题。
3. SQL 性能优化意识：是否能考虑到大数据量下的查询效率，并给出索引设计、查询改写等优化方案。

核心概念

漏斗分析是衡量用户在关键业务流程中每一步转化与流失情况的核心数据分析模型。

1. 漏斗模型 (Funnel Model) 它描述了用户从进入产品到完成最终目标（如购买、注册）所经历的、逐步递减的连续步骤。

   mermaid

   ⧉⛶

    1graph TD
    2    A[步骤1: 启动App<br/>10000用户] --> B[步骤2: 浏览商品<br/>5000用户];
    3    B --> C[步骤3: 加入购物车<br/>1500用户];
    4    C --> D[步骤4: 成功下单<br/>800用户];

   核心在于 顺序性 和 流失性。一个严格的漏斗要求用户必须按顺序完成步骤，例如，必须先“浏览商品”才能“加入购物车”。

2. 事件表 (Event Table) 用户行为通常记录在一张事件流水表中。

   • DDL 示例:
     sql

     ⧉⛶

      1CREATE TABLE user_events (
      2    user_id BIGINT,
      3    event_name VARCHAR(50),
      4    event_time DATETIME,
      5    -- 为了高性能查询，索引至关重要
      6    INDEX idx_user_event_time (user_id, event_name, event_time)
      7);

   • 数据示例:

     user_id	event_name	event_time
     101	app_launch	2023-10-26 10:00:00
     102	app_launch	2023-10-26 10:01:00
     101	view_product	2023-10-26 10:02:00
     103	app_launch	2023-10-26 10:03:00
     102	view_product	2023-10-26 10:04:00
     101	add_to_cart	2023-10-26 10:05:00
     102	create_order	2023-10-26 10:06:00
     101	create_order	2023-10-26 10:07:00

3. 条件聚合 (Conditional Aggregation) 在一次 GROUP BY 中使用 CASE WHEN 语句对不同条件的数据进行聚合，是实现数据透视（Pivot）和高效计算的关键。

   sql

   ⧉⛶

    1-- 计算每个用户完成各个步骤的首次时间
    2SELECT
    3    user_id,
    4    MIN(CASE WHEN event_name = 'app_launch' THEN event_time END) AS launch_time,
    5    MIN(CASE WHEN event_name = 'view_product' THEN event_time END) AS view_time
    6FROM user_events
    7GROUP BY user_id;

   这个查询会将行式数据转换为列式，为后续判断步骤顺序打下基础。

多种解法

假设我们的漏斗步骤为：app_launch -> view_product -> add_to_cart -> create_order。我们需要计算一个 严格顺序漏斗，即用户必须按顺序完成步骤才被计数。

解法 1：GROUP BY + 条件聚合（推荐，性能和扩展性好）

该方法通过两层聚合实现：第一层按用户聚合，将用户的行为序列“拍平”到一行；第二层对所有用户进行计数，计算漏斗。

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

  ⧉⛶

   1WITH user_step_times AS (
   2    -- 步骤1: 计算每个用户完成每个事件的首次时间
   3    SELECT
   4        user_id,
   5        MIN(CASE WHEN event_name = 'app_launch' THEN event_time END) AS t1_launch,
   6        MIN(CASE WHEN event_name = 'view_product' THEN event_time END) AS t2_view,
   7        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS t3_cart,
   8        MIN(CASE WHEN event_name = 'create_order' THEN event_time END) AS t4_order
   9    FROM user_events
  10    WHERE event_name IN ('app_launch', 'view_product', 'add_to_cart', 'create_order')
  11    GROUP BY user_id
  12),
  13funnel_counts AS (
  14    -- 步骤2: 基于步骤时间顺序，计算每个漏斗层级的用户数
  15    SELECT
  16        SUM(CASE WHEN t1_launch IS NOT NULL THEN 1 ELSE 0 END) AS step1_users,
  17        SUM(CASE WHEN t1_launch IS NOT NULL AND t2_view IS NOT NULL AND t2_view > t1_launch THEN 1 ELSE 0 END) AS step2_users,
  18        SUM(CASE WHEN t1_launch IS NOT NULL AND t2_view IS NOT NULL AND t3_cart IS NOT NULL AND t2_view > t1_launch AND t3_cart > t2_view THEN 1 ELSE 0 END) AS step3_users,
  19        SUM(CASE WHEN t1_launch IS NOT NULL AND t2_view IS NOT NULL AND t3_cart IS NOT NULL AND t4_order IS NOT NULL AND t2_view > t1_launch AND t3_cart > t2_view AND t4_order > t3_cart THEN 1 ELSE 0 END) AS step4_users
  20    FROM user_step_times
  21),
  22funnel_report AS (
  23    -- 步骤3: 将列式结果转换为行式，并计算转化率 (MySQL 8.0+ / PostgreSQL)
  24    SELECT 'app_launch' AS step_name, step1_users AS user_count FROM funnel_counts
  25    UNION ALL
  26    SELECT 'view_product' AS step_name, step2_users AS user_count FROM funnel_counts
  27    UNION ALL
  28    SELECT 'add_to_cart' AS step_name, step3_users AS user_count FROM funnel_counts
  29    UNION ALL
  30    SELECT 'create_order' AS step_name, step4_users AS user_count FROM funnel_counts
  31)
  32-- 步骤4: 使用窗口函数计算上一步至今的转化率
  33SELECT
  34    step_name,
  35    user_count,
  36    -- LAG(user_count, 1, user_count) OVER() 表示取上一个值，如果不存在（即第一行），则取当前值
  37    ROUND(user_count * 100.0 / LAG(user_count, 1, user_count) OVER(ORDER BY (CASE step_name WHEN 'app_launch' THEN 1 WHEN 'view_product' THEN 2 WHEN 'add_to_cart' THEN 3 ELSE 4 END)), 2) AS conversion_rate
  38FROM funnel_report
  39ORDER BY (CASE step_name WHEN 'app_launch' THEN 1 WHEN 'view_product' THEN 2 WHEN 'add_to_cart' THEN 3 ELSE 4 END);

• 时间/空间复杂度：第一步 GROUP BY 是主要开销，若使用哈希聚合为 $O(N)$，若排序则为 $O(N \log N)$，其中 N 是 user_events 表中符合条件的行数。后续步骤处理的数据量很小。
• 索引依赖：复合索引 (user_id, event_name, event_time) 极大地提升性能。user_id 在前，可以利用索引完成 GROUP BY；event_name 在后，用于 WHERE 条件过滤。

解法 2：LEFT JOIN 链式连接（直观但性能较差）

该方法模拟漏斗的每一步，通过 LEFT JOIN 将用户的后续行为关联起来。每增加一个漏斗步骤，就增加一次 LEFT JOIN。

• 版本：✅ 所有 SQL 版本
• SQL 代码:
  sql

  ⧉⛶

   1WITH step1 AS (
   2    SELECT user_id, MIN(event_time) AS step_time FROM user_events WHERE event_name = 'app_launch' GROUP BY user_id
   3),
   4step2 AS (
   5    SELECT user_id, MIN(event_time) AS step_time FROM user_events WHERE event_name = 'view_product' GROUP BY user_id
   6),
   7step3 AS (
   8    SELECT user_id, MIN(event_time) AS step_time FROM user_events WHERE event_name = 'add_to_cart' GROUP BY user_id
   9),
  10step4 AS (
  11    SELECT user_id, MIN(event_time) AS step_time FROM user_events WHERE event_name = 'create_order' GROUP BY user_id
  12)
  13SELECT
  14    'app_launch' AS step_name,
  15    COUNT(DISTINCT s1.user_id) AS user_count
  16FROM step1 s1
  17UNION ALL
  18SELECT
  19    'view_product' AS step_name,
  20    COUNT(DISTINCT s2.user_id) AS user_count
  21FROM step1 s1
  22JOIN step2 s2 ON s1.user_id = s2.user_id AND s2.step_time > s1.step_time
  23UNION ALL
  24SELECT
  25    'add_to_cart' AS step_name,
  26    COUNT(DISTINCT s3.user_id) AS user_count
  27FROM step1 s1
  28JOIN step2 s2 ON s1.user_id = s2.user_id AND s2.step_time > s1.step_time
  29JOIN step3 s3 ON s2.user_id = s3.user_id AND s3.step_time > s2.step_time
  30UNION ALL
  31SELECT
  32    'create_order' AS step_name,
  33    COUNT(DISTINCT s4.user_id) AS user_count
  34FROM step1 s1
  35JOIN step2 s2 ON s1.user_id = s2.user_id AND s2.step_time > s1.step_time
  36JOIN step3 s3 ON s2.user_id = s3.user_id AND s3.step_time > s2.step_time
  37JOIN step4 s4 ON s3.user_id = s4.user_id AND s4.step_time > s3.step_time;
  38-- 注：此方法计算转化率需要再包一层CTE和窗口函数，代码更冗长。

• 时间/空间复杂度：$O(M \times N_{steps})$，其中 M 是每一步的用户数。随着漏斗层级加深，JOIN 的次数和复杂度急剧增加，性能会远差于解法 1。
• 索引依赖：每个子查询都需要 (event_name, user_id, event_time) 索引，JOIN 操作需要 user_id 上的索引。

何时使用：

• 解法 1：是生产环境中的首选。它只扫描一次主表，扩展性强（增加漏斗步骤只需在 CASE WHEN 中加列），性能更优。
• 解法 2：适用于漏斗层级很少（2-3步）且需要快速编写的场景。对于面试，可以作为一种思路提出，但要明确指出其性能局限性。

常见低分回答

1. 忽略用户唯一性：使用 COUNT(*) 或 COUNT(user_id) 而不是 COUNT(DISTINCT user_id)。一个用户可能多次触发同一事件，漏斗统计的是独立用户数。
2. 忽略漏斗的顺序性：只独立计算每个事件的用户数，没有 event_time 的先后判断。
   sql

   ⧉⛶

    1-- 错误示例：这只是计算了每个事件的独立用户数，不是漏斗
    2SELECT event_name, COUNT(DISTINCT user_id)
    3FROM user_events
    4GROUP BY event_name;

3. 使用 LEFT JOIN 但未正确处理 NULL：在 LEFT JOIN 的链条中，如果某一步用户未完成，后续 JOIN 的 user_id 会是 NULL。计数时需要 COUNT(DISTINCT s_last.user_id)，而不是 COUNT(DISTINCT s1.user_id)。
4. 多次扫描全表：使用多个 UNION ALL 连接的独立查询，每个查询都扫描一遍全表，效率极低。
   sql

   ⧉⛶

    1-- 错误示例：效率低下
    2SELECT 'app_launch', COUNT(DISTINCT user_id) FROM user_events WHERE event_name = 'app_launch'
    3UNION ALL
    4SELECT 'view_product', COUNT(DISTINCT user_id) FROM user_events WHERE event_name = 'view_product'
    5...

5. 在 WHERE 子句中使用函数：例如 WHERE DATE(event_time) = '2023-10-26'，这会导致 event_time 列上的索引失效。应改为 WHERE event_time >= '2023-10-26 00:00:00' AND event_time < '2023-10-27 00:00:00'。

工程实践与优化

• 索引是生命线：对于 user_events 这种量级的表，最优索引是 (user_id, event_name, event_time)。user_id 在前，因为核心是按用户聚合；event_name 在后，用于快速过滤事件类型；event_time 最后，用于排序和比较。
• 数据分区：当事件表达到数十亿甚至千亿级别时，必须按时间（如 event_time）进行分区（Partitioning）。查询时带上时间范围，可以实现分区裁剪（Partition Pruning），只扫描相关的分区，性能提升几个数量级。
• 预聚合/物化视图：对于高频访问的漏斗报表，在原始事件表上实时计算是不可接受的。工程上会采用 T+1 的方式，每天凌晨通过离线任务（如 Spark/Hive SQL）计算好前一天的漏斗数据，存入结果表（Summary Table）。前端直接查询结果表，实现秒级响应。
• 执行计划分析：使用 EXPLAIN 分析解法 1 的查询计划。
  • 第一步 CTE (user_step_times)：理想的 Extra 字段应包含 Using index for group-by，表示 MySQL 可以直接利用索引的有序性来完成 GROUP BY，避免了额外的排序或临时表，这是最高效的 GROUP BY 方式。type 应该是 range 或 ref。
  • 第二步 CTE (funnel_counts)：这一步是在内存中对一个很小的结果集（只有一行）进行计算，开销极小。
• 近似计算：在某些对精度要求不高的场景（如内部看板），COUNT(DISTINCT ...) 的精确计算非常耗时。可以考虑使用近似去重算法，如 HyperLogLog。一些数据库（如 ClickHouse, Presto）内置了 approx_distinct() 函数，性能比精确去重高出很多。

常见追问与延伸题目

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

   • 回答要点 1 (瓶颈分析)：直接在百亿级原始表上运行此 SQL 会非常慢，甚至无法在有效时间内跑出结果。主要瓶颈在于第一步 GROUP BY user_id，它需要扫描大量数据并进行聚合。COUNT(DISTINCT) 操作本身在分布式系统（如 Hive/Spark）中也会触发耗时的 Shuffle。
   • 回答要点 2 (架构级优化)：必须放弃实时查询，转向预计算架构。
     • 数据分层：引入数据仓库概念，将原始事件表（ODS 层）通过每日的 ETL 任务，加工成用户行为宽表（DWD 层）。这个宽表可以预先处理好每个用户每天的首次行为时间戳。
     • 聚合层：基于用户行为宽表，再次计算生成漏斗聚合表（DWS/ADS 层），存储最终的漏斗步骤和用户数。
     • 查询服务：线上报表直接查询这个最终的聚合表。整个链路从小时级或天级更新，保证了查询性能。

2. 追问：如何计算用户完成漏斗各步骤之间的平均/中位数耗时？

   • 回答要点：在解法 1 的 user_step_times CTE 基础上进行扩展。这个 CTE 已经有了每个用户完成各步骤的时间戳 t1_launch, t2_view 等。
   • SQL 改造：
     sql

     ⧉⛶

      1-- 在 user_step_times CTE 之后
      2SELECT
      3    'step1_to_step2' AS duration_name,
      4    AVG(TIMESTAMPDIFF(SECOND, t1_launch, t2_view)) AS avg_duration_seconds
      5FROM user_step_times
      6WHERE t1_launch IS NOT NULL AND t2_view IS NOT NULL AND t2_view > t1_launch
      7UNION ALL
      8SELECT
      9    'step2_to_step3' AS duration_name,
     10    AVG(TIMESTAMPDIFF(SECOND, t2_view, t3_cart)) AS avg_duration_seconds
     11FROM user_step_times
     12WHERE t2_view IS NOT NULL AND t3_cart IS NOT NULL AND t3_cart > t2_view
     13-- ... 以此类推

   • 对于中位数，MySQL 8.0+ 支持 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...) 窗口函数，可以用来计算中位数耗时。

3. 追问：如果漏斗的定义是“7天内完成”，如何修改查询？

   • 回答要点：需要在判断步骤顺序的同时，增加时间差的限制。
   • SQL 改造：在解法 1 的 funnel_counts CTE 中增加时间判断逻辑。
     sql

     ⧉⛶

      1-- 在 funnel_counts CTE 中
      2SUM(CASE
      3    WHEN t1_launch IS NOT NULL
      4         AND t2_view IS NOT NULL
      5         AND t2_view > t1_launch
      6         -- 增加时间差判断
      7         AND TIMESTAMPDIFF(DAY, t1_launch, t2_view) <= 7
      8    THEN 1 ELSE 0 END) AS step2_users,
      9SUM(CASE
     10    WHEN ... -- 前序步骤满足
     11         AND t3_cart > t2_view
     12         -- 整体时间窗口从第一步开始算
     13         AND TIMESTAMPDIFF(DAY, t1_launch, t3_cart) <= 7
     14    THEN 1 ELSE 0 END) AS step3_users
     15-- ... 以此类推

   • 需要和面试官明确“7天内完成”的定义：是指任意相邻两步之间不超过7天，还是指从第一步到最后一步总耗时不超过7天。上面的例子演示了两种不同的判断方式，需要根据具体业务需求来定。