如何用更简洁的SQL语句计算各骑手的成功配送率？

表结构与测试数据 #

CREATE TABLE runner_orders (
  "order_id" integer,
  "runner_id" integer,
  "distance" decimal (5,2),
  "duration" decimal (5,2),
  "cancellation" varchar(23)
);

INSERT INTO runner_orders
  ("order_id", "runner_id", "distance", "duration", "cancellation")
VALUES
  (1, 1,  20, 32, ''),
  ('2', '1',  20, 27, ''),
  ('3', '1',  13.4, 20, ''),
  ('4', '2',  23.4, 40, ''),
  ('5', '3',  10, 15, ''),
  ('6', '3',  NULL, NULL, 'Restaurant Cancellation'),
  ('7', '2',  25, 25, ''),
  ('8', '2',  23.4, 15, ''),
  ('9', '2',  NULL, NULL, 'Customer Cancellation'),
  ('10', '1', 10, 10, '');

需求 #

计算每个骑手（runner_id）的成功配送百分比，成功配送指cancellation字段为空字符串的订单。

现有CTE实现（正确但冗余） #

以下是通过CTE实现的正确解法，但代码相对冗长：

WITH cte_1
AS (
  SELECT runner_id, (COUNT(runner_id))*100 AS percentages
  FROM runner_orders 
  WHERE cancellation = ''
  group by runner_id
)
SELECT cte_1.runner_id, (percentages/(COUNT(ru.cancellation))) as percentages_successful_deliveries
FROM cte_1
FULL JOIN runner_orders AS ru
  ON cte_1.runner_id = ru.runner_id
GROUP BY cte_1.runner_id, cte_1.percentages
ORDER BY runner_id

简洁非CTE解决方案 #

可以通过条件聚合直接完成计算，无需CTE或子查询，代码更简洁高效：

方案1：使用COUNT(CASE...) #

SELECT 
  runner_id,
  ROUND(
    (COUNT(CASE WHEN cancellation = '' THEN 1 END) * 100.0) / COUNT(*),
    2
  ) AS percentages_successful_deliveries
FROM runner_orders
GROUP BY runner_id
ORDER BY runner_id;

方案2：使用SUM(CASE...) #

SELECT 
  runner_id,
  ROUND(
    (SUM(CASE WHEN cancellation = '' THEN 1 ELSE 0 END) * 100.0) / COUNT(*),
    2
  ) AS percentages_successful_deliveries
FROM runner_orders
GROUP BY runner_id
ORDER BY runner_id;

说明 #

• COUNT(CASE WHEN cancellation = '' THEN 1 END)：仅统计成功配送的订单数（CASE返回1时计数，否则忽略）
• SUM(CASE...)：通过返回1/0的方式，累加成功订单的数量
• 乘以100.0是为了将整数除法转为浮点除法，避免结果被截断
• ROUND(..., 2)用于保留两位小数，可根据需求调整小数位数

原尝试方案失败原因 #

你之前的子查询写法问题在于：

• 按runner_id和cancellation分组，会返回每个骑手的"成功"和"取消"两条记录，不符合需求
• 窗口函数PARTITION BY runner_id, cancellation没有正确计算骑手的总订单数，导致百分比计算错误

内容的提问来源于stack exchange，提问作者Ale