题解 | #国庆期间近7日日均取消订单量#

# 问题：请统计国庆头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