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

-- 1、找到每个日子的近7日的订单完成数和取消订单数
with t as
(
    select date(order_time) dt,
    sum(count(mileage))over(order by date(order_time) range interval 6 day preceding) d7_yes,
    sum(count(1)-count(mileage))over(order by date(order_time) range interval 6 day preceding) d7_no
    from tb_get_car_order 
    group by dt
)
-- 2、将日期限定为国庆前三天且求指标
select dt,round(d7_yes/7,2),round(d7_no/7,2)
from t
where dt between '2021-10-01' and '2021-10-03'
order by dt

-- 1、找到每个日子的近7日的订单完成数和取消订单数

知识点：开窗函数