播面

SparkSQL 面试题：跨终端活跃用户比例统计

1. 题目背景

在多端运营（App、小程序、Web）的电商或服务场景中，识别“全渠道活跃用户”（即在多个终端都有过购买行为的用户）对于评估用户粘性和跨端营销策略至关重要。本题旨在考察候选人利用 SparkSQL 处理时间窗口过滤、多维去重、条件聚合以及比例计算的综合能力。

2. 示例数据

表1：用户购买流水表 (user_orders)

记录了用户在各个终端的购买历史。

假设当前系统时间（锚定计算时间）为：2023-11-06。

• 过去 30 天范围：2023-10-08 至 2023-11-06。
• 符合全终端购买的用户：
  • user_A：30天内有 App、MiniProgram、Web 购买记录（满足）。
  • user_B：只有 App、Web 购买记录（不满足）。
  • user_C：虽有App和小程序购买，但购买时间 10-15 和 10-20 虽然在30天内，但缺少 Web 端（不满足）。
  • user_D：30天内有 App、MiniProgram、Web 购买记录（满足）。
• 30天内总购买用户数：user_A, user_B, user_D（共3人）。user_C 也在30天内有购买（共4人）。

3. 期望输出结果

4. SparkSQL 标准答案

WITH active_users AS (
    -- Step 1: 筛选过去30天内的购买记录，并按用户分组统计其使用的终端种类
    SELECT 
        user_id,
        COUNT(DISTINCT terminal_type) AS terminal_count,
        -- 使用 collect_set 聚合终端，方便后续可能的多维度分析（可选）
        collect_set(terminal_type) AS terminal_set
    FROM user_orders
    WHERE buy_time >= CAST(date_sub('2023-11-06', 30) AS TIMESTAMP)
      AND buy_time <= CAST('2023-11-06 23:59:59' AS TIMESTAMP)
      AND terminal_type IN ('App', 'MiniProgram', 'Web')
    GROUP BY user_id
)
-- Step 2: 计算比例
SELECT 
    COUNT(1) AS active_30d_user_cnt,
    SUM(CASE WHEN terminal_count = 3 THEN 1 ELSE 0 END) AS all_three_terminal_user_cnt,
    CONCAT(
        ROUND(
            SUM(CASE WHEN terminal_count = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(1), 
            2
        ), 
        '%'
    ) AS user_ratio
FROM active_users;

5. SparkSQL 面试题深度分析

5.1 核心考点解析

1. 时间窗口计算 (date_sub / timestamp 转换)：
   在实际生产中，绝对不能使用 GPS 或 System.currentTimeMillis() 动态获取时间，这会导致任务重跑（Retroactive/Backfill）时数据不一致。面试中，使用固定的业务时间（如 '2023-11-06'）结合 date_sub(current_date, 30) 是标准写法。
2. 精确去重与条件过滤 (COUNT(DISTINCT ...) )：
   题目要求“同时使用过三种终端”。通过 WHERE terminal_type IN ('App', 'MiniProgram', 'Web') 先过滤干扰终端，再通过 COUNT(DISTINCT terminal_type) = 3 来精准判定。
3. 聚合后的二次计算 (KPI指标输出)：
   如何在一行数据中同时输出“分母（总人数）”和“分子（满足条件人数）”？
   通过 COUNT(1) 计算分母，利用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 计算分子，避免了两次 JOIN 带来的 Shuffle 开销。

5.2 Spark 引擎执行优化分析 (面试加分项)

在面对海量数据（TB/PB级）时，上述 SQL 在 Spark 引擎中会如何运行？如何优化？

• 数据倾斜 (Data Skew) 风险：
  COUNT(DISTINCT terminal_type) 会引入两阶段聚合（Partial/Merge Aggregation）。如果某些热门用户（如商家、代购账号）有极多订单，GROUP BY user_id 可能会导致数据倾斜。

  • 优化方案：在 Spark 中，可以先按 (user_id, terminal_type) 进行一步 GROUP BY 去重，减少数据量，再按 user_id 聚合。

• 内存优化 (Avoid Collect_Set)：
  标准答案中写了 collect_set(terminal_type)，这在面试中可以展现对数据的掌控力。但需注意，若单个 Key 的数据量极大，collect_set 会将数据缓存在 Executor 内存中，容易导致 OOM (Out Of Memory)。在生产环境下，如果仅做计数，应坚持使用 COUNT(DISTINCT ...)，Spark 优化器（Catalyst）会将其转化为高效率的 HashAggregate。

• 广播 Join (Broadcast Join) 的潜在应用：
  如果此题要求输出这些“全渠道活跃用户”的详细画像（需要关联用户主表），由于全渠道用户占比较小（通常 < 5%），可以利用 Broadcast Hash Join 将这部分高价值用户 ID 广播出去，避免大表与大表之间的大幅 Shuffle。