播面

SparkSQL 面试题：新老用户每日销售额占比统计

1. 题目背景与要求

在电商和零售的数据分析中，区分新用户（当天注册且当天消费）与老用户（之前已注册且当天有消费）的消费贡献，对于评估获客质量和用户留存具有重要意义。

请编写一段 SparkSQL 代码，统计每日的销售总额中，新用户与老用户各自的金额占比。

2. 示例数据

表 1：用户注册表 user_reg

表 2：订单消费表 user_orders

3. 期望输出结果

数据解析说明：

• 10-01：U01, U02 均为当天注册并消费，全是新用户。
• 10-02：U03（新用户）消费 300，U01（老用户）消费 150，总额 450。新占 300/450=66.67%，老占 150/450=33.33%。
• 10-03：U04（新用户）消费 100，U02（老用户）消费 50，总额 150。新占 100/150=66.67%，老占 50/150=33.33%。

4. SparkSQL 标准答案

WITH user_order_detail AS (
    -- 1. 关联订单表与注册表，判断每次消费时用户是新用户还是老用户
    SELECT 
        o.order_date,
        o.amount,
        CASE WHEN o.order_date = r.reg_date THEN 'new' ELSE 'old' END AS user_type
    FROM user_orders o
    LEFT JOIN user_reg r 
    ON o.user_id = r.user_id
),
daily_summary AS (
    -- 2. 按天聚合，利用条件聚合分别计算新老用户的消费总额及当天总销售额
    SELECT 
        order_date AS dt,
        SUM(amount) AS daily_total_amount,
        SUM(CASE WHEN user_type = 'new' THEN amount ELSE 0 END) AS new_user_amount,
        SUM(CASE WHEN user_type = 'old' THEN amount ELSE 0 END) AS old_user_amount
    FROM user_order_detail
    GROUP BY order_date
)
-- 3. 计算占比并格式化输出
SELECT 
    dt,
    ROUND(daily_total_amount, 2) AS total_amount,
    CONCAT(ROUND((new_user_amount / daily_total_amount) * 100, 2), '%') AS new_user_ratio,
    CONCAT(ROUND((old_user_amount / daily_total_amount) * 100, 2), '%') AS old_user_ratio
FROM daily_summary
ORDER BY dt;

5. SparkSQL 深度解析与面试应对策略

面试官考察这道题，表面上是看你能不能写出 SQL 逻辑，实际上是在评估你的 Spark 性能调优意识、边界条件处理能力 以及 大数据计算思维。

核心解析一：JOIN 类型的选择与数据倾斜（Spark 优化重点）

• 大表 JOIN 小表（广播 Join）：在实际业务中，user_orders（订单表）通常是事实表（海量数据），而 user_reg（注册表）是维度表。
  • 面试加分表达：“如果 user_reg 表的数据量较小（例如在 Spark 端小于广播阈值，默认 10MB），我会显式使用广播连接 /*+ BROADCAST(r) */，将 user_reg 广播到每个 Executor，从而避免 Shuffle，彻底解决由于用户 ID 分布不均可能导致的数据倾斜问题。”
• 大表 JOIN 大表：如果两表都是百亿级大表，Spark 默认会采用 SortMergeJoin。此时需确保两表在 user_id 上已经过预分区（Bucketing），以减少 Shuffle Read 的数据传输量。

核心解析二：指标计算技巧（条件聚合 vs 子查询）

• 避免多次扫描数据：初学者容易写出“先用一个 SQL 算新用户，再写一个 SQL 算老用户，最后 JOIN 起来”的低效代码（导致数据被重复扫描）。
• 推荐做法：使用 CASE WHEN + SUM 的条件聚合（Conditional Aggregation）方式。在一次 GROUP BY 的 map-side 聚合中，同时完成新、老用户金额的分类累加，极大地减少了磁盘 I/O 和内存占用。

核心解析三：边界与容错处理

• 数据缺失（Null 值）处理：如果存在“有消费记录但在注册表里找不到”的异常用户（如脏数据），使用 LEFT JOIN 后 r.reg_date 会为 NULL。在上面的答案中，CASE WHEN o.order_date = r.reg_date 的判断会将这些异常用户安全地归类为 'old'（非当天注册），不会导致 SQL 报错或数据丢失。如果在严格业务场景下，应提前与面试官沟通是否需要将 r.user_id IS NULL 的数据进行过滤。