Leetcode SQL刷题 262题

---

题目描述

行程和用户
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。

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

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

基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

一、分析思路

1.第一种方法
首先确定非禁止用户，用户分为两类，司机和乘客，这两类用户都是满足非禁止条件的，所以需要t 表示出行表，u1表示司机表，u2表示乘客表

SELECT *
FROM Trips AS t INNER JOIN Users AS u1 ON (t.Driver_Id=u1.Users_Id AND u1.Banned='NO')
                INNER JOIN Users AS u2 ON (t.Client_Id=u2.Users_Id AND u2.Banned='NO')

第二步对用户进行筛选出行日期介于2013年10月1日 至 2013年10月3日
注意：比较日期，日期一定要写成‘2013-10-01’，‘2013-10-1’这个是不对的

SELECT *
FROM Trips AS t INNER JOIN Users AS u1 ON (t.Driver_Id=u1.Users_Id AND u1.Banned='NO')
                INNER JOIN Users AS u2 ON (t.Client_Id=u2.Users_Id AND u2.Banned='NO')
WHERE  t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'

第三步确定取消率
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
被司机或乘客取消的非禁止用户生成的订单数量：
两种方式：

SUM(IF (t.Status='completed', 0,1))

SUM(CASE WHEN t.Status='cancelled_by_client' OR  t.Status='cancelled_by_driver' THEN 1
                      ELSE 0 END )

非禁止用户生成的订单总数:

COUNT(Status)

取消率（Cancellation Rate）保留两位小数:

ROUND(SUM(IF (t.Status='completed', 0,1))/COUNT(Status),2)

第四步，结合前面的内容，按照日期进行分组

SELECT t.Request_at AS Day,ROUND(IF (t.Status='completed', 0,1) )/COUNT(Status),2) AS 'Cancellation Rate'
FROM Trips AS t INNER JOIN Users AS u1 ON (t.Driver_Id=u1.Users_Id AND u1.Banned='NO')
                INNER JOIN Users AS u2 ON (t.Client_Id=u2.Users_Id AND u2.Banned='NO')
WHERE  t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY  t.Request_at

2.第二种方法
找出被禁止的用户后，不再连接行程表和用户表，直接从行程表中排除掉被被禁止用户的行程记录。

被禁止的用户用子查询：

(
	SELECT Users_id
	FROM Users
	WHERE banned = 'Yes'
) AS A

行程表中 client_id 和 driver_id 都在此子查询结果中的行要剔除掉。

SELECT *
FROM Trips AS t
WHERE 
t.Client_Id NOT IN (
	SELECT Users_id
	FROM Users
	WHERE banned = 'Yes'
)
AND
t.Driver_Id NOT IN (
	SELECT Users_id
	FROM Users
	WHERE banned = 'Yes'
)

后面的思路与第一种方法相同

SELECT t.Request_at AS Day,ROUND(IF (t.Status='completed', 0,1) )/COUNT(Status),2) AS 'Cancellation Rate'
FROM Trips AS t
WHERE 
t.Client_Id NOT IN (
	SELECT Users_id
	FROM Users
	WHERE banned = 'Yes'
)
AND
t.Driver_Id NOT IN (
	SELECT Users_id
	FROM Users
	WHERE banned = 'Yes'
)
AND t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY  t.Request_at