【LeetCode】262.行程和用户（难度：困难）

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

DROp TABLE if EXISTS Trips;
CREATE TABLE Trips
(Id INT,
Client_Id INT,
Driver_Id INT,
City_Id INT,
Status VARCHAr(30),
Request_at DATE,
PRIMARY KEY (Id));

INSERT INTO Trips VALUES (1, 1, 10, 1, 'completed', '2013-10-1');
INSERT INTO Trips VALUES (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-1');
INSERT INTO Trips VALUES (3, 3, 12, 6, 'completed', '2013-10-1');
INSERT INTO Trips VALUES (4, 4, 13, 6, 'cancelled_by_client', '2013-10-1');
INSERT INTO Trips VALUES (5, 1, 10, 1, 'completed', '2013-10-2');
INSERT INTO Trips VALUES (6, 2, 11, 6, 'completed', '2013-10-2');
INSERT INTO Trips VALUES (7, 3, 12, 6, 'completed', '2013-10-2');
INSERT INTO Trips VALUES (8, 2, 12, 12, 'completed', '2013-10-3');
INSERT INTO Trips VALUES (9, 3, 10, 12, 'completed', '2013-10-3');
INSERT INTO Trips VALUES (10, 4, 13, 12, 'cancelled_by_driver', '2013-10-3');

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

DROP TABLE if EXISTS Users ;
CREATE TABLE Users 
(Users_Id  INT,
 Banned    VARCHAr(30),
 Role      VARCHAr(30),
PRIMARY KEY (Users_Id));

INSERT INTO Users VALUES (1,    'No',  'client');
INSERT INTO Users VALUES (2,    'Yes', 'client');
INSERT INTO Users VALUES (3,    'No',  'client');
INSERT INTO Users VALUES (4,    'No',  'client');
INSERT INTO Users VALUES (10,   'No',  'driver');
INSERT INTO Users VALUES (11,   'No',  'driver');
INSERT INTO Users VALUES (12,   'No',  'driver');
INSERT INTO Users VALUES (13,   'No',  'driver');

写一段 SQL 语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

SELECT
	temp4.request_at,
	# 当日行程取消单数/当日行程总单数
	round( ifnull( count1 / count_2, 0 ), 2 ) AS Cancellation_Rate 
FROM
	(
	# 取2013-10-01至2013-10-03用户未禁止每日的行程总单数
	SELECt
		request_at,
		count( 1 ) AS count_2 
	FROM
		( SELECt * FROM trips WHERe Request_at BETWEEN "2013-10-01" AND "2013-10-03" ) temp1
		RIGHT JOIN ( SELECt users_id, role FROM users WHERe banned != 'Yes' ) temp2 ON temp1.client_id = temp2.users_id 
	WHERe
		id IS NOT NULL 
	GROUP BY
		request_at 
		# 至此
	) temp4
	LEFT JOIN (
	# 取2013-10-01至2013-10-03用户未禁止每日的行程取消单数
	SELECt
		count( 1 ) count1,
		request_at 
	FROM
		( SELECt * FROM trips WHERe Request_at BETWEEN "2013-10-01" AND "2013-10-03" ) temp1
		RIGHT JOIN ( SELECt users_id, role FROM users WHERe banned != 'Yes' ) temp2 ON temp1.client_id = temp2.users_id 
	WHERe
		id IS NOT NULL 
		AND STATUS IN ( "cancelled_by_driver", "cancelled_by_client" ) 
	GROUP BY
	request_at 
	# 至此
	) temp5 ON temp4.request_at = temp5.request_at

SELECt
	t.Request_at DAY,
	round( sum( CASE WHEN t.STATUS LIKE 'cancelled%' THEN 1 ELSE 0 END )/ count(*), 2 ) AS 'Cancellation Rate' 
FROM
	Trips t
	INNER JOIN Users u ON u.Users_Id = t.Client_Id 
	AND u.Banned = 'No' 
WHERe
	t.Request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	t.Request_at;

Kettle实现循环功能！！！
https://blog.csdn.net/weixin_43932609/article/details/119608497
ETL工具kettle的计算方式
https://blog.csdn.net/weixin_43932609/article/details/110371110
Kettle工具中Rest client组件的用法！！
https://blog.csdn.net/weixin_43932609/article/details/109988783
ETL工具kettle的Excel合并操作
https://blog.csdn.net/weixin_43932609/article/details/109326043
ETL工具kettle的插入更新操作
https://blog.csdn.net/weixin_43932609/article/details/109065366
ETL工具Kettle的发送邮件功能：
https://blog.csdn.net/weixin_43932609/article/details/108766325
ETL工具Kettle的列转行组件：
https://blog.csdn.net/weixin_43932609/article/details/108795244
ETL工具Kettle性能优化：
https://blog.csdn.net/weixin_43932609/article/details/108749304

=========================================================

人生得意须尽欢，莫使金樽空对月！
__一个热爱说唱的程序员。
今日份推荐音乐：王以太《贰麻贰麻》

=========================================================