基于本文回答

播面 播面

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

统计每个店铺的退款率（退款订单数 / 总订单数）。输出退款订单数大于 100 且退款率排名前 10 的店铺 ID

面试真题：高频电商数据分析 — 店铺退款率统计

1. 题目描述

在电商业务中，退款率是衡量商家服务质量和商品质量的重要指标。请编写一个 SQL 语句，统计每个店铺的退款率（退款订单数 / 总订单数）。
要求输出：退款订单数大于 100 且退款率排名前 10 的店铺 ID、退款订单数、总订单数以及退款率（保留4位小数）。

2. 示例数据

订单表：orders

(注：以下为模拟简化数据，实际业务中数据量会更大)

order_id shop_id order_status create_time
O10001 S001 refunded 2023-10-01 10:00:00
O10002 S001 completed 2023-10-01 10:05:00
O10003 S001 refunded 2023-10-01 10:10:00
O10004 S002 completed 2023-10-01 10:15:00
O10005 S002 completed 2023-10-01 10:20:00
O10006 S003 refunded 2023-10-01 10:25:00
O10007 S003 refunded 2023-10-01 10:30:00

说明：order_status 字段中，'refunded' 表示退款，'completed' 表示交易成功。

3. 期望输出结果维度

(最终查询结果将根据退款率降序排列，取前10名，且退款数需 > 100。以下为示意输出)

shop_id refund_orders total_orders refund_rate
S012 150 200 0.7500
S005 120 240 0.5000
S088 300 800 0.3750
... ... ... ...

4. SQL 解决方案 (Standard SQL)

sql 
SELECT 
    shop_id,
    SUM(CASE WHEN order_status = 'refunded' THEN 1 ELSE 0 END) AS refund_orders,
    COUNT(order_id) AS total_orders,
    ROUND(
        SUM(CASE WHEN order_status = 'refunded' THEN 1 ELSE 0 END) * 1.0 / COUNT(order_id), 
        4
    ) AS refund_rate
FROM 
    orders
GROUP BY 
    shop_id
HAVING 
    SUM(CASE WHEN order_status = 'refunded' THEN 1 ELSE 0 END) > 100
ORDER BY 
    refund_rate DESC
LIMIT 10;

5. 核心考点与深度解析（助力面试）

在面试中，这道题看似简单，但考官实际上在考察你对 聚合函数、条件过滤时机、数据类型转换以及性能优化 的综合掌握。

考点一：如何优雅地统计满足特定条件的行数？
  • 常用写法：SUM(CASE WHEN order_status = 'refunded' THEN 1 ELSE 0 END)。
  • 替代写法（MySQL特有）：SUM(order_status = 'refunded')。因为在 MySQL 中，布尔值 true 会被隐式转换为 1，false 转换为 0。
  • 面试加分项：在标准 SQL 中，使用 CASE WHEN 兼容性最好。另外，也可以使用 COUNT(CASE WHEN order_status = 'refunded' THEN 1 END)，因为 COUNT 会自动忽略 NULL 值。
考点二：WHERE 与 HAVING 的区别
  • 这是面试的高频死穴。
  • 错误做法：在 WHERE 子句中直接使用 refund_orders > 100。
  • 正确做法：必须使用 HAVING。因为 WHERE 是在分组前对原始行进行过滤，而本题的限制条件“退款订单数 > 100”是基于 GROUP BY shop_id 之后的聚合结果，因此必须放在 HAVING 子句中。
考点三：规避“整数除法截断”问题 (Integer Division)
  • 在很多数据库（如 SQL Server、PostgreSQL）中，如果两个整数相除（例如 150 / 200），结果会自动截断为整数 0。
  • 解决方案：在分子或分母乘以 1.0，将其强制转换为浮点数。例如：SUM(...) * 1.0 / COUNT(...)。然后再外层套用 ROUND(..., 4) 保留四位小数。
考点四：性能优化思考 (面试官追问)

当面试官问到：“如果 orders 表有几亿行数据，这个 SQL 跑得很慢，该怎么优化？”

  1. 索引优化：建立联合索引 idx_shop_status (shop_id, order_status)。这可以利用“覆盖索引”（Covering Index），避免全表回表扫描。
  2. 分区表：电商订单通常具有时间热度，可以按天或按月进行物理分区，在查询时加上时间限制（如 WHERE create_time >= '2023-01-01'），利用分区裁剪减少扫描数据量。
  3. 增量预汇总：对于这种高频统计看板，不建议直接跑实时大表。可以设计一张日汇总表（shop_daily_stats），每天凌晨增量计算前一天的订单数和退款数。查询时直接对日汇总表进行 SUM 聚合，速度能提升数万倍。
00:00
00:00