播面

面试真题：跨季度消费行为转变用户分析

题目背景：
在电商数据分析中，分析用户的生命周期和消费偏好转移是非常常见的需求。本题要求找出在 2025 年第一季度（Q1）偏好“母婴”品类（消费占比 > 50%），但在第二季度（Q2）偏好转移到“玩具”品类（消费占比 > 50%）的高价值流转用户。

1. 示例数据

订单明细表：user_orders

数据逻辑说明：

• 用户 101：Q1 总消费 1000，母婴 600（占比 60% > 50%）；Q2 总消费 1000，玩具 800（占比 80% > 50%）。（符合条件）
• 用户 102：Q1 总消费 1000，母婴 400（占比 40% <= 50%）；Q2 总消费 1000，玩具 900（占比 90%）。（不符合条件：Q1母婴未过半）
• 用户 103：Q1 总消费 1000，母婴 800（占比 80%）；Q2 总消费 1000，玩具 300（占比 30% <= 50%）。（不符合条件：Q2玩具未过半）
• 用户 104：Q1 总消费 900，母婴 900（占比 100%）；Q2 无消费。（不符合条件）

2. SparkSQL 核心写法

WITH user_quarterly_metrics AS (
    SELECT
        user_id,
        -- Q1 消费统计
        SUM(CASE WHEN order_date >= '2025-01-01' AND order_date <= '2025-03-31' THEN amount ELSE 0 END) AS q1_total_amount,
        SUM(CASE WHEN order_date >= '2025-01-01' AND order_date <= '2025-03-31' AND category = '母婴' THEN amount ELSE 0 END) AS q1_baby_amount,
        
        -- Q2 消费统计
        SUM(CASE WHEN order_date >= '2025-04-01' AND order_date <= '2025-06-30' THEN amount ELSE 0 END) AS q2_total_amount,
        SUM(CASE WHEN order_date >= '2025-04-01' AND order_date <= '2025-06-30' AND category = '玩具' THEN amount ELSE 0 END) AS q2_toy_amount
    FROM user_orders
    -- 过滤出 2025 上半年的数据，充分利用分区裁剪或索引
    WHERE order_date >= '2025-01-01' AND order_date <= '2025-06-30'
    GROUP BY user_id
)
SELECT
    user_id,
    q1_total_amount,
    q1_baby_amount,
    ROUND(q1_baby_amount / q1_total_amount, 4) AS q1_baby_ratio,
    q2_total_amount,
    q2_toy_amount,
    ROUND(q2_toy_amount / q2_total_amount, 4) AS q2_toy_ratio
FROM user_quarterly_metrics
WHERE q1_total_amount > 0 
  AND (q1_baby_amount / q1_total_amount) > 0.5
  AND q2_total_amount > 0 
  AND (q2_toy_amount / q2_total_amount) > 0.5;

3. 预期输出结果

4. 经典面试深度剖析与 SparkSQL 优化策略

面试官在考察这道题时，不仅看你能不能写出正确的 SQL，更看重你对 Spark 运行机制、Shuffle 优化和边界条件 的理解。

剖析一：为什么使用【条件聚合】而不是【JOIN 关联】？

• 初级开发做法：先过滤出 Q1 母婴占比过半的临时表 A，再过滤出 Q2 玩具占比过半的临时表 B，最后用 A JOIN B ON A.user_id = B.user_id。
• 高级开发做法（如上文答案）：使用一阶段条件聚合（Conditional Aggregation）。
• Spark 深度解释：
  • Join 方案：会导致两次 GROUP BY user_id 产生两次 Shuffle，最后的 JOIN 还要再产生一次 Shuffle（涉及三次 Shuffle Stage）。
  • 条件聚合方案：只需在 WHERE 子句中过滤 2025 上半年的数据，对全表进行一次扫描（Single Pass Scan）和一次 Shuffle（聚合），大大节约了网络 I/O 与磁盘溢写。

剖析二：如何避免 Double 类型计算中的【零除异常】（Division by Zero）？

• 在 Spark 中，如果某个用户在 Q1 消费了，但在 Q2 完全没有消费，其 q2_total_amount 为 0。
• 如果在 WHERE 子句中直接进行 q2_toy_amount / q2_total_amount 计算，会导致计算报错或者返回 NaN/Null。
• 解决方案：
  1. 在 WHERE 条件中前置过滤 q1_total_amount > 0 AND q2_total_amount > 0。
  2. 或者在计算比例时，使用 nullif(q2_total_amount, 0)：q2_toy_amount / nullif(q2_total_amount, 0)。当分母为 0 时，nullif 返回 NULL，相除结果也是 NULL，从而避免报错。

剖析三：Spark 物理执行层面的优化点（Predicate Pushdown & Partition Pruning）

• 分区裁剪（Partition Pruning）：如果 user_orders 表在 Hive/Spark 中是以 order_date 的年份/季度（如 dt）作为分区键，那么 WHERE order_date >= '2025-01-01' AND order_date <= '2025-06-30' 能够让 Spark 只扫描对应的分区数据，避免全表扫描。
• 本地 Map 端预聚合（Map-side Aggregation）：Spark SQL 默认开启 spark.sql.mapOutputs.minSize 相关优化。在使用 SUM(CASE...) 时，Spark 会在 Map 端进行 HashAggregate（预聚合），大大减少发送到 Reduce 端（Shuffle Read）的数据量。