播面

面试题：自动取消订单占比统计

题目描述：
在电商业务中，用户下单后若在 30 分钟内未支付，系统会自动取消该订单。现需要统计每个一级商品品类下，这种“下单后 30 分钟内未支付而被系统自动取消的订单”占该品类“总下单量”的比例（结果保留两位小数，以百分比形式展示，如 25.50%）。

1. 示例数据

表 1：订单信息表 (t_order_info)

记录订单的主周期信息。

注：order_status 状态包括：PAID（已支付）、AUTO_CANCEL（系统自动取消）、USER_CANCEL（用户主动取消）、PENDING（待支付）。

表 2：订单明细表 (t_order_detail)

记录订单中包含的商品明细（一个订单可包含多个商品）。

表 3：商品信息表 (t_sku_info)

记录商品的属性及品类信息。

2. 期望输出结果

数据解析说明：

• 电子产品 (C1)：涉及订单 1001, 1002, 1005（共 3 单）。其中 1002 属于 30 分钟内系统自动取消（10:40 - 10:10 = 30分）；1005 虽是自动取消，但耗时 40 分钟（不计入分子）。占比 $1 / 3 = 33.33\%$。
• 服装鞋帽 (C2)：涉及订单 1003, 1004, 1006, 1007（共 4 单）。其中 1004（耗时25分）、1007（耗时20分）属于30分钟内系统自动取消。占比 $2 / 4 = 50.00\%$。

3. SparkSQL 实现

SELECT
    s.category1_name,
    -- 1. 统计该品类下的总下单量（对 order_id 去重，防止一单多商品导致重复计数）
    COUNT(DISTINCT o.order_id) AS total_orders,
    
    -- 2. 统计30分钟内被系统自动取消的订单数
    COUNT(DISTINCT CASE 
        WHEN o.order_status = 'AUTO_CANCEL' 
             AND (CAST(unix_timestamp(o.cancel_time) AS LONG) - CAST(unix_timestamp(o.create_time) AS LONG)) <= 1800
        THEN o.order_id 
    END) AS auto_cancel_30m_orders,
    
    -- 3. 计算比例并格式化输出
    CONCAT(
        ROUND(
            COUNT(DISTINCT CASE 
                WHEN o.order_status = 'AUTO_CANCEL' 
                     AND (CAST(unix_timestamp(o.cancel_time) AS LONG) - CAST(unix_timestamp(o.create_time) AS LONG)) <= 1800
                THEN o.order_id 
            END) * 100.0 / COUNT(DISTINCT o.order_id), 
            2
        ),
        '%'
    ) AS cancel_ratio
FROM t_order_info o
JOIN t_order_detail d ON o.order_id = d.order_id
JOIN t_sku_info s ON d.sku_id = s.sku_id
GROUP BY s.category1_name;

4. 核心考点与 SparkSQL 深度分析

在面试中，这道题看似简单，但实际隐藏了多个关于数据倾斜、时间函数选用以及指标粒度控制的考点：

考点一：一单多商品的“去重陷阱”

• 问题：一个订单（如 1001）可能包含多个同一品类的商品（S01, S02 均属电子产品）。如果直接使用 COUNT(1) 或 COUNT(o.order_id)，该订单在 GROUP BY 分组后会被重复计算。
• 解答：分子和分母均必须使用 COUNT(DISTINCT o.order_id)，确保每个订单在每个品类维度下只被精确计数一次。

考点二：时间差值的精准计算

• 在 SparkSQL 中，处理时间差值有多种方式：
  1. unix_timestamp(t1) - unix_timestamp(t2) <= 1800：最通用、最稳定。转化为秒级时间戳做减法，能精准规避跨天、跨时区等边界问题。
  2. 使用 datediff / timediff / col1 - col2：SparkSQL 的直接减法会返回 Interval 类型，解析起来较为繁琐，且在不同 Spark 版本中行为可能不一致，因此推荐在面试中书写显式的时间戳转换，展示严谨性。

考点三：Spark 性能优化（针对大表 Join）

在实际生产环境中，这三张表往往数据量极大。面试官可能会追问：“如何优化这个 SQL 的执行效率？”

• 广播连接 (Broadcast Hash Join)：
  • t_sku_info（商品表）通常是一张相对较小的维度表。可以使用 BROADCAST 提示，强制将小表广播到各个 Executor，避免大表在 Shuffle 阶段带来的网络传输开销。
  • 写法示例：FROM t_order_info o JOIN t_order_detail d ... JOIN /*+ BROADCAST(s) */ t_sku_info s
• 过滤下推 (Predicate Pushdown)：
  • 在 Join 之前，如果能对订单表的时间范围（如只统计近一个月的订单）进行过滤，应尽早过滤，减少参与 Join 的数据量。

考点四：条件聚合 (Conditional Aggregation)

• 技巧：这里使用了 COUNT(DISTINCT CASE WHEN ... THEN order_id END)。
• 原理：在 SQL 中，COUNT(column) 会自动忽略 NULL 值。当条件不满足时，CASE 表达式默认返回 NULL，因此能够完美实现“只统计符合特定条件的去重新增值”，避免了写多个子查询再进行 Join 的低效写法。