面试题：计算用户从点击促销 Banner 到最终购买该商品的平均耗时（小时）

1. 模拟场景与数据集

在电商场景中，为了评估 Banner 广告的转化效果和用户的决策周期，我们需要计算用户点击促销 Banner 广告到最终购买该促销商品的平均耗时（单位：小时）。

表 1：用户点击 Banner 记录表 (user_clicks)

记录用户点击促销 Banner 的日志。由于用户可能多次点击同一个 Banner，我们通常以购买前离购买时间最近的一次点击作为归因的起点。

表 2：用户购买记录表 (user_purchases)

记录用户最终购买商品的日志。

2. SparkSQL 解决方案

核心思路：

1. 关联匹配（Join）：通过 user_id 和 item_id 将点击表和购买表进行内关联。
2. 时间约束：点击时间必须在购买时间之前（click_time <= purchase_time）。
3. 归因去重（Attribution）：如果用户在购买前有多次点击，取最后一次（MAX）点击时间作为归因点击。
4. 计算差值：计算购买时间与归因点击时间的时间差（小时），并求平均值。

WITH matched_clicks AS (
    -- 1. 关联点击与购买表，并找出购买前最近的一次点击
    SELECT 
        p.user_id,
        p.item_id,
        p.purchase_time,
        MAX(c.click_time) AS last_click_time
    FROM user_purchases p
    INNER JOIN user_clicks c 
        ON p.user_id = c.user_id 
       AND p.item_id = c.item_id
    WHERE c.click_time <= p.purchase_time
    GROUP BY p.user_id, p.item_id, p.purchase_time
)
-- 2. 计算平均耗时（小时）
SELECT 
    ROUND(
        AVG(
            (CAST(unix_timestamp(purchase_time) AS DOUBLE) - CAST(unix_timestamp(last_click_time) AS DOUBLE)) / 3600.0
        ), 2
    ) AS avg_hours_to_buy
FROM matched_clicks;

样例数据计算过程解析：

• U001: 购买时间 15:00，购买前有两次点击（10:00 和 12:00），取最近的 12:00。耗时：3小时。
• U002: 购买时间 10-03 14:00，点击时间 10-01 14:00。耗时：48小时。
• U003: 购买时间 08:30，点击时间 09:00（点击在购买之后，过滤不计入）。
• U004: 购买时间 15:30，点击时间 15:00。耗时：0.5小时。
• 平均耗时：(3 + 48 + 0.5) / 3 = 17.17 小时。

3. SparkSQL 深度剖析与面试通关技巧

面试官在考察这道题时，不仅仅看你能不能写出 JOIN，更看重你对分布式计算瓶颈、数据倾斜和归因逻辑的理解。

考点 1：如何处理多对多关联引起的数据膨胀？

• 痛点：如果一个热门商品被用户点击了成百上千次，然后再购买，直接 JOIN 会导致该用户的数据急剧膨胀，产生 Shuffle 瓶颈。
• 面试加分答法：

  “在实际生产环境中，为了防止数据膨胀，我们可以限制归因窗口（例如只看购买前 7 天内的点击）。在 JOIN 条件中加入时间窗口限制，可以让 Spark 进行区间关联（Interval Join）优化，减少 Shuffle 缓存的数据量。”

  sql

  -- 优化写法：加入时间窗口限制（如7天内）
  ON p.user_id = c.user_id 
  AND p.item_id = c.item_id
  AND c.click_time BETWEEN p.purchase_time - INTERVAL 7 DAYS AND p.purchase_time

考点 2：时间函数的选择与 Spark 兼容性

• 痛点：不同 Spark 版本对时间计算的支持不同。直接用 datediff 只能精确到天，无法精确到小时。
• 面试加分答法：

  “在 SparkSQL 中，如果需要高精度的时间差（如小时/分钟），推荐将时间戳转换为 Unix 时间戳（秒级）进行减法运算，再除以 3600（或 60）。在 Spark 3.x 中，也可以直接使用 timestampdiff(hour, last_click_time, purchase_time)，这样代码更加简洁且利用了底层引擎的本地优化。”

考点 3：数据倾斜（Data Skew）优化

• 痛点：大促期间，某些极其热门的商品（如 iPhone 促销）或超级用户（如爬虫、大商户）会导致某些 Task 处理的数据量远大于其他 Task，造成 “长尾效应”（99% 的 Task 都结束了，只有 1% 在卡着）。
• 面试加分答法：

  “如果遇到数据倾斜，我有两种优化策略：

  1. 广播连接（Broadcast Hash Join）：如果购买表（通常较小）或过滤后的点击表小于 spark.sql.autoBroadcastJoinThreshold（默认10MB），我们可以强制使用 BROADCAST 提示，避免大范围的 Shuffle。
  2. 加盐（Salting）：如果必须进行 Shuffle Hash Join，可以对倾斜的 item_id 进行加盐（加上随机前缀），将倾斜的数据分散到多个分区，关联后再去掉盐值进行聚合。”