SparkSQL 面试题：快递寄件业务优惠券使用比例及用户复寄率分析

1. 背景介绍

在快递物流行业中，优惠券（如满减券、折扣券）是吸引用户寄件、提升平台活跃度的重要营销工具。评估优惠券的核销效果（使用比例）以及它对用户粘性的拉动作用（后续复寄率），是数据分析师和大数据开发工程师的常见核心任务。

2. 示例数据

本题提供三张基础业务表：用户信息表（user_info）、寄件订单表（express_order） 和 优惠券明细表（coupon_detail）。

表 1：用户信息表 user_info

数据样例：

表 2：寄件订单表 express_order

数据样例：

表 3：优惠券明细表 coupon_detail

数据样例：

3. 面试需求

编写 SparkSQL 实现以下两个指标的统计：

1. 指标一：优惠券订单比例。计算 2023年10月份，使用各类优惠券（满减券、折扣券）的订单量占总订单量的比例（需展示：总订单量、满减券订单量、满减券占比、折扣券订单量、折扣券占比）。
2. 指标二：使用优惠券用户的后续复寄率。定义“后续复寄率”为：在 2023年10月份期间，首次使用优惠券寄件的用户，在首次寄件之后，后续是否有过任意寄件行为（无论后续是否用券）。输出：使用优惠券的总人数、后续有复寄行为的人数、复寄率。

4. 面试题答案 (SparkSQL)

WITH coupon_order_base AS (
    -- 1. 关联订单与券表，清洗基础数据
    SELECT 
        o.order_id,
        o.user_id,
        o.create_time,
        o.coupon_id,
        c.coupon_type
    FROM express_order o
    LEFT JOIN coupon_detail c 
      ON o.coupon_id = c.coupon_id
    WHERE o.create_time >= '2023-10-01 00:00:00' 
      AND o.create_time < '2023-11-01 00:00:00'
),

-- ==========================================
-- 需求一：统计各类优惠券订单占比
-- ==========================================
coupon_ratio_result AS (
    SELECT
        COUNT(1) AS total_order_cnt,
        SUM(CASE WHEN coupon_type = '满减券' THEN 1 ELSE 0 END) AS money_off_order_cnt,
        ROUND(SUM(CASE WHEN coupon_type = '满减券' THEN 1 ELSE 0 END) * 100.0 / COUNT(1), 2) AS money_off_ratio_pct,
        SUM(CASE WHEN coupon_type = '折扣券' THEN 1 ELSE 0 END) AS discount_order_cnt,
        ROUND(SUM(CASE WHEN coupon_type = '折扣券' THEN 1 ELSE 0 END) * 100.0 / COUNT(1), 2) AS discount_ratio_pct
    FROM coupon_order_base
),

-- ==========================================
-- 需求二：统计使用优惠券用户的后续复寄率
-- ==========================================
user_coupon_timeline AS (
    -- 获取每个用户在10月份首次使用优惠券的时间
    SELECT 
        user_id,
        MIN(create_time) AS first_coupon_time
    FROM coupon_order_base
    WHERE coupon_type IS NOT NULL
    GROUP BY user_id
),

user_re_express AS (
    -- 判断用户在首次用券时间之后，是否还有其他寄件记录
    SELECT 
        f.user_id,
        -- 如果存在订单创建时间大于首次用券时间的记录，说明有复寄行为
        MAX(CASE WHEN o.create_time > f.first_coupon_time THEN 1 ELSE 0 END) AS is_re_express
    FROM user_coupon_timeline f
    LEFT JOIN express_order o 
      ON f.user_id = o.user_id
    GROUP BY f.user_id
),

repurchase_ratio_result AS (
    SELECT
        COUNT(1) AS coupon_user_cnt,
        SUM(is_re_express) AS re_express_user_cnt,
        ROUND(SUM(is_re_express) * 100.0 / COUNT(1), 2) AS re_express_ratio_pct
    FROM user_re_express
)

