基于本文回答

播面 播面

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

统计下单后 30 分钟内未支付而被系统自动取消的订单占总下单量的比例，并按一级商品品类进行分组

面试真题：统计各一级品类下，下单 30 分钟内未支付被系统自动取消的订单占比

1. 题目背景

在电商业务中，用户下单后往往会有一定的支付等待期（如30分钟）。如果用户在规定时间内未支付，系统会自动取消订单并释放库存。高比例的“超时未支付自动取消”通常意味着用户流失率高或存在恶意刷单行为。

面试要求：编写 SQL 统计每个一级商品品类下，下单后 30 分钟内（含30分钟）未支付而被系统自动取消的订单占该品类总下单量的比例。结果按比例降序排列。

2. 候选表结构及数据样例

表 1：订单表 t_order

记录订单的基础状态及时间信息。

  • order_status 说明：paid（已支付）、user_canceled（用户主动取消）、auto_canceled（系统超时自动取消）。
order_id user_id create_time cancel_time pay_time order_status
O001 U101 2023-10-01 10:00:00 2023-10-01 10:30:00 NULL auto_canceled
O002 U102 2023-10-01 10:15:00 NULL 2023-10-01 10:20:00 paid
O003 U103 2023-10-01 11:00:00 2023-10-01 11:15:00 NULL user_canceled
O004 U104 2023-10-01 12:00:00 2023-10-01 12:45:00 NULL auto_canceled
O005 U105 2023-10-01 13:00:00 2023-10-01 13:25:00 NULL auto_canceled
表 2：订单明细表 t_order_detail

记录订单包含的商品。

order_id goods_id goods_num
O001 G001 1
O002 G002 1
O003 G003 2
O004 G003 1
O005 G004 1
表 3：商品信息表 t_goods

记录商品及其类目归属。

goods_id goods_name category_id_1 category_name_1
G001 智能手机 C01 电子产品
G002 蓝牙耳机 C01 电子产品
G003 运动卫衣 C02 服装鞋帽
G004 休闲牛仔裤 C02 服装鞋帽

3. 期望输出结果

一级品类 总下单量 超时自动取消量 自动取消占比(%)
电子产品 2 1 50.00%
服装鞋帽 3 1 33.33%
  • 解析说明：
    • 电子产品下有订单 O001（30分钟内自动取消）和 O002（已支付），占比 1/2 = 50.00%。
    • 服装鞋帽下有订单 O003（用户主动取消）、O004（45分钟才自动取消，超过30分钟限制）、O005（25分钟自动取消），符合条件的仅 O005，占比 1/3 = 33.33%。

4. SQL 解答（MySQL 8.0 标准）

sql 
SELECT
    g.category_name_1 AS `一级品类`,
    COUNT(DISTINCT o.order_id) AS `总下单量`,
    COUNT(DISTINCT CASE 
        WHEN o.order_status = 'auto_canceled' 
             AND TIMESTAMPDIFF(SECOND, o.create_time, o.cancel_time) <= 1800 
        THEN o.order_id 
    END) AS `超时自动取消量`,
    CONCAT(
        ROUND(
            COUNT(DISTINCT CASE 
                WHEN o.order_status = 'auto_canceled' 
                     AND TIMESTAMPDIFF(SECOND, o.create_time, o.cancel_time) <= 1800 
                THEN o.order_id 
            END) * 100.0 / COUNT(DISTINCT o.order_id), 
            2
        ), 
        '%'
    ) AS `自动取消占比(%)`
FROM t_order o
JOIN t_order_detail od ON o.order_id = od.order_id
JOIN t_goods g ON od.goods_id = g.goods_id
GROUP BY g.category_name_1
ORDER BY `自动取消占比(%)` DESC;

5. 核心考点与 SQL 分析（面试通关秘籍）

考点一：时间差计算的精准度（TIMESTAMPDIFF）
  • 常见误区：使用 TIMESTAMPDIFF(MINUTE, start, end) <= 30。
  • 细节陷阱：在 MySQL 中，TIMESTAMPDIFF(MINUTE, ...) 是向下取整的。例如，相差 30 分 59 秒也会被计算为 30 分钟。
  • 大厂解法：在要求精准的业务场景（如30分钟限制）中，建议将单位转换为“秒”，即 TIMESTAMPDIFF(SECOND, start, end) <= 1800，以此规避边界误差。
考点二：条件过滤与指标共存（Conditional Aggregation）
  • 考查重点：不要盲目在 WHERE 子句中过滤 order_status = 'auto_canceled'。
  • 原因：如果在外层直接使用 WHERE 过滤，会导致分母（总下单量）同样被过滤，从而无法计算占比。
  • 解法：必须使用条件聚合（CASE WHEN 或 IF）。在 COUNT(DISTINCT ...) 内部嵌套条件，只对分子进行过滤，分母依然保留全量。
考点三：精度丢失与格式化输出
  • 除零异常防御：由于使用了 GROUP BY，需注意分子为 0 的情况，COUNT(...) 为 0 时除法可以正常运行。若担心分母为 0 导致报错，可用 NULLIF(COUNT(...), 0) 保证安全。
  • 类型转换：在 SQL 中，整数除以整数（如 1 / 2）在某些数据库（如 SQL Server/PostgreSQL）中会直接截断为 0。因此计算比例时，务必将分子乘以 100.0（转换为浮点数）后再做除法。
  • 美化展示：使用 ROUND(..., 2) 保留两位小数，并使用 CONCAT(..., '%') 拼接百分号，能极大提升结果的业务可读性，展现良好的工程素养。
考点四：一对多关联（Join）引发的数据翻倍
  • 隐患：如果一个订单包含多件同属于一个一级品类的商品，在 t_order_detail 中会产生多条记录。
  • 对策：在聚合计算订单量时，必须使用 COUNT(DISTINCT o.order_id) 进行去重，防止因为商品明细的多条记录导致订单量被重复放大计算。
00:00
00:00