播面

面试真题：电商退货流程节点平均处理时长统计

1. 题目背景

在电商业务中，退货体验是影响用户复购率的关键因素。为了监控退货链路的效率，我们需要统计退货流程中各个核心节点之间的平均处理耗时。
请根据给定的退款操作日志表，计算出以下三个阶段的平均处理时长（单位：小时，保留2位小数）：

1. 申请到同意：买家发起申请 -> 商家同意
2. 同意到寄回：商家同意 -> 买家寄回
3. 寄回到退款：买家寄回 -> 平台退款完成

2. 候选数据源

退货日志表：refund_log

注：订单1003目前只进行到“商家同意”阶段。

3. 期望输出结果

计算逻辑说明：

• 申请-同意：(1001单2小时 + 1002单4小时 + 1003单2小时) / 3 = 3.00小时
• 同意-寄回：(1001单6小时 + 1002单24小时) / 2 = 15.00小时
• 寄回-退款：(1001单24小时 + 1002单24小时) / 2 = 24.00小时

4. 解决方案 SQL (MySQL 8.0+)

WITH pv_refund AS (
    -- 1. 行转列：将同一个订单的各个节点时间拉平到同一行
    SELECT 
        order_id,
        MAX(CASE WHEN status = '买家发起申请' THEN log_time END) AS apply_time,
        MAX(CASE WHEN status = '商家同意' THEN log_time END) AS agree_time,
        MAX(CASE WHEN status = '买家寄回' THEN log_time END) AS ship_time,
        MAX(CASE WHEN status = '平台退款完成' THEN log_time END) AS refund_time
    FROM refund_log
    GROUP BY order_id
)
-- 2. 计算各个阶段的差值并求平均值
SELECT 
    ROUND(AVG(TIMESTAMPDIFF(SECOND, apply_time, agree_time) / 3600.0), 2) AS avg_apply_to_agree_hours,
    ROUND(AVG(TIMESTAMPDIFF(SECOND, agree_time, ship_time) / 3600.0), 2) AS avg_agree_to_ship_hours,
    ROUND(AVG(TIMESTAMPDIFF(SECOND, ship_time, refund_time) / 3600.0), 2) AS avg_ship_to_refund_hours
FROM pv_refund;

5. 剖析与面试应对技巧

此题是标准的“漏斗分析/时效分析”场景，在电商、物流、客服域面试中出现频率极高。面试官主要考察以下几个技术维度和业务思考：

技术细节解析

1. 行转列（Pivot）的应用：
   原始数据是“纵表”（每个订单有多行状态日志），如果不做处理，直接使用 JOIN 关联自身会产生大量的笛卡尔积，且 SQL 极难维护。
   最优解法是使用 GROUP BY + CASE WHEN 进行条件聚合，将纵表转为“横表”（即一个订单只有一行，包含所有节点的时间戳）。

2. 空值（NULL）的自动处理：
   在 AVG 聚合函数中，MySQL 会自动忽略 NULL 值。例如订单 1003 的 ship_time 和 refund_time 为 NULL，那么在计算 avg_agree_to_ship_hours 时，该单不会被计入分子和分母中，保证了均值的准确性。

3. 时间差精度问题：
   避免直接使用 TIMESTAMPDIFF(HOUR, ...)。因为该函数会直接向下取整（如 1小时50分钟 会被计算为 1小时），导致严重精度丢失。
   标准实践：先计算 SECOND（秒）或 MINUTE（分钟），再除以相应的系数（3600.0 或 60.0），最后用 ROUND 保留小数。

面试加分项（主动向面试官提及）

• 脏数据与逆流程处理：在实际业务中，可能存在“买家撤回申请后重新申请”或“商家拒绝后买家再次修改”的情况。如果在面试中，你能主动问一句：“如果存在多次重复的节点（例如多次发起申请），是以第一次为准还是最后一次为准？”，面试官会对你的业务敏感度刮目相看。
  • 应对方案：如果按最后一次，可以在行转列时，将 MAX(log_time) 作为取值标准。
• 大表性能优化：如果 refund_log 表每天有千万级数据，直接 GROUP BY 全表会引发 OOM。实际工作中应该限制时间分区，比如 WHERE log_time >= '2023-10-01 00:00:00'。