面试题：用户漏斗转化率分析

1. 题目背景

在电商分析中，漏斗分析（Funnel Analysis）是最核心的指标之一。本题要求统计用户在指定时间窗口（2小时）内，顺次完成 “搜索关键词 -> 点击商品 -> 加入购物车 -> 提交订单” 这四个步骤的完整漏斗转化情况，并计算出每一步的转化率。

2. 示例数据

输入数据表：user_behavior（用户行为日志表）

数据解析说明：

• U1 在 1.5 小时内完成了全部 4 个步骤（符合 2 小时窗口限制）。
• U2 虽完成了所有步骤，但 cart 时间（13:00）距离 search 时间（10:00）已超过 2 小时，因此其漏斗只计算到 click 步骤。
• U3 只完成了 search 和 click。
• U4 完成了 search、click 和 cart。
• U5 直接进行了 click，没有 search 作为起点，不计入漏斗。

期望输出结果

3. SparkSQL 实现代码

WITH t_search AS (
    -- 步骤 1：获取每个用户最早的搜索时间（漏斗起点）
    SELECT 
        user_id, 
        MIN(CAST(action_time AS TIMESTAMP)) AS search_time
    FROM user_behavior
    WHERE action_type = 'search'
    GROUP BY user_id
),
t_click AS (
    -- 步骤 2：在搜索后 2 小时内，发生的最早点击行为
    SELECT 
        b.user_id, 
        MIN(CAST(b.action_time AS TIMESTAMP)) AS click_time
    FROM user_behavior b
    JOIN t_search s ON b.user_id = s.user_id
    WHERE b.action_type = 'click'
      AND CAST(b.action_time AS TIMESTAMP) > s.search_time
      AND CAST(b.action_time AS TIMESTAMP) <= s.search_time + INTERVAL 2 HOURS
    GROUP BY b.user_id
),
t_cart AS (
    -- 步骤 3：在点击后、且在搜索后 2 小时内，发生的最早加购行为
    SELECT 
        b.user_id, 
        MIN(CAST(b.action_time AS TIMESTAMP)) AS cart_time
    FROM user_behavior b
    JOIN t_click c ON b.user_id = c.user_id
    JOIN t_search s ON b.user_id = s.user_id
    WHERE b.action_type = 'cart'
      AND CAST(b.action_time AS TIMESTAMP) > c.click_time
      AND CAST(b.action_time AS TIMESTAMP) <= s.search_time + INTERVAL 2 HOURS
    GROUP BY b.user_id
),
t_order AS (
    -- 步骤 4：在加购后、且在搜索后 2 小时内，发生的最早下单行为
    SELECT 
        b.user_id, 
        MIN(CAST(b.action_time AS TIMESTAMP)) AS order_time
    FROM user_behavior b
    JOIN t_cart ca ON b.user_id = ca.user_id
    JOIN t_search s ON b.user_id = s.user_id
    WHERE b.action_type = 'order'
      AND CAST(b.action_time AS TIMESTAMP) > ca.cart_time
      AND CAST(b.action_time AS TIMESTAMP) <= s.search_time + INTERVAL 2 HOURS
    GROUP BY b.user_id
)
-- 汇总计算各步骤人数及转化率
SELECT
    COUNT(s.user_id) AS search_count,
    COUNT(cl.user_id) AS click_count,
    COUNT(ca.user_id) AS cart_count,
    COUNT(o.user_id) AS order_count,
    -- 阶段转化率计算
    CONCAT(ROUND(COUNT(cl.user_id) / COUNT(s.user_id) * 100, 2), '%') AS search_to_click_rate,
    CONCAT(ROUND(IF(COUNT(cl.user_id) = 0, 0, COUNT(ca.user_id) / COUNT(cl.user_id) * 100), 2), '%') AS click_to_cart_rate,
    CONCAT(ROUND(IF(COUNT(ca.user_id) = 0, 0, COUNT(o.user_id) / COUNT(ca.user_id) * 100), 2), '%') AS cart_to_order_rate,
    -- 总体转化率
    CONCAT(ROUND(COUNT(o.user_id) / COUNT(s.user_id) * 100, 2), '%') AS overall_conversion_rate
