1651. Hopper 公司查询 III - 力扣（LeetCode）

标签：INSERT INTO ride Hopper month 力扣 VALUES 1651 id

1651. Hopper 公司查询 III - 力扣（LeetCode）

目标

输入

表：AcceptedRides
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

表：Rides
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

表： 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

输出

输出
month average_ride_distance average_ride_duration
1 21 12.67
2 21 12.67
3 21 12.67
4 24.33 32
5 57.67 41.33
6 97.33 64
7 73 32
8 39.67 22.67
9 54.33 64.33
10 56.33 77

分析

编写一个解决方案，计算出从 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 ...）。
															表：AcceptedRides			表：Rides				表： Drivers		输出
															ride_id	user_id	requested_at	ride_id	driver_id	ride_distance	ride_duration	driver_id	join_date	month	average_ride_distance	average_ride_duration
															6	75	2019/12/9	10	10	63	38	10	2019/12/10	1	21	12.67
															1	54	2020/2/9	13	10	73	96	8	2020/1/13	2	21	12.67
															10	63	2020/3/4	7	8	100	28	5	2020/2/16	3	21	12.67
															19	39	2020/4/6	17	7	119	68	7	2020/3/8	4	24.33	32
3	41	2020/6/3		20	1	121	92		4	2020/5/17		5	57.67	41.33
13	52	2020/6/22		5	7	42	101		1	2020/10/24		6	97.33	64
7	69	2020/7/16		2	4	6	38		6	2021/1/5		7	73	32
17	70	2020/8/25		11	8	37	43					8	39.67	22.67
20	81	2020/11/2		15	8	108	82					9	54.33	64.33
5	57	2020/11/9		12	8	38	34					10	56.33	77
2	42	2020/12/9		14	1	90	74
11	68	2021/1/11
15	32	2021/1/17
12	11	2021/1/19
14	18	2021/1/27
递归月份		month	计算每月的距离和时间		month	ride_distance	ride_duration	计算每3个月的平均距离和时间		month	average_ride_distance	average_ride_duration
		1			1	0	0			1	21	12.67
		2			2	0	0			2	21	12.67
		3			3	63	38			3	21	12.67
		4			4	0	0			4	24.33	32
		5			5	0	0			5	57.67	41.33
		6			6	73	96			6	97.33	64
		7			7	100	28			7	73	32
		8			8	119	68			8	39.67	22.67
		9			9	0	0			9	54.33	64.33
		10			10	0	0			10	56.33	77
		11			11	163	193
		12			12	6	38

实现

CREATE TABLE IF NOT EXISTS Drivers
(
    driver_id INT,
    join_date DATE
);
CREATE TABLE IF NOT EXISTS Rides
(
    ride_id      INT,
    user_id      INT,
    requested_at DATE
);
CREATE TABLE IF NOT EXISTS AcceptedRides
(
    ride_id       INT,
    driver_id     INT,
    ride_distance INT,
    ride_duration INT
);
TRUNCATE TABLE Drivers;
INSERT INTO Drivers (driver_id, join_date)
VALUES ('10', '2019-12-10');
INSERT INTO Drivers (driver_id, join_date)
VALUES ('8', '2020-1-13');
INSERT INTO Drivers (driver_id, join_date)
VALUES ('5', '2020-2-16');
INSERT INTO Drivers (driver_id, join_date)
VALUES ('7', '2020-3-8');
INSERT INTO Drivers (driver_id, join_date)
VALUES ('4', '2020-5-17');
INSERT INTO Drivers (driver_id, join_date)
VALUES ('1', '2020-10-24');
INSERT INTO Drivers (driver_id, join_date)
VALUES ('6', '2021-1-5');
TRUNCATE TABLE Rides;
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('6', '75', '2019-12-9');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('1', '54', '2020-2-9');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('10', '63', '2020-3-4');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('19', '39', '2020-4-6');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('3', '41', '2020-6-3');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('13', '52', '2020-6-22');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('7', '69', '2020-7-16');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('17', '70', '2020-8-25');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('20', '81', '2020-11-2');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('5', '57', '2020-11-9');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('2', '42', '2020-12-9');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('11', '68', '2021-1-11');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('15', '32', '2021-1-17');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('12', '11', '2021-1-19');
INSERT INTO Rides (ride_id, user_id, requested_at)
VALUES ('14', '18', '2021-1-27');
TRUNCATE TABLE AcceptedRides;
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('10', '10', '63', '38');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('13', '10', '73', '96');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('7', '8', '100', '28');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('17', '7', '119', '68');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('20', '1', '121', '92');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('5', '7', '42', '101');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('2', '4', '6', '38');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('11', '8', '37', '43');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('15', '8', '108', '82');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('12', '8', '38', '34');
INSERT INTO AcceptedRides (ride_id, driver_id, ride_distance, ride_duration)
VALUES ('14', '1', '90', '74');
SELECT *
FROM Rides;
SELECT *
FROM AcceptedRides;
SELECT *
FROM Drivers;
# 递归月份
WITH RECURSIVE T1 (month) AS (SELECT
                                  1
                              FROM dual
                              UNION ALL
                              SELECT
                                  month + 1
                              FROM T1
                              WHERE month < 12)
SELECT *
FROM T1
;
# 计算每个月的距离和时间
WITH RECURSIVE T1 (month) AS (SELECT
                                  1
                              FROM dual
                              UNION ALL
                              SELECT
                                  month + 1
                              FROM T1
                              WHERE month < 12)
SELECT DISTINCT
    month,
    SUM(IFNULL(ride_distance, 0)) ride_distance,
    SUM(IFNULL(ride_duration, 0)) ride_duration
FROM T1
LEFT JOIN Rides R ON YEAR(requested_at) = 2020 AND month = MONTH(requested_at)
LEFT JOIN AcceptedRides AR ON R.ride_id = AR.ride_id
GROUP BY month
ORDER BY month
;
# 计算每3个月的平均距离和时间
WITH RECURSIVE
    T1 (month) AS (SELECT
                       1
                   FROM dual
                   UNION ALL
                   SELECT
                       month + 1
                   FROM T1
                   WHERE month < 12),
    T2 AS (SELECT DISTINCT
               month,
               SUM(IFNULL(ride_distance, 0)) ride_distance,
               SUM(IFNULL(ride_duration, 0)) ride_duration
           FROM T1
           LEFT JOIN Rides R ON YEAR(requested_at) = 2020 AND month = MONTH(requested_at)
           LEFT JOIN AcceptedRides AR ON R.ride_id = AR.ride_id
           GROUP BY month
           ORDER BY month)
,T3 AS (  SELECT month,ROUND( SUM(IFNULL(ride_distance, 0)) OVER (ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) /
    3,2) average_ride_distance,
    ROUND(SUM(IFNULL(ride_duration, 0)) OVER (ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) /
    3,2) average_ride_duration
           FROM T2 )
SELECT * FROM T3 WHERE month<11

小结

递归,窗口函数,多表连接

标签：INSERT,INTO,ride,Hopper,month,力扣,VALUES,1651,id
From： https://blog.csdn.net/2301_78665414/article/details/144955353

1651. Hopper 公司查询 III - 力扣（LeetCode）

目标

输入

输出

分析

实现

小结

相关文章

赞助商

阅读排行