Leetcode 1645. Hopper 公司查询 II

1.题目基本信息

1.1.题目描述

表: 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.2.题目地址

https://leetcode.cn/problems/hopper-company-queries-ii/description/

2.解题方法

2.1.解题思路

请参考步骤

2.2.解题步骤

第一步，通过递归构建月份表months

第二步，根据各个"%Y%m"的大小关系将months和Drivers表格连接起来，并根据month分组聚合统计数量，获取每个月在职司机数active_drivers

第三步，统计每个月份的至少接过一单的司机数量

第四步，将T和S表根据month连接起来，并计算working_percentage

3.解题代码

sql代码

# Write your MySQL query statement below

# 第一步，根据【Hopper 公司查询 II】中方法获取每个月的在职的司机数

# 第一步，通过递归构建月份表months
WITH RECURSIVE months AS (
    SELECT 1 AS month 
        UNION 
    SELECT month + 1 AS month FROM months WHERE month <= 11
), T AS (
    # 第二步，根据各个"%Y%m"的大小关系将months和Drivers表格连接起来，并根据month分组聚合统计数量，获取每个月在职司机数active_drivers
    SELECT 
        months.month, 
        COUNT(join_date) AS active_drivers
    FROM 
        months 
            LEFT JOIN 
        Drivers 
        ON CONCAT('2020', LPAD(months.month, 2, '0')) >= DATE_FORMAT(Drivers.join_date, '%Y%m')
    GROUP BY months.month
), S AS (
    # 第三步，统计每个月份的至少接过一单的司机数量
    SELECT 
        MONTH(requested_at) AS month, 
        COUNT(DISTINCT driver_id) AS cnt2
    FROM 
        Rides LEFT JOIN AcceptedRides USING (ride_id)
    WHERE driver_id IS NOT NULL AND YEAR(requested_at) = 2020
    GROUP BY month
)
# 第四步，将T和S表根据month连接起来，并计算working_percentage 
SELECT 
    month, 
    IFNULL(ROUND(100 * cnt2 / active_drivers, 2), 0) AS working_percentage 
FROM 
    T LEFT JOIN S USING (month)

4.执行结果