基于本文回答

播面 播面

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

统计退货订单中，从“买家发起申请 -> 商家同意 -> 买家寄回 -> 平台退款完成”每个节点的平均处理时长

面试真题：电商退货全链路流转效率分析

1. 题目背景

在电商平台的退货业务中，退货链路的流转效率直接影响用户的退款体验。一个完整的退货流程包含四个核心节点：买家发起申请 (APPLY) -> 商家同意 (AGREE) -> 买家寄回 (SEND_BACK) -> 平台退款完成 (COMPLETE)。

请编写 SparkSQL 查询，统计每个流转节点之间的平均处理时长（单位：小时，保留2位小数）。

2. 示例数据

退货流水日志表：return_order_log

该表记录了每个退货单在不同节点的操作时间。

return_id (退货单号) node (当前节点) operate_time (操作时间)
101 APPLY 2023-10-01 10:00:00
101 AGREE 2023-10-01 14:00:00
101 SEND_BACK 2023-10-02 10:00:00
101 COMPLETE 2023-10-03 10:00:00
102 APPLY 2023-10-01 12:00:00
102 AGREE 2023-10-01 18:00:00
102 SEND_BACK 2023-10-02 12:00:00
102 COMPLETE 2023-10-03 18:00:00
103 APPLY 2023-10-01 08:00:00
103 AGREE 2023-10-01 10:00:00

注：退款单 103 处于未完成状态，计算后续缺失节点的平均耗时时需自动过滤（不计入分母）。

3. 期望输出结果

avg_apply_to_agree_hours (申请->同意 平均小时) avg_agree_to_send_hours (同意->寄回 平均小时) avg_send_to_complete_hours (寄回->完成 平均小时)
4.00 19.00 27.00
  • 申请->同意: ( (14-10) + (18-12) + (10-8) ) / 3 = (4 + 6 + 2) / 3 = 4.00 小时
  • 同意->寄回: ( (10号10点 - 1号14点) + (2号12点 - 1号18点) ) / 2 = (20 + 18) / 2 = 19.00 小时
  • 寄回->完成: ( (3号10点 - 2号10点) + (3号18点 - 2号12点) ) / 2 = (24 + 30) / 2 = 27.00 小时

4. SparkSQL 解决方案

方案一：行转列（Pivot / 行级聚合）法（推荐，性能高且逻辑清晰）

通过 CASE WHEN 将多行状态数据压平到一行，再进行差值计算。

sql 
WITH pv_order AS (
    SELECT
        return_id,
        MAX(CASE WHEN node = 'APPLY' THEN CAST(operate_time AS TIMESTAMP) END) AS apply_time,
        MAX(CASE WHEN node = 'AGREE' THEN CAST(operate_time AS TIMESTAMP) END) AS agree_time,
        MAX(CASE WHEN node = 'SEND_BACK' THEN CAST(operate_time AS TIMESTAMP) END) AS send_back_time,
        MAX(CASE WHEN node = 'COMPLETE' THEN CAST(operate_time AS TIMESTAMP) END) AS complete_time
    FROM return_order_log
    GROUP BY return_id
)
SELECT
    -- 1. 申请 -> 同意
    ROUND(
        AVG(unix_timestamp(agree_time) - unix_timestamp(apply_time)) / 3600, 
        2
    ) AS avg_apply_to_agree_hours,
    
    -- 2. 同意 -> 寄回
    ROUND(
        AVG(unix_timestamp(send_back_time) - unix_timestamp(agree_time)) / 3600, 
        2
    ) AS avg_agree_to_send_hours,
    
    -- 3. 寄回 -> 完成
    ROUND(
        AVG(unix_timestamp(complete_time) - unix_timestamp(send_back_time)) / 3600, 
        2
    ) AS avg_send_to_complete_hours
FROM pv_order;
方案二：窗口函数 LEAD 法（适用于动态/未知流转节点的通用场景）

通过窗口函数获取下一个节点的节点名和时间，然后过滤特定跳转路径计算平均值。

sql 
WITH next_node_info AS (
    SELECT
        return_id,
        node AS cur_node,
        operate_time AS cur_time,
        LEAD(node) OVER (PARTITION BY return_id ORDER BY operate_time) AS next_node,
        LEAD(operate_time) OVER (PARTITION BY return_id ORDER BY operate_time) AS next_time
    FROM return_order_log
),
gap_seconds AS (
    SELECT
        -- 构造流转链路标识
        CONCAT(cur_node, '->', next_node) AS transition,
        (unix_timestamp(next_time) - unix_timestamp(cur_time)) AS duration_sec
    FROM next_node_info
    WHERE next_node IS NOT NULL
)
SELECT
    ROUND(AVG(CASE WHEN transition = 'APPLY->AGREE' THEN duration_sec END) / 3600, 2) AS avg_apply_to_agree_hours,
    ROUND(AVG(CASE WHEN transition = 'AGREE->SEND_BACK' THEN duration_sec END) / 3600, 2) AS avg_agree_to_send_hours,
    ROUND(AVG(CASE WHEN transition = 'SEND_BACK->COMPLETE' THEN duration_sec END) / 3600, 2) AS avg_send_to_complete_hours
FROM gap_seconds;

5. 核心考点与深度解析

在 SparkSQL 面试中，这道题重点考察候选人对数据变形（行转列）、时间函数以及数据倾斜/性能优化的理解：

1. 行转列（Aggregation + CASE WHEN）的妙用
  • 为什么用： 原始数据是“窄表”（一个订单多行状态），而计算节点耗时需要将同一订单的不同状态时间放到同一行进行减法。
  • 面试官关注点： 是否能熟练使用 MAX(CASE WHEN ...) 将行数据进行聚合。由于 GROUP BY return_id 后，每个 return_id 只有一行，非目标状态的数据为 NULL，利用 MAX() 可以过滤掉 NULL 提取到唯一的时间戳。
2. 时间差值的安全计算
  • 时间计算坑点： 很多候选人直接使用 datediff，但这只支持“天”级别。题目要求精确到“小时”（带小数），因此必须转换为秒级时间戳。
  • SparkSQL 函数选择：
    • unix_timestamp(string_ts)：将标准格式的字符串时间转换为秒（Unix时间戳）。
    • 差值除以 3600 转化为小时。
    • AVG() 函数会自动忽略 NULL 值，因此未完成的节点（如 103 的 SEND_BACK 和 COMPLETE 时间为 NULL）在做减法后也是 NULL，不会影响 avg_agree_to_send 和 avg_send_to_complete 的分母计算。这是极为优雅的数据处理技巧。
3. 方案对比与 Spark 性能调优（高分加分项）

在面试中，主动分析两种方案的优劣会给面试官留下极佳的印象：

  • 方案一（行转列）性能更优：
    • 原理： 只经历了一次 GROUP BY return_id 的 Shuffle 过程，并且在 Map 端可以进行局部预聚合（Map-side combination），传输数据量小。
    • 适用场景： 业务链路固定、节点明确（如本题）。
  • 方案二（Window LEAD）更通用但开销稍大：
    • 原理： 使用了开窗函数 OVER (PARTITION BY return_id ORDER BY operate_time)。在 Spark 中，窗口函数通常会阻止算子下推，且无法在 Map 端预聚合，必须将每个 return_id 的所有明细数据都 Shuffle 到同一个 Reduce 节点进行排序，如果单个 return_id 的日志量极大，极易造成数据倾斜。
    • 适用场景： 节点不固定，或者需要动态分析任意两个相邻节点的跳转效率。
00:00
00:00