【LeetCode 题解】数据库：262.行程和用户

一、问题背景

本次我们要解决的问题是从 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’) 。

三、解题思路

1. 筛选非禁止用户的行程：我们需要从 Trips 表和 Users 表中筛选出乘客和司机都未被禁止的行程。这就需要进行两次连接操作，一次连接乘客相关的用户信息，一次连接司机相关的用户信息，并筛选出 banned 为 No 的记录。

2. 统计每天的行程总数和取消行程数：在筛选出符合条件的行程后，根据日期 request_at 对行程进行分组，分别统计每天的行程总数和取消行程数（status 为 'cancelled_by_driver' 或 'cancelled_by_client' 的行程）。

3. 计算取消率：根据统计得到的每天的行程总数和取消行程数，计算出每天的取消率。取消率的计算公式为：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。最后，将取消率四舍五入保留两位小数。

四、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 对筛选后的行程记录进行分组，以便分别计算每天的取消率。

六、复杂度分析

1. 时间复杂度：由于我们需要对 Trips 表和 Users 表进行连接操作，并且进行了分组和聚合计算，时间复杂度主要取决于表的大小。假设 Trips 表有 m 条记录，Users 表有 n 条记录，连接操作的时间复杂度为 (O(mn))，分组和聚合计算的时间复杂度为 (O(m))，因此总的时间复杂度为 (O(mn + m))。

2. 空间复杂度：在计算过程中，我们主要使用了一些临时变量和聚合结果，空间复杂度为 (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 代码在数据库中运行，将得到的结果与预期输出进行对比，如果结果一致，则说明我们的代码实现是正确的。

感谢各位的阅读，后续将持续给大家讲解力扣中的算法题和数据库题，如果觉得这篇内容对你有帮助，别忘了点赞和关注，后续还有更多精彩的算法解析与你分享！