用户旅行的题目如下
The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
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 The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘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 Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
Day Cancellation Rate 2013-10-01 0.33 2013-10-02 0.00 2013-10-03 0.50
题目的意思: 求出十月一号到三号未被禁止的客户的取消订单的比例
解题思路
大体的思路:
- 事先将需要的用户先筛选出来
- 然后将表连接里面的数据在用on筛选出未被禁止的用户
- 然后在对这些数据进行计算
代码如下
select DISTINCT T.Request_at,round(count(case when T.status<>'completed' then T.status else null end)/count(1),2)
from (
select Request_at
,Driver_Id
,Client_Id
,status
from Trips where Request_at between '2013-10-01' and '2013-10-03') T
inner join (select Users_Id,Banned from Users) U
on (U.Banned = 'NO' and T.Client_Id = U.Users_Id)
group by 1;
总结: 对数据进行处理的时候,比较合适的方法就是先统计,然后在运算,降低需要数据的维度,减少表连接的时候不可控的因素.