公众号merlinsea
- leetcode链接地址
- 题目介绍
- Trips表
- id 是这张表的主键。这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | client_id | int | | driver_id | int | | city_id | int | | status | enum | | request_at | date | +-------------+----------+
- Users表
- users_id 是这张表的主键。这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
+-------------+----------+ | Column Name | Type | +-------------+----------+ | users_id | int | | banned | enum | | role | enum | +-------------+----------+
- 取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。写一段 SQL 语句查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
- 思路
- 第一步：将trips表和users表进行leftjoin找到所有司机和乘客是否是被禁止状态，通过client_id 和user_id进行关联，以及driver_id和user_id进行关联
select trips.*,cu.*,du.* from trips left join users cu on trips.client_id=cu.users_id left join users du on trips.driver_id = du.users_id
- 第二步：由于需要求出10.1号到10.3号每一天的取消率，因此需要将结果按照时间进行分组，并挑选出10.1号到10.3号的分组结果
# 计算非禁止用户的订单【10.1 - 10.3 每一天】 select trips.request_at,count(*) cnt from trips left join users cu on trips.client_id=cu.users_id left join users du on trips.driver_id = du.users_id where cu.banned = 'No' and du.banned = 'No' group by request_at having request_at>='2013-10-01' and request_at<='2013-10-03' | request_at | cnt | | ---------- | --- | | 2013-10-01 | 3 | | 2013-10-02 | 2 | | 2013-10-03 | 2 |
# 被司机或乘客取消的非禁止用户生成的订单数量 select trips.request_at,count(*) cnt from trips left join users cu on trips.client_id=cu.users_id left join users du on trips.driver_id = du.users_id where cu.banned = 'No' and du.banned = 'No' and (trips.status = 'cancelled_by_client' or trips.status='cancelled_by_driver') group by request_at having request_at>='2013-10-01' and request_at<='2013-10-03' | request_at | cnt | | ---------- | --- | | 2013-10-01 | 1 | | 2013-10-03 | 1 |
- sql实现
select tmp1.request_at 'Day', round(ifnull(tmp2.cnt,0) / tmp1.cnt,2) 'Cancellation Rate' from ( #分母 select trips.request_at,count(*) cnt from trips left join users cu on trips.client_id=cu.users_id left join users du on trips.driver_id = du.users_id where cu.banned = 'No' and du.banned = 'No' group by request_at having request_at>='2013-10-01' and request_at<='2013-10-03' ) tmp1 left join( #分子 select trips.request_at,count(*) cnt from trips left join users cu on trips.client_id=cu.users_id left join users du on trips.driver_id = du.users_id where cu.banned = 'No' and du.banned = 'No' and (trips.status = 'cancelled_by_client' or trips.status='cancelled_by_driver') group by request_at having request_at>='2013-10-01' and request_at<='2013-10-03' ) tmp2 on tmp1.request_at = tmp2.request_at
