基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

计算每个用户从完成注册（Register）到在平台上下首个订单（Order）之间，平均经历了多少个小时（精确到小数点后两位）

面试真题：计算用户从注册到首单的平均耗时

1. 题目背景

在电商、流媒体等平台中，新用户的“转化效率”是一个极关键的业务指标。本题要求计算：每个用户从完成注册（Register）到在平台上下首个订单（Order）之间，平均经历了多少个小时（结果保留2位小数）。

2. 示例数据

输入表：user_events（用户行为事件表）
user_id event_type event_time
101 Register 2023-10-01 10:00:00
101 Order 2023-10-01 12:30:00
101 Order 2023-10-02 15:00:00
102 Register 2023-10-01 08:00:00
102 View 2023-10-01 09:00:00
102 Order 2023-10-03 08:00:00
103 Register 2023-10-02 12:00:00
103 Order 2023-10-02 13:15:00
104 Register 2023-10-03 09:00:00

数据说明：

  • 用户 101 在注册后 2.5 小时下了首单，后续还有一次下单行为（应忽略非首单）。
  • 用户 102 在注册后 48 小时下了首单（中间有一次浏览行为 View 应忽略）。
  • 用户 103 在注册后 1.25 小时下了首单。
  • 用户 104 仅注册，未下单，计算平均值时应排除该用户。
期望输出结果
avg_hours
17.25

(计算逻辑：((2.5 + 48 + 1.25) / 3) = 17.25 小时)

3. SparkSQL 解决方案

sql 
WITH user_milestones AS (
    SELECT 
        user_id,
        -- 获取每个用户的注册时间
        MIN(CASE WHEN event_type = 'Register' THEN event_time END) AS register_time,
        -- 获取每个用户的首单时间
        MIN(CASE WHEN event_type = 'Order' THEN event_time END) AS first_order_time
    FROM 
        user_events
    GROUP BY 
        user_id
)
SELECT 
    ROUND(
        AVG(
            (CAST(first_order_time AS LONG) - CAST(register_time AS LONG)) / 3600.0
        ), 2
    ) AS avg_hours
FROM 
    user_milestones
WHERE 
    register_time IS NOT NULL 
    AND first_order_time IS NOT NULL;

4. 面试官视角：考点深入剖析与调优

在 SparkSQL 面试中，这道题看似简单，但能拉开候选人差距的地方在于底层执行计划的理解和对 Spark 特性的掌握。

考点一：条件聚合（Conditional Aggregation） vs 自连接（Self-Join）
  • 不推荐写法（Self-Join）：很多候选人会写成两张子查询表（一张过滤 Register，一张过滤 Order）进行 JOIN。
    • 弊端：这在 Spark 中会引入 Shuffle（Shuffle Hash Join 或 Sort Merge Join），导致大量的数据网络传输，性能较差。
  • 推荐写法（条件聚合）：如上述答案，利用 GROUP BY user_id 配合 MIN(CASE WHEN...)。
    • 优势：在 Spark 底层，这通常只需一次 Map-side 聚合（Hash Aggregate），大大减少了 Shuffle 的数据量，执行效率呈倍数提升。
考点二：时间差值计算的 Spark 最佳实践
  • 在 SparkSQL 中，直接将 Timestamp 转换成 LONG 类型（表示自 Epoch 以来的秒数）进行减法计算，是最稳妥且高效的方式（避免了时区转换和复杂的日期函数嵌套）。
  • 计算公式：(first_order_time - register_time) / 3600.0。注意除以 3600.0（带小数点）能自动转换为 Double 类型，避免整数除法丢失精度。
考点三：异常数据与边界处理（体现工程严谨性）
  • 未下单用户过滤：必须在最外层使用 WHERE register_time IS NOT NULL AND first_order_time IS NOT NULL 过滤掉未下单（如 104）或未注册直接下单的异常数据。
  • 多单去重：使用 MIN(event_time) ... WHERE event_type = 'Order' 保证拿到的必然是首单，过滤掉后续重复下单的影响。
考点四：Spark 性能调优延伸（加分项）

若面试官追问：“如果 user_events 表极大，上述 SQL 执行很慢，你会如何调优？”

  1. 数据倾斜（Data Skew）：如果某些大促期间注册/下单用户暴增，GROUP BY user_id 可能会产生倾斜。可以开启 Spark 3.x 的 AQE（Adaptive Query Execution） 自适应查询执行，或使用加盐（Salted）两阶段聚合。
  2. 过滤下推（Predicate Pushdown）：在聚合前，先 WHERE event_type IN ('Register', 'Order') 过滤掉占大头的 View、Click 等无用事件，极大地减少参与 Shuffle 的数据量。
00:00
00:00