30 行程和用户

[astak16]

题目

题目链接：行程和用户

查询出 2013-10-01 和 2013-10-03 期间非禁止用户（乘客和司机都必须未被禁止）的取消率，非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。

取消率需要四舍五入保留两位小数

其中 client_id 和 driver_id 对应 users 表中的 users_id

create table trips (
	id int,
	client_id int, 
	driver_id int, 
	city_id int, 
	status char(255), 
	request_at date
);
insert into trips values(1, 1, 10, 1, 'completed', '2013-10-01');
insert into trips values(2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01');
insert into trips values(3, 3, 12, 6, 'completed', '2013-10-01');
insert into trips values(4, 4, 13, 6, 'cancelled_by_client', '2013-10-01');
insert into trips values(5, 1, 10, 1, 'completed', '2013-10-02');
insert into trips values(6, 2, 11, 6, 'completed', '2013-10-02');
insert into trips values(7, 3, 12, 6, 'completed', '2013-10-02');
insert into trips values(8, 2, 12, 12, 'completed', '2013-10-03');
insert into trips values(9, 3, 10, 12, 'completed', '2013-10-03');
insert into trips values(10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');

create table users (
	users_id int,
	banned char(255), 
	role char(255)
);
insert into users value(1, 'No', 'client');
insert into users value(2, 'Yes', 'client');
insert into users value(3, 'No', 'client');
insert into users value(4, 'No', 'client');
insert into users value(10, 'No', 'driver');
insert into users value(11, 'No', 'driver');
insert into users value(12, 'No', 'driver');
insert into users value(13, 'No', 'driver');

SQL：方法一

select 
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips
join users u1 on trips.client_id = u1.users_id and u1.banned = 'No'
join users u2 on trips.driver_id = u2.users_id and u2.banned = 'No'
where trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;

解析

错误的思路：

if (client_id = users_id or driver_id = users_id) and users_id没有被禁止 
...

只要 client_id 和 driver_id 只要有一个满足条件，就会被查出来

SQL 代码

select * from trips join users on (trips.client_id = users.users_id 
or trips.driver_id = users.users_id) and users.banned = 'No';

这样查出的结果没有排除掉 users_id = 2 的用户

正确的思路：

if(client_id = users_id and users_id没有禁止 
and driver_id = users_id and users_id没有被禁止)
...

client_id 和 driver_id 要分别和自己关联的 users_id 判断是否被禁止。

SQL 代码

select * from trips
join users u1 on trips.client_id = u1.users_id and u1.banned = 'No'
join users u2 on trips.driver_id = u2.users_id and u2.banned = 'No';

Tips

if 语法： if(expr1, expr2, expr3) 如何 expr1 为 true 则输出为 expr2 否则输出为 expr3

SQL：方法二

select
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips
left join (
	select users_id from users where banned = 'Yes'
) as a1 on trips.client_id = a1.users_id
left join(
	select users_id from users where banned = 'Yes'
) as a2 on trips.driver_id = a2.users_id
where a1.users_id is null and a2.users_id is null
and trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;

解析

找出被禁止的用户

select users_id from users where banned = 'Yes';

错误的思路：

select * from trips, (
	select users_id from users where banned = 'Yes'
) as a 
where trips.client_id != a.users_id and trips.driver_id != a.users_id

有两点错误：

1. 没有考虑 a 表为空时，最后的结果是为空的
2. 多个结果用 != 是查不出结果的

修改成这样就可以了

select * from trips where
trips.client_id in (select users_id from users where banned = 'No')
and
trips.driver_id in (select users_id from users where banned = 'No');

如果不使用 in 需要换一种思路

正确的思路：

select 
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips 
left join (
	select users_id from users where banned = 'Yes'
) as a1 on trips.client_id = a1.users_id
left join (
	select users_id from users where banned = 'Yes'
) as a2 on trips.driver_id = a2.users_id
where a1.users_id is null and a2.users_id is null
and trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;

查出被禁止的用户作为表 a 和 trips 表做两次左连：

• trips.client_id = a1.users_id
• trips.driver_id = a2.users_id

因为 trips 作为主体表，和 a 表左连，连接的结果是被禁止的用户行程数据

而我们要的结果是非禁止用户的形成数据， a 中 users_id 为 null 的就是我们要的数据

所以最后的筛选条件是 a1.users_id is null and a2.users_id is null

这种写法还可以反过来写，查出没被禁止的用户 a 和 trips 做内连，就不会有 null 存在

select 
	trips.request_at as 日期,
	round(sum(if(trips.status = 'completed', 0, 1)) / count(trips.status), 2) as 取消率
from trips 
join (
	select users_id from users where banned = 'No'
) as a1 on trips.client_id = a1.users_id
join (
	select users_id from users where banned = 'No'
) as a2 on trips.driver_id = a2.users_id
where trips.request_at between '2013-10-01' and '2013-10-03'
group by 日期;