提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
关于sql的执行顺序,https://blog.csdn.net/weixin_44141495/article/details/108744720/ 这篇文章说的很好
一、力扣262. 行程和用户
https://leetcode.cn/problems/trips-and-users/description/
二、问题分析
题目的要求是计算每天的取消率,既然是每天,那肯定要对日期进行分组了。取消率是2个聚合值的比例,所以用group by不用partition by。
其他限制条件包括:
1、乘客和司机未被禁止。这个限制对取消的订单和所有订单都是同等的,所以可以在总体上先处理(无需区分是不是取消订单)。处理后的取消率就等于取消订单数量/订单总数,简单一些;
2、时间在"2013-10-01" 至 "2013-10-03"之间;
3、取消率 Cancellation Rate 需要四舍五入保留 两位小数。
三、解决步骤
1、基础整表
为满足上述所有的计算条件,需要一个包括日期、顾客id、顾客禁止状态、司机id、司机禁止状态、以及订单状态(是非被取消)等维度的整表。
1.select request_at ,client_id,Client.banned client_banned,driver_id,
Driver.banned driver_banned,status
2.from Trips
3.left join Users Client on client_id = Client.users_id
4.left join Users Driver on driver_id = Driver.users_id
5.where Client.banned ="No" and Driver.banned ="No" and request_at between DATE("2013-10-01") and DATE("2013-10-03")
上述代码执行顺序为:
1、from join:执行2和3,Trips 表和Users Client表通过client_id和users_id左连接,相当于为Trips 表根据顾客ID映射,增加了Client.banned列;
2、继续join:执行4,第一步的虚拟表和Users Client表通过driver_id和users_id左连接,相当于为虚拟表根据司机ID映射,增加了Driver.banned列;
3、where:执行5,通过顾客和司机的禁止状态以及时间生成满足限制条件1和2的结果;
4、select:执行1,选取计算需要的维度。
2、分组计算
每天取消率的计算需要两个聚合值,一个是每天取消的订单数量,一个是每天订单总数量。
基于第1步的基础表,做两处修改:一是增加group by;二是修改select,选择两个聚合值。
代码如下(示例):
1.SELECT request_at, COUNT(status) AS count_all,
count(CASE WHEN status = 'cancelled_by_client' OR status = 'cancelled_by_driver' THEN 1 ELSE NULL END) AS count_cancelled
2.from Trips
3.left join Users Client on client_id = Client.users_id
4.left join Users Driver on driver_id = Driver.users_id
5.where Client.banned ="No" and Driver.banned ="No" and request_at between DATE("2013-10-01") and DATE("2013-10-03")
6.group by request_at
2、3、4、5步执行顺序与基础表一致,然后执行6,对时间进行分组(若有筛选条件,下一步可填写having);
接着执行select,选择两个聚合值,每天取消的订单数量需要判断status状态。
3、计算每天的取消率
select
request_at "Day",
ROUND(a.count_cancelled / a.count_all , 2 ) "Cancellation Rate"
from (
SELECT request_at,
COUNT(status) AS count_all,
count(CASE WHEN status = 'cancelled_by_client' OR status = 'cancelled_by_driver' THEN 1 ELSE NULL END) AS count_cancelled
from Trips
left join Users Client on client_id = Client.users_id
left join Users Driver on driver_id = Driver.users_id
where Client.banned ="No" and Driver.banned ="No" and request_at between DATE("2013-10-01") and DATE("2013-10-03")
group by request_at
) a
把分组计算输出的虚拟表命名为a,然后在a上select并计算取消率。
Day | Cancellation Rate |
---|---|
2013-10-01 | 0.33 |
2013-10-02 | 0 |
2013-10-03 | 0.5 |
输出正确,测试通过。
四、sql执行顺序总结
关于sql的执行顺序,https://blog.csdn.net/weixin_44141495/article/details/108744720/ 这篇文字说的很清楚,这里搬几个图(如侵权请联系删除)。
1、标准查询语句
2、执行顺序
3、示例