面试真题：2025年度首尾购买行为分析

题目描述：
在电商数据分析中，了解用户的首次和最后一次消费行为对于用户画像和流失预测至关重要。请针对给定的用户购买记录表 user_purchases，编写 SparkSQL 查询，找出在 2025年 有购买行为的每个用户，在该年度内购买的第一件商品和最后一件商品的名称及购买时间。

1. 基础数据准备

输入表：user_purchases（用户购买记录表）

期望输出结果

注：U002 的 Book A (2024年) 和 U004 的 Mouse (2026年) 不在2025统计范围内；U003 在2025年仅有一笔购买，首尾商品相同。

2. SparkSQL 解决方案

方案一：窗口函数 + 行列转换（最优解法，推荐）

利用 ROW_NUMBER() 在一次扫描中同时标记正序和逆序的排名，再通过聚合函数过滤并拼装结果。此方案避免了多表 Self-Join，性能最高。

WITH ranked_purchases AS (
    SELECT
        user_id,
        item_name,
        purchase_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_time ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_time DESC) AS rn_desc
    FROM user_purchases
    -- 过滤 2025 年的数据（不要对字段使用 year() 函数，保证分区裁剪和索引生效）
    WHERE purchase_time >= '2025-01-01 00:00:00' 
      AND purchase_time < '2026-01-01 00:00:00'
)
SELECT
    user_id,
    MAX(CASE WHEN rn_asc = 1 THEN item_name END) AS first_item,
    MAX(CASE WHEN rn_asc = 1 THEN purchase_time END) AS first_purchase_time,
    MAX(CASE WHEN rn_desc = 1 THEN item_name END) AS last_item,
    MAX(CASE WHEN rn_desc = 1 THEN purchase_time END) AS last_purchase_time
FROM ranked_purchases
WHERE rn_asc = 1 OR rn_desc = 1
GROUP BY user_id;

方案二：子查询 Join（传统解法，易理解但性能稍差）

分别取出 First 和 Last 的数据，再通过 user_id 进行关联。

WITH base_data AS (
    SELECT user_id, item_name, purchase_time
    FROM user_purchases
    WHERE purchase_time >= '2025-01-01 00:00:00' 
      AND purchase_time < '2026-01-01 00:00:00'
),
first_purchase AS (
    SELECT user_id, item_name, purchase_time
    FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_time ASC) as rn 
        FROM base_data
    ) WHERE rn = 1
),
last_purchase AS (
    SELECT user_id, item_name, purchase_time
    FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_time DESC) as rn 
        FROM base_data
    ) WHERE rn = 1
)
SELECT 
    f.user_id,
    f.item_name AS first_item,
    f.purchase_time AS first_purchase_time,
    l.item_name AS last_item,
    l.purchase_time AS last_purchase_time
FROM first_purchase f
JOIN last_purchase l ON f.user_id = l.user_id;

3. 面试官视角：SparkSQL 深度分析与调优

在面试中，仅仅写出 SQL 是不够的。面试官往往会追问底层的执行逻辑与优化细节。你可以从以下几个维度主动分析，以体现你的专业度：

① 避免 Self-Join（方案一 vs 方案二）

• 痛点：方案二使用了 JOIN 算子。在分布式计算（Spark）中，如果 user_id 没有预先分区，对两个大表进行 JOIN 会触发一次昂贵的 Shuffle (Shuffle Hash Join 或 Sort Merge Join)。
• 优化机制：方案一通过 ROW_NUMBER() 结合 MAX(CASE WHEN...) GROUP BY 的方式，将两次窗口计算和合并合并到了单次 Map 端和一次 Reduce（Shuffle）端。Spark 只需要按照 user_id 进行一次 Shuffle 即可输出结果，消除了 Join 操作，大大减少了磁盘与网络 I/O。

② 谓词下推与 SARGable（Sargable Query）

在过滤 2025 年数据时，使用 WHERE purchase_time >= '2025-01-01 00:00:00' AND purchase_time < '2026-01-01':

• 原理：如果写成 WHERE year(purchase_time) = 2025，会导致 Spark 无法利用底层数据存储格式（如 Parquet/Orc）的 Min/Max 索引统计信息 进行 Row Group 级别的过滤（即无法进行 File Skip），并且对每行数据都要执行函数调用，增加 CPU 损耗。
• 效果：直接对字段进行范围比对，使 Spark 优化器（Catalyst Optimizer）能够成功实施 谓词下推（Pushdown Predicates），在读取数据源时就过滤掉绝大部分不相关的数据。

③ 解决时间戳碰撞（Ties）问题

• 潜在风险：如果一个用户在同一秒（同一时间戳）购买了两个商品，ROW_NUMBER() 会随机决定谁是第一或最后。
• 高分回答：在实际业务中，如果遇到时间戳相同的情况，应当和面试官确认业务口径。
  • 若要求保留全部，应使用 DENSE_RANK()。
  • 若有主键 ID，可在 ORDER BY purchase_time ASC 后面加上辅助排序字段，例如 ORDER BY purchase_time ASC, order_id ASC，以确保结果的幂等性（即多次运行结果一致）。

④ Spark 执行计划（Physical Plan）微观分析

使用方案一，Spark 会生成如下的关键物理执行步骤：

1. FileScan：读取 user_purchases，应用 purchase_time 过滤条件。
2. Exchange (Shuffle)：根据 user_id 进行 Hash Partitioning（数据分发）。
3. Sort：在每个 Executor 分区内部，对 user_id 和 purchase_time 进行排序。
4. Window：执行 WindowFunction（计算 rn_asc 和 rn_desc）。
5. Filter：过滤出 rn_asc = 1 OR rn_desc = 1 的记录（数据量骤减）。
6. HashAggregate：执行 GROUP BY user_id，利用内存聚合计算出首尾商品。