1645. Hopper 公司查询 II - 力扣（LeetCode）

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

1645. Hopper 公司查询 II - 力扣（LeetCode）

目标

输入

表 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

表 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
表 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
2 0
3 25
4 0
5 0
6 20
7 20
8 20
9 0
10 0
11 33.33
12 16.67

分析

编写解决方案以报告 2020 年每个月的工作驱动因素百分比（working_percentage），其中：注意：如果一个月内可用驾驶员的数量为零，我们认为 working_percentage 为 0。返回按 month 升序排列的结果表，其中 month 是月份的编号（一月是 1，二月是 2，等等）。将 working_percentage 四舍五入至小数点后两位。
														表 Rides:			表 AcceptedRides:				表 Drivers:		输出
														ride_id	user_id	requested_at	ride_id	driver_id	ride_distance	ride_duration	driver_id	join_date	month	working_percentage
														6	75	2019/12/9	10	10	63	38	10	2019/12/10	1	0
														1	54	2020/2/9	13	10	73	96	8	2020/1/13	2	0
10	63	2020/3/4		7	8	100	28	5	2020/2/16		3	25
19	39	2020/4/6		17	7	119	68	7	2020/3/8		4	0
3	41	2020/6/3		20	1	121	92	4	2020/5/17		5	0
13	52	2020/6/22		5	7	42	101	1	2020/10/24		6	20
7	69	2020/7/16		2	4	6	38	6	2021/1/5		7	20
17	70	2020/8/25		11	8	37	43				8	20
20	81	2020/11/2		15	8	108	82				9	0
5	57	2020/11/9		12	8	38	34				10	0
2	42	2020/12/9		14	1	90	74				11	33.33
11	68	2021/1/11									12	16.67
15	32	2021/1/17
12	11	2021/1/19
14	18	2021/1/27
递归月份		month	每月累计驾驶员数量		month	active_drivers	每月接受了订单的驾驶员数量	month	AR_CNT	求百分比		month	working_percentage
		1			1	2		3	1			1	0
		2			2	3		6	1			2	0
		3			3	4		7	1			3	25
		4			4	4		8	1			4	0
		5			5	5		11	2			5	0
		6			6	5		12	1			6	20
		7			7	5		2	0			7	20
		8			8	5		4	0			8	20
		9			9	5		1	0			9	0
		10			10	6		5	0			10	0
		11			11	6		9	0			11	33.33
		12			12	6		10	0			12	16.67

实现

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
    month,
    COUNT(IF(d.driver_id IS NOT NULL, 1, NULL)) +
    (SELECT COUNT(IF(driver_id IS NOT NULL, 1, NULL)) FROM Drivers WHERE YEAR(join_date) < 2020) active_drivers
FROM T1
LEFT JOIN Drivers D ON T1.month >= MONTH(join_date) AND YEAR(join_date) = 2020
GROUP BY month
ORDER BY month
;
# 计算每月有多少驾驶员接受了订单
WITH RECURSIVE T1 (month) AS (SELECT
                                  1
                              FROM dual
                              UNION ALL
                              SELECT
                                  month + 1
                              FROM T1
                              WHERE month < 12)
SELECT
month,COUNT(AR.driver_id)AR_CNT
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;
# 求百分比
WITH RECURSIVE T1 (month) AS (SELECT
                                  1
                              FROM dual
                              UNION ALL
                              SELECT
                                  month + 1
                              FROM T1
                              WHERE month < 12)
,T2 AS (
SELECT month,COUNT(AR.driver_id)AR_CNT
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
 )
 ,T3 AS (
 SELECT
    month,
    COUNT(IF(d.driver_id IS NOT NULL, 1, NULL)) +
    (SELECT COUNT(IF(driver_id IS NOT NULL, 1, NULL)) FROM Drivers WHERE YEAR(join_date) < 2020) active_drivers
FROM T1
LEFT JOIN Drivers D ON T1.month >= MONTH(join_date) AND YEAR(join_date) = 2020
GROUP BY month
ORDER BY month
  )SELECT T2.month,IF(ROUND (AR_CNT/active_drivers*100,2) IS NOT NULL ,ROUND(AR_CNT/active_drivers*100,2),0)working_percentage
  FROM T2,T3 WHERE T2.month=T3.month
  ORDER BY month

小结

递归,多表连接,分组聚合

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

1645. Hopper 公司查询 II - 力扣（LeetCode）

目标

输入

输出

分析

实现

小结

相关文章

赞助商

阅读排行