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

# 国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数

# 日均订单完成量和日均订单取消量    近7日   国庆头3天里

# 订单完成：mileage is not null

# 订单取消：mileage is nulll

# 先计算每天的订单完成量和每天的订单取消量
# 再用窗口函数计算每天的近七日日均订单完成量和日均订单取消量
select
    *
from(
    select
        date(co.order_time) as dt
        , round(avg(count(co.mileage)) over (order by date(co.order_time) range interval 6 day preceding), 2) as finish_num_7d
        , round(avg(count(case when co.mileage is null then 1 end)) over (order by date(co.order_time) range interval 6 day preceding), 2) as cancel_num_7d
    from tb_get_car_order as co 
    group by date(co.order_time)
    ) as t
where dt between '2021-10-01' and '2021-10-03'
order by dt