SparkSQL 面试题：流失用户最后一次行为分布统计

1. 题目背景与业务需求

在用户运营中，“流失用户行为回溯”是一个非常经典的分析场景。我们需要识别出“已流失”（在本题中定义为：距离分析基准日过去 90 天内无任何登录行为）的用户，并回溯他们在流失前在平台发生的最后一次行为（如：浏览、加购、搜索、退单等），统计这些行为类型的分布人数及占比。

分析基准日假定为：2023-10-01。

2. 示例数据

表 1: 用户登录日志表 user_login

表 2: 用户行为明细表 user_behavior

3. 期望输出结果

以 2023-10-01 为基准线，90天前为 2023-07-03。最后一次登录在 2023-07-03 之前的视为流失用户（包含 101, 103, 104）。
这三位用户流失前的最后一次行为分别为：101 -> cart, 103 -> refund, 104 -> browse。

4. SparkSQL 解决方案

WITH churned_users AS (
    -- Step 1: 筛选出流失用户（最后一次登录距离 2023-10-01 超过 90 天）
    SELECT 
        user_id
    FROM user_login
    GROUP BY user_id
    HAVING datediff('2023-10-01', max(login_date)) > 90
),
user_last_action AS (
    -- Step 2: 过滤出流失用户的行为，并用窗口函数锁定最后一次行为
    SELECT 
        b.user_id,
        b.action_type,
        ROW_NUMBER() OVER (PARTITION BY b.user_id ORDER BY b.action_time DESC) as rn
    FROM user_behavior b
    INNER JOIN churned_users c ON b.user_id = c.user_id
),
final_action_counts AS (
    -- Step 3: 统计最后一次行为的频数
    SELECT 
        action_type,
        COUNT(1) as user_count
    FROM user_last_action
    WHERE rn = 1
    GROUP BY action_type
)
-- Step 4: 计算占比
SELECT 
    action_type,
    user_count,
    ROUND(user_count * 100.0 / SUM(user_count) OVER(), 2) as percentage
FROM final_action_counts
ORDER BY user_count DESC;

5. 面试官视角：SparkSQL 考点与深度剖析

在实际的面试中，这道题不仅仅考察 SQL 的编写能力，更是对大数据计算引擎（Spark）的优化、内存控制和架构理解的深度试金石。以下是面试官可能会追问的高频优化点及考点解析：

考点一：窗口函数与倾斜风险（ROW_NUMBER()）

• 面试官追问：在计算最后一次行为时，你使用了 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_time DESC)。如果某个大促期间，部分头部用户（如爬虫或商家）产生了数百万条行为数据，这会导致什么问题？如何优化？
• 解析：
  • 问题：PARTITION BY user_id 会导致相同 user_id 的所有行为数据通过 Shuffle 发送到同一个 Executor 的单线程中进行排序。如果单个 user_id 数据量过大，会导致 OOM（内存溢出） 或严重的 数据倾斜（Long Tail Task）。
  • 优化方案一（减少无用数据输入）：在 Join 之前，先利用 Spark 谓词下推，或者先对 user_behavior 进行时间窗口截断（例如只保留该用户最后活跃那几天的数据），减少参与 Shuffle 的数据量。
  • 优化方案二（分治法/两阶段聚合）：若只需最大值，可以先用 GROUP BY user_id, action_date 找出最大日期，再关联求出确切的 action_time，规避大范围的 ROW_NUMBER 排序。

考点二：Join 方式的选择与优化（Shuffle Join vs Broadcast Join）

• 面试官追问：在 user_behavior 与 churned_users 进行 INNER JOIN 时，Spark 底层会如何执行？如果 churned_users 结果集很小（例如只有几万行），如何优化这个 Join？
• 解析：
  • 默认情况下，Spark 会采用 SortMergeJoin (SMJ)，这会引起两张表的大规模 Shuffle 重分区，开销极大。
  • 如果流失用户表 churned_users 过滤后数据量非常小（默认低于 10MB，可通过 spark.sql.autoBroadcastJoinThreshold 配置），我们可以强制指定 Broadcast Hash Join (BHJ)：
    sql

    -- 在 SparkSQL 中使用 Hint 提示
    SELECT /*+ BROADCAST(c) */ b.user_id, b.action_type ...
    FROM user_behavior b
    INNER JOIN churned_users c ON b.user_id = c.user_id

  • 原理：将小表 c 广播到所有的 Executor 节点，大表 b 保持本地读取，直接在 Map 端完成 Join，消除了 Shuffle 阶段，性能能提升数倍。

考点三：全局占比计算中的单点瓶颈（SUM(user_count) OVER()）

• 面试官追问：最后一步计算占比时，你使用了没有 PARTITION BY 的窗口函数 SUM(user_count) OVER()，这在 Spark 中有什么隐患？
• 解析：
  • 当 OVER() 括号内为空时，Spark 会将所有数据 Shuffle 到单台机器的一个分区（Partition）中进行全局汇总计算。
  • 虽然在 Step 3 final_action_counts 聚合后，action_type 的种类（即行数）通常极少（通常只有几十种），单点计算完全可以承受。
  • 但如果前置统计维度极多（比如按商品、按城市等多维分析），行数过大时，必须改用先计算总数存为变量/临时表，再与原表进行 笛卡尔积（Cross Join） 或直接在 Driver 端获取后进行 Map 端 Join，避免单点瓶颈。