SQL - 5

8.行程和用户

需求:写一段 SQL 语句查出 2019年10月1日 至 2019年10月3日 期间非禁止用户的取消率。基于上表,你的 SQL 语句应返回如下结果,取消率(Cancellation Rate)保留两位小数。取消率的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

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

Id Client_Id Driver_Id City_Id Status Request_at
1 1 10 1 completed 2019-10-01
2 2 11 1 cancelled_by_driver 2019-10-01
3 3 12 6 completed 2019-10-01
4 4 13 6 cancelled_by_client 2019-10-01
5 1 10 1 completed 2019-10-02
6 2 11 6 completed 2019-10-02
7 3 12 6 completed 2019-10-02
8 2 12 12 completed 2019-10-03
9 3 10 12 completed 2019-10-03
10 4 13 12 cancelled_by_driver 2019-10-03

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

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
2019-10-01 0.33
2019-10-02 0.00
2019-10-03 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', '2019-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (2, 2, 11, 1, 'cancelled_by_driver', '2019-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (3, 3, 12, 6, 'completed', '2019-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (4, 4, 13, 6, 'cancelled_by_client', '2019-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (5, 1, 10, 1, 'completed', '2019-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (6, 2, 11, 6, 'completed', '2019-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (7, 3, 12, 6, 'completed', '2019-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (8, 2, 12, 12, 'completed', '2019-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (9, 3, 10, 12, 'completed', '2019-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (10, 4, 13, 12, 'cancelled_by_driver', '2019-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');

方法1:

select 
	t.request_at as 'Day',
	Round(sum(if(t.status = 'complete',0,1))/count(t.status))
from
	Trips as t
join 
	Users as u
on
	t.cilent_id = u.user.id 
and 
	u.Banned = 'No'
where 
	t.request_at between '2019-10-01' AND '2019-10-03'
group by
	t.request_at;
作者:yuexiuping
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
posted @ 2021-06-15 22:49  yuexiuping  阅读(20)  评论(0编辑  收藏  举报