-- 输出最终汇总结果（面试时可以分步输出，也可以像这样合成一个结果集）
SELECT 
    r1.total_order_cnt,
    r1.money_off_order_cnt,
    r1.money_off_ratio_pct,
    r1.discount_order_cnt,
    r1.discount_ratio_pct,
    r2.coupon_user_cnt,
    r2.re_express_user_cnt,
    r2.re_express_ratio_pct
FROM coupon_ratio_result r1
CROSS JOIN repurchase_ratio_result r2;

5. SparkSQL 深度分析与面试应对

在面试中，仅仅写出 SQL 是不够的，面试官往往会针对你的 SQL 进行追问，以考察你的 Spark 底层优化、逻辑严密性。以下是针对此题的深度分析和避坑指南：

5.1 核心考点剖析

1. 条件过滤与外连接（Left Join）
   • 考点：express_order 关联 coupon_detail。未用券的订单 coupon_id 是 'None' 或 NULL。
   • 细节：必须使用 LEFT JOIN。如果是 INNER JOIN，未用券的订单会被过滤掉，导致第一个指标的“总订单数”计算偏小。
2. 条件计数（Conditional Aggregation）
   • 考点：在一行内统计多种不同券类型的占比。
   • 技巧：使用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 或 COUNT(IF(...))。注意乘 100.0 转换为浮点数以防止整除丢失精度。
3. “首次”与“后续”的时间窗口计算
   • 考点：复寄率的精准定义。
   • 设计：首先通过 MIN(create_time) 锁定用户首次用券时间点，然后再拿这个时间点去与该用户的所有订单时间做 > 比较。

5.2 SparkSQL 执行优化（面试加分项）

若数据量达到千万/亿级，直接跑上述 SQL 可能会遇到性能瓶颈。在面试中主动提出以下优化方案，能极大增加通过率：

1. 广播连接（MapJoin / Broadcast Hash Join）
   • 背景：coupon_detail（优惠券明细表）通常是一张极小的维度表（几十或几百行），而 express_order 是海量事实表。
   • 优化：Spark 默认会进行 Shuffle Hash Join。可以通过 Hint 强制将小表广播到各个 Executor，避免 Shuffle：
     sql

     SELECT /*+ BROADCAST(c) */ o.order_id ... 
     FROM express_order o LEFT JOIN coupon_detail c ...

2. 避免大表与大表的二次 Join（针对复寄率计算）
   • 痛点：在 user_re_express 步骤中，我们用 user_coupon_timeline（用户首次用券时间表）再次 Join 了 express_order（大表），这会触发大范围的 Shuffle。
   • 高级替代方案（使用窗口函数）：
     可以使用窗口函数在一次扫描中完成时间比对，减少一次 Join：
     sql

     WITH temp AS (
         SELECT 
             user_id,
             create_time,
             coupon_id,
             -- 标记当前订单是否使用了优惠券
             IF(coupon_id IS NOT NULL AND coupon_id != 'None', 1, 0) AS is_coupon,
             -- 获取该用户全量订单中，最早一次使用优惠券的时间
             MIN(CASE WHEN coupon_id IS NOT NULL AND coupon_id != 'None' THEN create_time END) 
                 OVER(PARTITION BY user_id) AS first_coupon_time
         FROM express_order
         WHERE create_time >= '2023-10-01 00:00:00' AND create_time < '2023-11-01 00:00:00'
     )
     SELECT 
         user_id,
         -- 如果存在某笔订单的产生时间晚于首次用券时间，则记为复寄
         MAX(CASE WHEN create_time > first_coupon_time THEN 1 ELSE 0 END) AS is_re_express
     FROM temp
     WHERE first_coupon_time IS NOT NULL -- 过滤出用过券的用户
     GROUP BY user_id;

     此方案只对 express_order 进行了一次扫描，利用 Window 算子省去了大表 Join 带来的 网络 IO 和 Shuffle 压力，是典型的 Spark 调优高分回答。