一、问题背景
本次我们要解决的问题是从 Trips 表和 Users 表中，计算在特定日期范围内（2013-10-01 至 2013-10-03）有至少一次行程的非禁止用户（乘客和司机都必须未被禁止）的取消率。这不仅需要我们对 SQL 的多表连接操作有熟练的掌握，还需要处理数据筛选、计算等多个环节。
二、表结构分析
1、Trips 表
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | varchar |
+-------------+----------+
id 是这张表的主键（具有唯一值的列）。
这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。
status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
2、Users 表
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| users_id | int |
| banned | enum |
| role | enum |
+-------------+----------+
users_id 是这张表的主键（具有唯一值的列）。
这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
三、解题思路
-
筛选非禁止用户的行程：我们需要从
Trips表和Users表中筛选出乘客和司机都未被禁止的行程。这就需要进行两次连接操作，一次连接乘客相关的用户信息，一次连接司机相关的用户信息，并筛选出banned为No的记录。 -
统计每天的行程总数和取消行程数：在筛选出符合条件的行程后，根据日期
request_at对行程进行分组，分别统计每天的行程总数和取消行程数（status为 'cancelled_by_driver' 或 'cancelled_by_client' 的行程）。 -
计算取消率：根据统计得到的每天的行程总数和取消行程数，计算出每天的取消率。取消率的计算公式为：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。最后，将取消率四舍五入保留两位小数。
四、SQL 代码实现
-- 选择日期和计算取消率
SELECT t.request_at AS Day,-- 计算取消率，使用 ROUND 函数保留两位小数ROUND(SUM(CASE WHEN t.status IN ('cancelled_by_driver', 'cancelled_by_client') THEN 1 ELSE 0 END) / NULLIF(SUM(1), 0), 2) AS Cancellation_Rate
FROM Trips t
-- 连接乘客用户表，筛选非禁止乘客
JOIN Users c ON t.client_id = c.users_id AND c.banned = 'No' AND c.role = 'client'
-- 连接司机用户表，筛选非禁止司机
JOIN Users d ON t.driver_id = d.users_id AND d.banned = 'No' AND d.role = 'driver'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at;
五、代码详细解释
1、选择和计算取消率部分：
SELECT t.request_at AS Day,ROUND(SUM(CASE WHEN t.status IN ('cancelled_by_driver', 'cancelled_by_client') THEN 1 ELSE 0 END) / NULLIF(SUM(1), 0), 2) AS Cancellation_Rate
-
t.request_at AS Day：将Trips表中的request_at列重命名为Day，作为结果表中的日期列。 -
ROUND(SUM(CASE WHEN t.status IN ('cancelled_by_driver', 'cancelled_by_client') THEN 1 ELSE 0 END) / NULLIF(SUM(1), 0), 2) AS Cancellation_Rate：
（1）SUM(CASE WHEN t.status IN ('cancelled_by_driver', 'cancelled_by_client') THEN 1 ELSE 0 END)：使用 CASE WHEN 语句判断行程状态，如果是取消状态（'cancelled_by_driver' 或 'cancelled_by_client'），则计为 1，否则计为 0，然后使用 SUM 函数统计取消行程的总数。
（2）NULLIF(SUM(1), 0)：SUM(1) 统计的是行程的总数，NULLIF 函数用于避免除数为 0 的情况，如果行程总数为 0，则返回 NULL。
（3）ROUND(..., 2)：将计算得到的取消率四舍五入保留两位小数，并将结果列重命名为 Cancellation_Rate。
2、连接表部分：
JOIN Users c ON t.client_id = c.users_id AND c.banned = 'No' AND c.role = 'client'
JOIN Users d ON t.driver_id = d.users_id AND d.banned = 'No' AND d.role = 'driver'
-
JOIN Users c ON t.client_id = c.users_id AND c.banned = 'No' AND c.role = 'client'：将Trips表与Users表连接，连接条件是Trips表中的client_id等于Users表中的users_id，并且该用户未被禁止（banned = 'No'）且角色为乘客（role = 'client'）。 -
JOIN Users d ON t.driver_id = d.users_id AND d.banned = 'No' AND d.role = 'driver'：将Trips表与Users表再次连接，连接条件是Trips表中的driver_id等于Users表中的users_id，并且该用户未被禁止（banned = 'No'）且角色为司机（role = 'driver'）。
3、筛选日期范围部分：
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
使用 WHERE 子句筛选出 request_at 在 2013-10-01 至 2013-10-03 范围内的行程记录。
4、分组部分：
GROUP BY t.request_at
根据 request_at 对筛选后的行程记录进行分组，以便分别计算每天的取消率。
六、复杂度分析
-
时间复杂度：由于我们需要对
Trips表和Users表进行连接操作，并且进行了分组和聚合计算，时间复杂度主要取决于表的大小。假设Trips表有m条记录，Users表有n条记录，连接操作的时间复杂度为 (O(mn))，分组和聚合计算的时间复杂度为 (O(m))，因此总的时间复杂度为 (O(mn + m))。 -
空间复杂度：在计算过程中，我们主要使用了一些临时变量和聚合结果，空间复杂度为 (O(k))，其中
k是分组的数量（即日期的数量，这里为 3），因此空间复杂度为 (O(1))。
七、测试用例验证
我们可以使用题目中给出的示例数据进行测试：
输入数据：
-- Trips 表数据插入
INSERT INTO Trips (id, client_id, driver_id, city_id, status, request_at) VALUES
(1, 1, 10, 1, 'completed', '2013-10-01'),
(2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01'),
(3, 3, 12, 6, 'completed', '2013-10-01'),
(4, 4, 13, 6, 'cancelled_by_client', '2013-10-01'),
(5, 1, 10, 1, 'completed', '2013-10-02'),
(6, 2, 11, 6, 'completed', '2013-10-02'),
(7, 3, 12, 6, 'completed', '2013-10-02'),
(8, 2, 12, 12, 'completed', '2013-10-03'),
(9, 3, 10, 12, 'completed', '2013-10-03'),
(10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');-- Users 表数据插入
INSERT INTO Users (users_id, banned, role) VALUES
(1, 'No', 'client'),
(2, 'Yes', 'client'),
(3, 'No', 'client'),
(4, 'No', 'client'),
(10, 'No', 'driver'),
(11, 'No', 'driver'),
(12, 'No', 'driver'),
(13, 'No', 'driver');
预期输出：
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
验证过程：将上述 SQL 代码在数据库中运行，将得到的结果与预期输出进行对比，如果结果一致，则说明我们的代码实现是正确的。
感谢各位的阅读，后续将持续给大家讲解力扣中的算法题和数据库题，如果觉得这篇内容对你有帮助，别忘了点赞和关注，后续还有更多精彩的算法解析与你分享！