播面 统计在过去 30 天内，同时使用过 App、微信小程序和 Web 网页三种终端进行过购买行为的用户比例
SparkSQL 面试题：跨终端活跃用户比例统计
1. 题目背景
在多端运营（App、小程序、Web）的电商或服务场景中，识别“全渠道活跃用户”（即在多个终端都有过购买行为的用户）对于评估用户粘性和跨端营销策略至关重要。本题旨在考察候选人利用 SparkSQL 处理时间窗口过滤、多维去重、条件聚合以及比例计算的综合能力。
2. 示例数据
表1：用户购买流水表 (user_orders)
记录了用户在各个终端的购买历史。
| order_id (订单ID) | user_id (用户ID) | terminal_type (终端类型) | buy_time (购买时间) | amount (金额) |
|---|---|---|---|---|
| ord_101 | user_A | App | 2023-10-25 10:00:00 | 150.00 |
| ord_102 | user_A | MiniProgram | 2023-10-26 14:30:00 | 50.00 |
| ord_103 | user_A | Web | 2023-11-01 09:00:00 | 200.00 |
| ord_104 | user_B | App | 2023-11-02 18:00:00 | 300.00 |
| ord_105 | user_B | Web | 2023-11-05 12:00:00 | 100.00 |
| ord_106 | user_C | App | 2023-10-15 08:00:00 | 80.00 |
| ord_107 | user_C | MiniProgram | 2023-10-20 11:00:00 | 45.00 |
| ord_108 | user_D | App | 2023-11-04 15:00:00 | 120.00 |
| ord_109 | user_D | MiniProgram | 2023-11-05 16:00:00 | 30.00 |
| ord_110 | user_D | Web | 2023-11-06 10:00:00 | 90.00 |
假设当前系统时间（锚定计算时间）为：
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. 期望输出结果
| active_30d_user_cnt (30天购买总人数) | all_three_terminal_user_cnt (三端协同购买人数) | user_ratio (占比 %) |
|---|---|---|
| 4 | 2 | 50.00% |
4. SparkSQL 标准答案
sql
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 核心考点解析
- 时间窗口计算 (
date_sub/timestamp转换)：
在实际生产中，绝对不能使用GPS或System.currentTimeMillis()动态获取时间，这会导致任务重跑（Retroactive/Backfill）时数据不一致。面试中，使用固定的业务时间（如'2023-11-06'）结合date_sub(current_date, 30)是标准写法。 - 精确去重与条件过滤 (
COUNT(DISTINCT ...))：
题目要求“同时使用过三种终端”。通过WHERE terminal_type IN ('App', 'MiniProgram', 'Web')先过滤干扰终端，再通过COUNT(DISTINCT terminal_type) = 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聚合。
- 优化方案：在 Spark 中，可以先按
内存优化 (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。