LeetCode题练习与总结：行程和用户--262

一、题目描述

SQL Schema > Pandas Schema > 

表：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’) 。

表：Users

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| users_id    | int      |
| banned      | enum     |
| role        | enum     |
+-------------+----------+
users_id 是这张表的主键（具有唯一值的列）。
这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

编写解决方案找出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

返回结果表中的数据 无顺序要求 。

结果格式如下例所示。

示例 1：

输入： 
Trips 表：
+----+-----------+-----------+---------+---------------------+------------+
| id | client_id | driver_id | city_id | status              | request_at |
+----+-----------+-----------+---------+---------------------+------------+
| 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 表：
+----------+--------+--------+
| users_id | banned | role   |
+----------+--------+--------+
| 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              |
+------------+-------------------+
解释：
2013-10-01：
  - 共有 4 条请求，其中 2 条取消。
  - 然而，id=2 的请求是由禁止用户（user_id=2）发出的，所以计算时应当忽略它。
  - 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。
  - 取消率为 (1 / 3) = 0.33
2013-10-02：
  - 共有 3 条请求，其中 0 条取消。
  - 然而，id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。
  - 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。
  - 取消率为 (0 / 2) = 0.00
2013-10-03：
  - 共有 3 条请求，其中 1 条取消。
  - 然而，id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。
  - 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。
  - 取消率为 (1 / 2) = 0.50

二、解题思路

1. 首先，我们需要从 Trips 表中筛选出在 “2013-10-01” 至 “2013-10-03” 日期范围内的行程。
2. 然后，我们需要关联 Users 表，以确保我们只考虑那些未被禁止的用户（即 banned 列为 ‘No’）。
3. 我们需要计算每个日期的取消行程数和非取消行程数。
4. 计算取消率，公式为：取消行程数 / (取消行程数 + 非取消行程数)。
5. 最后，我们需要将取消率四舍五入到两位小数。

三、具体代码

SELECT 
    T.request_at AS Day,
    ROUND(
        COUNT(
            CASE 
                WHEN T.status IN ('cancelled_by_driver', 'cancelled_by_client') THEN 1 
                ELSE NULL 
            END
        ) / COUNT(T.id),
        2
    ) AS `Cancellation Rate`
FROM 
    Trips T
JOIN 
    Users U1 ON T.client_id = U1.users_id AND U1.banned = 'No'
JOIN 
    Users U2 ON T.driver_id = U2.users_id AND U2.banned = 'No'
WHERE 
    T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 
    T.request_at;

在这段代码中：

• 我们通过两次 JOIN 操作确保了 client_id 和 driver_id 都对应于未被禁止的用户。
• 我们使用 COUNT 函数与 CASE 表达式来分别计算每个日期的取消行程数和总行程数。
• 我们使用 ROUND 函数来将计算得到的取消率四舍五入到两位小数。
• 我们通过 WHERE 子句限制了日期范围。
• 最后，我们通过 GROUP BY 子句按日期分组，以便为每个日期计算取消率。

四、时间复杂度和空间复杂度

1. 时间复杂度

时间复杂度主要取决于查询中使用的操作类型以及这些操作如何与数据集的大小相关。

• JOIN 操作：

  • JOIN 操作的时间复杂度通常为 O(n * m)，其中 n 和 m 分别是参与连接的表中的行数。但是，如果连接条件是索引列（在本例中，users_id 应该是索引列），则时间复杂度可以降低到 O(n + m)，因为数据库可以利用索引进行更快的查找。

• WHERE 子句：

  • WHERE 子句用于过滤 Trips 表中的行，其时间复杂度取决于过滤条件。如果 request_at 列有索引，那么过滤操作的时间复杂度大约为 O(log n)，其中 n 是 Trips 表中的行数。

• GROUP BY 操作：

  • GROUP BY 操作的时间复杂度通常是 O(n)，因为它需要对所有行进行分组。

• 聚合函数（COUNT）：

  • 聚合函数通常与 GROUP BY 操作一起使用，其时间复杂度为 O(n)，因为需要遍历所有行来计算聚合值。

综上所述，整体查询的时间复杂度大致为 O(n + m)，假设 JOIN 操作利用了索引。

2. 空间复杂度

空间复杂度主要取决于查询执行过程中需要存储的数据量。

• JOIN 结果：

  • 如果 JOIN 操作的结果很大，那么它可能需要与输入表大小成比例的空间。因此，空间复杂度可能是 O(n * m)，但是在实际数据库中，通常会使用哈希连接或排序合并连接，这些方法可以优化空间使用。

• GROUP BY 操作：

  • GROUP BY 需要存储每个组的聚合结果，因此空间复杂度取决于不同组的数量。在本例中，我们按 request_at 分组，假设日期范围不大，空间复杂度将是 O(k)，其中 k 是不同日期的数量。

• 临时表和中间结果：

  • 查询执行过程中可能会产生临时表和中间结果，这些通常需要额外的空间。但是，数据库管理系统通常会优化这些操作以最小化空间使用。

综合来看，整体查询的空间复杂度大致为 O(n + m)，但实际使用中可能会因为数据库优化而低于这个值。

需要注意的是，实际的时间复杂度和空间复杂度会受到数据库的具体实现、索引、硬件配置、查询优化器等因素的影响，因此以上分析仅提供了一个理论上的估计。

五、总结知识点

• SELECT 语句：

  • 用于从数据库中检索数据。

• 列别名：

  • 使用 AS 关键字为查询结果中的列提供别名，如 T.request_at AS Day。

• 聚合函数：

  • COUNT 函数用于计算行数。

• CASE 表达式：

  • CASE 表达式用于条件逻辑，允许根据条件返回不同的值。

• ROUND 函数：

  • 用于将数值四舍五入到指定的小数位数。

• JOIN 操作：

  • JOIN 用于根据相关条件将行从两个或多个表合并在一起。
  • JOIN 有多种类型，如 INNER JOIN、LEFT JOIN、RIGHT JOIN 等。此处使用的是隐式 INNER JOIN。

• ON 子句：

  • 用于指定 JOIN 操作的连接条件。

• WHERE 子句：

  • 用于过滤结果集，仅返回满足特定条件的行。

• BETWEEN 操作符：

  • 用于指定一个范围，与 WHERE 子句一起使用来过滤日期或数值。

• GROUP BY 子句：

  • 用于将结果集分组，通常与聚合函数一起使用。

• IN 操作符：

  • 用于指定条件列表，匹配列表中的任何值。

• 字符串常量：

  • 在 SQL 中，字符串常量通常用单引号括起来。

• 枚举类型：

  • status 列被假设为 ENUM 类型，该类型限制列值只能是预定义的集合中的一个。

以上就是解决这个问题的详细步骤，希望能够为各位提供启发和帮助。