播面 给定每日购买流水，计算每天对应的“过去 7 天内有购买行为的去重买家总数”（即 7-day Rolling Active Buyers）
Spark SQL 面试题：计算每日“过去 7 天滚动的去重买家总数”
1. 题目背景与要求
在电商和用户行为分析中，Rolling Active Users/Buyers（滚动活跃用户/买家） 是一个极度高频的业务指标。
题目要求：给定一份每日买家购买流水表，计算每天对应的“过去 7 天内（包含当天）有购买行为的去重买家总数”。
2. 示例数据
输入数据：buyer_purchases（买家购买流水表）
| purchase_date (购买日期) | buyer_id (买家ID) | purchase_amount (购买金额) |
|---|---|---|
| 2023-10-01 | B101 | 100.0 |
| 2023-10-01 | B102 | 50.0 |
| 2023-10-02 | B101 | 30.0 |
| 2023-10-02 | B103 | 120.0 |
| 2023-10-03 | B102 | 80.0 |
| 2023-10-04 | B104 | 200.0 |
| 2023-10-07 | B101 | 15.0 |
| 2023-10-08 | B105 | 90.0 |
| 2023-10-09 | B102 | 60.0 |
期望输出结果（以部分日期为例说明）：
注：过去 7 天范围定义为 [date - 6天, date]。
| purchase_date (日期) | rolling_7d_active_buyers (过去7天去重买家数) | 备注 (活跃的去重买家ID列表) |
|---|---|---|
| 2023-10-01 | 2 | B101, B102 |
| 2023-10-02 | 3 | B101, B102, B103 |
| 2023-10-03 | 3 | B101, B102, B103 |
| 2023-10-04 | 4 | B101, B102, B103, B104 |
| 2023-10-07 | 4 | B101, B102, B103, B104 (10-01在7天内) |
| 2023-10-08 | 3 | B101, B104, B105 (10-01已过期) |
| 2023-10-09 | 3 | B101, B102, B105 (10-02已过期) |
3. 标准 Spark SQL 答案
解法一：自关联（Self-Join）—— 最通用、最易直观理解的解法
sql
WITH daily_active AS (
-- Step 1: 先按天进行买家去重，减少关联的数据量
SELECT DISTINCT
purchase_date,
buyer_id
FROM buyer_purchases
)
SELECT
t1.purchase_date,
COUNT(DISTINCT t2.buyer_id) AS rolling_7d_active_buyers
FROM (
-- 获取所有有交易的日期作为主轴
SELECT DISTINCT purchase_date FROM buyer_purchases
) t1
JOIN daily_active t2
ON t2.purchase_date BETWEEN DATE_SUB(t1.purchase_date, 6) AND t1.purchase_date
GROUP BY
t1.purchase_date
ORDER BY
t1.purchase_date;解法二：窗口函数 + 收集集合（Collect_Set）—— 避免大表 Join 的高级解法
sql
WITH daily_buyer_sets AS (
-- Step 1: 聚合每天的买家并去重，存入 Array 释放行数限制
SELECT
CAST(purchase_date AS DATE) AS purchase_date,
collect_set(buyer_id) AS buyers_today
FROM buyer_purchases
GROUP BY purchase_date
),
rolling_sets AS (
-- Step 2: 使用窗口函数，收集过去 7 天（转为秒数滑动）的所有买家集合
SELECT
purchase_date,
-- 将过去 7 天的 Array 组合成一个大 Array
flatten(
collect_list(buyers_today) OVER (
ORDER BY CAST(purchase_date AS DATE)
RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW
)
) AS rolling_buyers_array
FROM daily_buyer_sets
)
-- Step 3: 对大 Array 进行去重计数
SELECT
purchase_date,
size(array_distinct(rolling_buyers_array)) AS rolling_7d_active_buyers
FROM rolling_sets
ORDER BY purchase_date;4. Spark SQL 深度剖析与面试加分项
在面试中，仅仅写出 SQL 只能算及格，能够分析不同解法在 Spark 引擎中的底层执行原理、数据倾斜以及性能瓶颈，才是斩获高薪的高光时刻。
剖析 1：为什么窗口函数 COUNT(DISTINCT buyer_id) OVER (...) 会报错？
许多候选人会尝试写出如下 SQL：
sql
-- 错误示范！Spark SQL 不支持这种写法
SELECT purchase_date,
COUNT(DISTINCT buyer_id) OVER(ORDER BY CAST(purchase_date AS DATE) RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)
FROM buyer_purchases;- 原因分析：Spark SQL（以及大多数主流 SQL 引擎如 Hive, Presto）不支持在带有滑动窗口（Moving Window/Range）的窗口函数中使用
DISTINCT。因为窗口内的数据随着滑动不断变化，无法高效地在内存中维护一个去重的哈希表。
剖析 2：解法一（Self-Join）的性能瓶颈与优化
- 执行原理：解法一通过
JOIN条件t2.purchase_date BETWEEN ...进行非等值关联。 - 致命缺点：这会导致 笛卡尔积（Cartesian Product） 或 Broadcast Nested Loop Join (BNLJ)。如果历史数据有 3 年（1000天），每一条数据都要和过去 7 天的数据关联，数据量会膨胀近 7 倍。如果用户量（Buyer）极度庞大，在 Shuffle 阶段会产生严重的 数据倾斜（Data Skew） 和 内存溢出（OOM）。
- 面试官追问：如何优化？
- 答：在 Join 前，必须先进行 Step 1 的
DISTINCT降维。先将“每天每用户多笔交易”压缩为“每天每用户一笔”，极大地降低参与 Join 的数据量。
- 答：在 Join 前，必须先进行 Step 1 的
剖析 3：解法二（Collect_Set + Array）的降维打击（推荐方案）
- 设计妙处：
- 避免了 Join：利用
GROUP BY purchase_date将每个日期的所有买家打包成一个Array，行数直接从“亿级”压缩到了“天数级”（如 365 行）。 - 高效的窗口滑动：在仅有几百行的表上做
collect_list窗口滑动，几乎零开销，不产生 Shuffle。 - 利用 Spark 内置函数：
flatten将多维数组拍平，array_distinct在内存中利用 Spark 优化的底层 C++ / Java 集合进行去重，最后用size直接获取大小。整个过程极快，是应对海量数据时的生产级写法。
- 避免了 Join：利用