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

# 先计算每天的近7日平均订单完成量和日均订单取消量，按日期升序排序。
# 1.获取每天的订单完成数和订单取消数
with t1 as(
    select date(order_time) dt,
    sum(case when start_time is not null then 1 else 0 end) as finish_num,
    sum(case when start_time is null then 1 else 0 end) as cancel_num
    from tb_get_car_order 
    group by date(order_time)
    order by dt
),
# 2.对每天的聚合前6行也就是聚合每天的近7天的点歌单的完成量和订单的取消量，然后求平均，
t2 as(
    select dt,
    round(sum(finish_num) over(order by dt rows 6 preceding)/7,2) as finish_num_7d,
    round(sum(cancel_num) over(order by dt rows 6 preceding)/7,2) as cancel_num_7d
    from t1
)
select * from t2
where dt between '2021-10-01' and '2021-10-03'

遇到这种问题尽量使用cte分步骤的去算，这样逻辑更清晰

1.获取每天的订单完成数和订单取消数

2.通过聚合函数order by dt rows 6 preceding进行7天也就是7行聚合求平均

3.where子句的优先级高于select，因此要先选择聚合，后选择where，分两步执行