面试题：优惠券领券到消费转化率及平均使用时间间隔计算

1. 场景与数据准备

现有两张业务表：

• 领券记录表 (coupon_receive)：记录用户领取优惠券的时间。
• 消费记录表 (coupon_consume)：记录用户使用优惠券进行消费的时间。

表 1：领券记录表 (coupon_receive)

表 2：消费记录表 (coupon_consume)

2. SparkSQL 解决方案

WITH joined_data AS (
    -- 1. 使用 LEFT JOIN 关联领券表与消费表，确保未使用的券也能保留
    SELECT 
        r.user_id,
        r.coupon_id,
        r.receive_time,
        c.consume_time,
        -- 计算领券到消费的时间差（单位：小时），保留两位小数
        -- 使用 unix_timestamp 将时间转为秒，差值除以 3600 得到小时数
        CASE 
            WHEN c.consume_time IS NOT NULL 
            THEN (unix_timestamp(c.consume_time) - unix_timestamp(r.receive_time)) / 3600.0
            ELSE NULL 
        END AS use_interval_hours
    FROM coupon_receive r
    LEFT JOIN coupon_consume c 
      ON r.user_id = c.user_id 
     AND r.coupon_id = c.coupon_id
     -- 业务安全边界：消费时间必须大于等于领券时间
     AND c.consume_time >= r.receive_time
)
-- 2. 统计转化率与平均时间间隔
SELECT 
    -- 领券总数
    COUNT(1) AS total_received,
    -- 实际消费（核销）数
    COUNT(consume_time) AS total_consumed,
    -- 转化率 = 核销数 / 领券总数
    ROUND(COUNT(consume_time) / COUNT(1), 4) AS conversion_rate,
    -- 领券后到消费的平均时间间隔（小时）
    ROUND(AVG(use_interval_hours), 2) AS avg_use_interval_hours
FROM joined_data;

预期输出结果

(注：核销计算数据为：(1001, C_A)间隔 4.5h；(1002, C_A)间隔 2.25h；(1003, C_C)间隔 0.25h。平均值为 (4.5 + 2.25 + 0.25) / 3 = 2.33 小时)

3. 核心考点与 SparkSQL 优化分析

在面试中，仅仅写出 SQL 是不够的，面试官通常会针对该 SQL 进行深度追问。以下是应对面试的关键点分析：

1. 为什么使用 LEFT JOIN 而不是 INNER JOIN？

• 面试标准回答：因为要计算“转化率”，分母是所有领券的人（包括领了券但没用的人）。如果使用 INNER JOIN，没用券的数据会被过滤掉，导致分母缺失，无法计算出真实的转化率。

2. 时间差计算的 SparkSQL 实现

• 在 SparkSQL 中，处理时间差有多种方式：
  • unix_timestamp(t2) - unix_timestamp(t1)：最通用、最安全的方式。返回秒数差，除以 3600 灵活转换为小时。
  • datediff(t2, t1)：只能计算天数差，无法精确到小时。
  • cast(t2 as double) - cast(t1 as double)：在 Spark 3.x 中，直接将 Timestamp cast 转换为 Double/Long（表示秒数）也是高效的做法。

3. 空值（Null）处理与聚合函数的特性

• COUNT(1) 会统计包含 Null 在内的所有行（即总领券数）。
• COUNT(consume_time) 仅统计非 Null 的值（即实际消费数）。
• AVG(use_interval_hours) 会自动忽略 Null 值，直接计算已消费用户的平均间隔。
• 利用好聚合函数自动过滤 Null 的特性，可以避免写复杂的 CASE WHEN 过滤。

4. Spark 性能优化：大表 Join 小表（数据倾斜与广播）

• 追问：如果 coupon_consume（消费表）非常大，而 coupon_receive（领券表）相对较小，如何优化？
  • 回答：可以使用 Broadcast Hash Join（广播连接）。通过 BROADCAST 提示，将小表广播到各个 Executor 节点，在 Map 端完成 Join，从而避免 Spark 中最耗费性能的 Shuffle 阶段，同时也能有效防止数据倾斜。
  sql

  -- 语法示例
  SELECT /*+ BROADCAST(r) */ r.user_id ... 
  FROM coupon_receive r LEFT JOIN coupon_consume c ...

5. 极端边界条件：同一用户多次领取同一张券

• 追问：如果一个用户在不同时间领了多次同一张券（比如 C_A），且消费了多次，Join 会不会导致笛卡尔积？
  • 回答：会。如果存在这种业务场景，直接按 user_id 和 coupon_id 关联会导致多对多关联错乱。
  • 解决方案：在 Join 之前，必须引入唯一标示/行号。可以使用窗口函数 ROW_NUMBER() OVER (PARTITION BY user_id, coupon_id ORDER BY receive_time) 对领券和消费分别进行排序，按次序进行“第N次领券对第N次消费”的精确匹配关联。