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

select
t1.pday,
round(
t1.pwc,2),
round(
t1.pqx,2)
from(
select
t.pday,
avg(t.wc)over(order by t.pday range interval 6 day PRECEDING) as pwc,#求每日完成单前7天的平均数
avg(t.qx)over(order by t.pday range interval 6 day PRECEDING) as pqx #求每日未完成单前7天平均数
from(
select
date(tr.order_time) as pday,#每日的订单日期
count(case when tr.start_time is not null then  tr.order_id end) as wc,#求每日的完成订单的数量
sum(case when tr.start_time is null then 1 else 0 end) as qx #求每日取消订单
from tb_get_car_record td left join tb_get_car_order tr 
on td.uid=tr.uid and td.order_id=tr.order_id
group by pday#分组每日订单信息
) as t 
) as t1
where
t1.pday between "2021-10-01" and "2021-10-03"#提取2021-10-01到2021-10-03数据
order by t1.pday#进行排序