题解 | #国庆期间近7日日均取消订单量#
国庆期间近7日日均取消订单量
https://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703
# 问题：请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。
# 问题分解：
# 分三天分别计算指标
select
date('20211001') as dt,
round(count(start_time) / 7, 2) as finish_num_7d,
round(sum(if(start_time is null, 1, 0)) / 7, 2) as cancel_num_7d
from tb_get_car_order
where order_time between 20210925 and 20211002
# 问题：请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。
# 问题分解：
# 分三天分别计算指标
select
date('20211001') as dt,
round(count(start_time) / 7, 2) as finish_num_7d,
round(sum(if(start_time is null, 1, 0)) / 7, 2) as cancel_num_7d
from tb_get_car_order
where order_time between 20210925 and 20211002
union all
select
date('20211002') as dt,
round(count(start_time) / 7, 2) as finish_num_7d,
round(sum(if(start_time is null, 1, 0)) / 7, 2) as cancel_num_7d
from tb_get_car_order
where order_time between 20210926 and 20211003
union all
select
date('20211003') as dt,
round(count(start_time) / 7, 2) as finish_num_7d,
round(sum(if(start_time is null, 1, 0)) / 7, 2) as cancel_num_7d
from tb_get_car_order
where order_time between 20210927 and 20211004
