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

with t as
(
    select 
    date(order_time) dt, 
    count(mileage) 'day_complete',
    count(*) - count(mileage) 'day_cancle'
    from tb_get_car_record r
    join tb_get_car_order o on r.order_id = o.order_id
    group by date(order_time)
)


select
    dt,
    round(sum_7day / 7, 2) 'finish_num_7d',
    round(cancel_7day /7, 2)'cancel_num7day'
from 
(
    select dt,
        sum(day_complete) over(ORDER BY dt asc range between INTERVAL 6 day preceding and current row)'sum_7day',
        sum(day_cancle) over(ORDER BY dt asc range between INTERVAL 6 day preceding and current row)'cancel_7day'
    from t
)a

where dt between '2021-10-01' and '2021-10-03'
order by dt asc;

• 先计算每天的使用量和取消量，然后使用窗口函数记录7天数据，最后最外层循环做日期筛选就好