FROM t_search s
LEFT JOIN t_click cl ON s.user_id = cl.user_id
LEFT JOIN t_cart ca ON s.user_id = ca.user_id
LEFT JOIN t_order o ON s.user_id = o.user_id;

4. 深度解析（面试加分项）

在面试中，仅仅写出上述 SQL 只能算及格。面试官通常会顺着这个题目深入考察你的 Spark 性能调优和底层原理理解。以下是需要掌握的深度分析：

解析一：方案设计思路（极简多路流式 Join）

该 SQL 采用 CTE（公共表表达式）结合多级左外连接（LEFT JOIN） 的方式来实现：

1. 定义基准点：首先锚定用户的起点行为 search，并取其最早时间 MIN(action_time)。
2. 时序与窗口双约束：
   • 时序约束：后续行为的时间必须严格大于前序行为的时间（例如 click_time > search_time）。
   • 窗口约束：所有后续行为必须限制在起点行为 + 2小时之内（action_time <= search_time + INTERVAL 2 HOURS）。
3. 逐级缩减：通过 LEFT JOIN 保证即使后续步骤流失，前序步骤的数据依然保留，最终通过 COUNT(user_id) 即可自动实现非空计数。

解析二：面试官高频追问与调优

追问 1：数据量极大时，这段 SQL 会产生什么性能问题？如何解决？

• 问题分析：由于是基于 user_id 进行多表关联（Join），在 Spark 底层会产生多次 Shuffle（Shuffle Hash Join 或 Sort Merge Join），如果数据量达到十亿级，网络传输开销极大，且容易产生数据倾斜（Data Skew）。
• 解决方案：
  1. 广播 Join（MapJoin）：如果漏斗后端的表（如 t_order、t_cart）过滤后数据量非常小（例如小于 10MB/100MB），可以使用广播连接，通过 /*+ BROADCAST(ca) */ 避免 Shuffle。
  2. 联合主键分布：如果可以，在数仓建设前期，将用户行为表按照 user_id 进行 Bucket（分桶），这样在 Join 时可以实现 Bucket Join，避免 Shuffle 的发生。

追问 2：除了多表 Join，还有更优的单表扫描写法吗？

• 答案：有的。在大规模生产环境下，更推荐使用 “用户路径聚合 + 数组/位图” 或 “条件聚合（Conditional Aggregation）”。

• 替代写法（极简版思路）：
  我们可以对每个用户的行为数据按时间排序，收集到一个列表（Array）中，然后在 UDF（自定义函数）或者 Spark 内部通过一条 GROUP BY user_id 语句，直接利用系统函数完成匹配。

  Spark SQL 单表扫描高阶写法示例：

  sql

  SELECT
      COUNT(IF(search_time IS NOT NULL, 1, NULL)) AS search_count,
      COUNT(IF(click_time IS NOT NULL, 1, NULL)) AS click_count,
      COUNT(IF(cart_time IS NOT NULL, 1, NULL)) AS cart_count,
      COUNT(IF(order_time IS NOT NULL, 1, NULL)) AS order_count
  FROM (
      SELECT 
          user_id,
          MIN(CASE WHEN action_type = 'search' THEN action_time END) AS search_time,
          MIN(CASE WHEN action_type = 'click' THEN action_time END) AS click_time,
          MIN(CASE WHEN action_type = 'cart' THEN action_time END) AS cart_time,
          MIN(CASE WHEN action_type = 'order' THEN action_time END) AS order_time
      FROM user_behavior
      GROUP BY user_id
  ) t
  -- 后续通过时间差在最外层做过滤判断

  注意：这种一阶段聚合的方法在处理单人多次重复漏斗（如多次搜索点击）时需要格外注意边界，但其最大优点是全表只扫描一次（Zero Shuffle/Single Shuffle），性能通常比多次 Join 提升数倍。