描述:262. 行程和用户 - 力扣(LeetCode)
取消率 的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
编写解决方案找出
"2013-10-01"
至"2013-10-03"
期间非禁止用户(乘客和司机都必须未被禁止)的取消率。非禁止用户即 banned 为 No 的用户,禁止用户即 banned 为 Yes 的用户。其中取消率Cancellation Rate
需要四舍五入保留 两位小数 。返回结果表中的数据 无顺序要求 。
输入: Trips 表: +----+-----------+-----------+---------+---------------------+------------+ | id | client_id | driver_id | city_id | status | request_at | +----+-----------+-----------+---------+---------------------+------------+ | 1 | 1 | 10 | 1 | completed | 2013-10-01 | | 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 | | 3 | 3 | 12 | 6 | completed | 2013-10-01 | | 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | | 5 | 1 | 10 | 1 | completed | 2013-10-02 | | 6 | 2 | 11 | 6 | completed | 2013-10-02 | | 7 | 3 | 12 | 6 | completed | 2013-10-02 | | 8 | 2 | 12 | 12 | completed | 2013-10-03 | | 9 | 3 | 10 | 12 | completed | 2013-10-03 | | 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | +----+-----------+-----------+---------+---------------------+------------+ Users 表: +----------+--------+--------+ | users_id | banned | role | +----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | +----------+--------+--------+ 输出: +------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+解释: 2013-10-01:- 共有 4 条请求,其中 2 条取消。- 然而,id=2 的请求是由禁止用户(user_id=2)发出的,所以计算时应当忽略它。- 因此,总共有 3 条非禁止请求参与计算,其中 1 条取消。- 取消率为 (1 / 3) = 0.33 2013-10-02:- 共有 3 条请求,其中 0 条取消。- 然而,id=6 的请求是由禁止用户发出的,所以计算时应当忽略它。- 因此,总共有 2 条非禁止请求参与计算,其中 0 条取消。- 取消率为 (0 / 2) = 0.00 2013-10-03:- 共有 3 条请求,其中 1 条取消。- 然而,id=8 的请求是由禁止用户发出的,所以计算时应当忽略它。- 因此,总共有 2 条非禁止请求参与计算,其中 1 条取消。- 取消率为 (1 / 2) = 0.50
数据准备:
Create table If Not Exists Trips (id int, client_id int, driver_id int, city_id int, status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), request_at varchar(50))
Create table If Not Exists Users (users_id int, banned varchar(50), role ENUM('client', 'driver', 'partner'))
Truncate table Trips
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03')
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03')
Truncate table Users
insert into Users (users_id, banned, role) values ('1', 'No', 'client')
insert into Users (users_id, banned, role) values ('2', 'Yes', 'client')
insert into Users (users_id, banned, role) values ('3', 'No', 'client')
insert into Users (users_id, banned, role) values ('4', 'No', 'client')
insert into Users (users_id, banned, role) values ('10', 'No', 'driver')
insert into Users (users_id, banned, role) values ('11', 'No', 'driver')
insert into Users (users_id, banned, role) values ('12', 'No', 'driver')
insert into Users (users_id, banned, role) values ('13', 'No', 'driver')
分析:
①观察trip表需要client_id,driver_id,而这两个id在users表中,不妨分解一下users表
分解为只有client_id 数据的表t1 和只有driver_id数据的表t2
select * from users where role = 'client'select * from users where role = 'driver'②连接三张表,并进行相应的筛选,找到clent和driver的banned都为no的,以及相应日期的数据
select request_at,t1.users_id uid,t1.banned t1b,t2.users_id did,t2.banned t2b,status from (select * from users where role = 'client') t1,(select * from users where role = 'driver') t2,trips where Trips.client_id = t1.users_idand Trips.driver_id = t2.users_idand request_at between '2013-10-01' and '2013-10-03'and t1.banned = 'no'and t2.banned = 'no'③然后通过对日期分类求出取消率
select request_at,round((count(if(status !='completed',1,null))/(count(1)) ),2)as 'Cancellation Rate'from t1 group by request_at;
代码:
with t1 as (
select request_at,t1.users_id uid,t1.banned t1b,t2.users_id did,t2.banned t2b,status
from (select * from users where role = 'client') t1,(select * from users where role = 'driver') t2,trips
where Trips.client_id = t1.users_idand Trips.driver_id = t2.users_idand request_at between '2013-10-01' and '2013-10-03'and t1.banned = 'no'and t2.banned = 'no')
select request_at,round((count(if(status !='completed',1,null))/(count(1)) ),2)as 'Cancellation Rate'from t1
group by request_at;
总结:
分解users表之后再进行连接会使题目迎刃而解