Question

1645. Hopper 公司查询 II

English Version

题目描述

表: Drivers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| join_date   | date    |
+-------------+---------+
driver_id 是该表具有唯一值的列。
该表的每一行均包含驾驶员的ID以及他们加入 Hopper 公司的日期。

 

表: Rides

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| ride_id      | int     |
| user_id      | int     |
| requested_at | date    |
+--------------+---------+
ride_id 是该表具有唯一值的列。
该表的每一行均包含行程 ID(ride_id)，用户 ID(user_id) 以及该行程的日期 (requested_at)。
该表中可能有一些不被接受的乘车请求。

 

表: AcceptedRides

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ride_id       | int     |
| driver_id     | int     |
| ride_distance | int     |
| ride_duration | int     |
+---------------+---------+
ride_id 是该表具有唯一值的列。
该表的每一行都包含已接受的行程信息。
表中的行程信息都在 "Rides" 表中存在。

 

编写解决方案以报告 2020 年每个月的工作驱动因素 百分比（working_percentage），其中：

 

注意：如果一个月内可用驾驶员的数量为零，我们认为 working_percentage 为 0。

返回按 month 升序 排列的结果表，其中 month 是月份的编号（一月是 1，二月是 2，等等）。将 working_percentage 四舍五入至 小数点后两位。

结果格式如下例所示。

 

示例 1:

输入：
表 Drivers:
+-----------+------------+
| driver_id | join_date  |
+-----------+------------+
| 10        | 2019-12-10 |
| 8         | 2020-1-13  |
| 5         | 2020-2-16  |
| 7         | 2020-3-8   |
| 4         | 2020-5-17  |
| 1         | 2020-10-24 |
| 6         | 2021-1-5   |
+-----------+------------+

表 Rides:
+---------+---------+--------------+
| ride_id | user_id | requested_at |
+---------+---------+--------------+
| 6       | 75      | 2019-12-9    |
| 1       | 54      | 2020-2-9     |
| 10      | 63      | 2020-3-4     |
| 19      | 39      | 2020-4-6     |
| 3       | 41      | 2020-6-3     |
| 13      | 52      | 2020-6-22    |
| 7       | 69      | 2020-7-16    |
| 17      | 70      | 2020-8-25    |
| 20      | 81      | 2020-11-2    |
| 5       | 57      | 2020-11-9    |
| 2       | 42      | 2020-12-9    |
| 11      | 68      | 2021-1-11    |
| 15      | 32      | 2021-1-17    |
| 12      | 11      | 2021-1-19    |
| 14      | 18      | 2021-1-27    |
+---------+---------+--------------+

表 AcceptedRides:
+---------+-----------+---------------+---------------+
| ride_id | driver_id | ride_distance | ride_duration |
+---------+-----------+---------------+---------------+
| 10      | 10        | 63            | 38            |
| 13      | 10        | 73            | 96            |
| 7       | 8         | 100           | 28            |
| 17      | 7         | 119           | 68            |
| 20      | 1         | 121           | 92            |
| 5       | 7         | 42            | 101           |
| 2       | 4         | 6             | 38            |
| 11      | 8         | 37            | 43            |
| 15      | 8         | 108           | 82            |
| 12      | 8         | 38            | 34            |
| 14      | 1         | 90            | 74            |
+---------+-----------+---------------+---------------+
输出：
+-------+--------------------+
| month | working_percentage |
+-------+--------------------+
| 1     | 0.00               |
| 2     | 0.00               |
| 3     | 25.00              |
| 4     | 0.00               |
| 5     | 0.00               |
| 6     | 20.00              |
| 7     | 20.00              |
| 8     | 20.00              |
| 9     | 0.00               |
| 10    | 0.00               |
| 11    | 33.33              |
| 12    | 16.67              |
+-------+--------------------+
解释：
截至 1 月底 --> 2 个活跃的驾驶员 (10, 8)，无被接受的行程。百分比是0%。
截至 2 月底 --> 3 个活跃的驾驶员 (10, 8, 5)，无被接受的行程。百分比是0%。
截至 3 月底 --> 4 个活跃的驾驶员 (10, 8, 5, 7)，1 个被接受的行程 (10)。百分比是 (1 / 4) * 100 = 25%。
截至 4 月底 --> 4 个活跃的驾驶员 (10, 8, 5, 7)，无被接受的行程。百分比是 0%。
截至 5 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，无被接受的行程。百分比是 0%。
截至 6 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，1 个被接受的行程 (10)。 百分比是 (1 / 5) * 100 = 20%。
截至 7 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，1 个被接受的行程 (8)。百分比是 (1 / 5) * 100 = 20%。
截至 8 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，1 个被接受的行程 (7)。百分比是 (1 / 5) * 100 = 20%。
截至 9 月底 --> 5 个活跃的驾驶员 (10, 8, 5, 7, 4)，无被接受的行程。百分比是 0%。
截至 10 月底 --> 6 个活跃的驾驶员 (10, 8, 5, 7, 4, 1) 无被接受的行程。百分比是 0%。
截至 11 月底 --> 6 个活跃的驾驶员 (10, 8, 5, 7, 4, 1)，2 个被接受的行程 (1, 7)。百分比是 (2 / 6) * 100 = 33.33%。
截至 12 月底 --> 6 个活跃的驾驶员 (10, 8, 5, 7, 4, 1)，1 个被接受的行程 (4)。百分比是 (1 / 6) * 100 = 16.67%。

解法

方法一：递归 + 左连接 + 分组

我们可以使用递归的方法生成 $1 \sim 12$ 月的数据，记录在 Month 表中。

接下来，我们用 Month 表与 Drivers 表进行左连接，连接的条件是 year(d.join_date) < 2020 or (year(d.join_date) = 2020 and month(d.join_date) <= month)，这样就可以得到每个月的活跃司机数。

然后，我们再用 Rides 表与 AcceptedRides 表进行内连接，连接的条件是 ride_id 相等，并且我们只查出 year(requested_at) = 2020 的数据，这样就可以得到 $2020$ 年被接受的所有行程。

最后，我们将上面两个表进行左连接，连接的条件是 month 相等、driver_id 相等，并且 join_date 小于等于 requested_at，这样就可以得到每个月被接受的行程数，按月份进行分组，就可以得到每个月的活跃司机数和被接受的行程数，从而计算出每个月的接单率。

# Write your MySQL query statement below
WITH RECURSIVE
  Month AS (
    SELECT 1 AS month
    UNION
    SELECT month + 1
    FROM Month
    WHERE month < 12
  ),
  S AS (
    SELECT month, driver_id, join_date
    FROM
      Month AS m
      LEFT JOIN Drivers AS d
        ON YEAR(d.join_date) < 2020
        OR (YEAR(d.join_date) = 2020 AND MONTH(d.join_date) <= month)
  ),
  T AS (
    SELECT driver_id, requested_at
    FROM
      Rides
      JOIN AcceptedRides USING (ride_id)
    WHERE YEAR(requested_at) = 2020
  )
SELECT
  month,
  IFNULL(
    ROUND(COUNT(DISTINCT t.driver_id) * 100 / COUNT(DISTINCT s.driver_id), 2),
    0
  ) AS working_percentage
FROM
  S AS s
  LEFT JOIN T AS t
    ON s.driver_id = t.driver_id
    AND s.join_date <= t.requested_at
    AND s.month = MONTH(t.requested_at)
GROUP BY 1;