Leetcode 1651. Hopper 公司查询 III

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 年 1 月至 3 月 至 2020 年 10 月至 12 月 的每三个月窗口的 average_ride_distance 和 average_ride_duration 。并将 average_ride_distance 和 average_ride_duration 四舍五入至 小数点后两位 。

通过将三个月的总 ride_distance 相加并除以 3 来计算 average_ride_distance 。average_ride_duration 的计算方法与此类似。

返回按 month 升序排列的结果表，其中 month 是起始月份的编号（一月为 1，二月为 2 ...）。

1.2.题目地址

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

2.解题方法

2.1.解题思路

窗口函数

2.2.解题步骤

第一步，统计各个月份的总ride_distance和ride_duration

第二步，使用窗口函数计算当前和后两行的ride_distance和ride_duration的平均数

3.解题代码

sql代码

# Write your MySQL query statement below
# 知识点：窗口函数OVER (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)获取当前行和后两行的统计量，窗口函数OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)获取前两行和当前行的统计量
WITH RECURSIVE months AS (
    SELECT 1 month 
        UNION
    SELECT month + 1 AS month FROM months WHERE month <= 11
), T AS (
    # 第一步，统计各个月份的总ride_distance和ride_duration
    SELECT 
        MONTH(requested_at) AS month, 
        SUM(ride_distance) AS rd1, 
        SUM(ride_duration) AS rd2
    FROM 
        Rides LEFT JOIN AcceptedRides USING (ride_id)
    WHERE driver_id IS NOT NULL AND YEAR(requested_at) = 2020
    GROUP BY month
)
# 第二步，使用窗口函数计算当前和后两行的ride_distance和ride_duration的平均数
SELECT 
    month, 
    ROUND((SUM(rd1) OVER (ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)) / 3, 2) AS average_ride_distance, 
    ROUND((SUM(rd2) OVER (ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)) / 3, 2) AS average_ride_duration
FROM (
    SELECT 
        month, 
        IFNULL(rd1, 0) AS rd1, 
        IFNULL(rd2, 0) AS rd2
    FROM 
        months LEFT JOIN T USING (month)
) AS t1
LIMIT 10

4.执行结果