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

1. 指标
   • 每天的近7日的日均订单完成量
   • 每天的近7日的日均订单取消量
2. 条件
   • 国庆头3天
3. 排序
   • 日期升序

# 1.求出每天的订单完成量和取消量
# select date(order_time) dt,
# sum(case when start_time is not null then 1 else 0 end) finish_num
# ,sum(case when start_time is  null then 1 else 0 end) cancel_num
# from tb_get_car_order
# group by date(order_time)
# 2.每天的近7日的日均订单完成量
# SELECT dt
# ,sum(finish_num)over(order by dt rows 6 preceding)/7 finish_num_7d
# ,sum(cancel_num)over(order by dt rows 6 preceding)/7 cancel_num_7d
# from (
#   select date(order_time) dt,
#   sum(case when start_time is not null then 1 else 0 end) finish_num
#   ,sum(case when start_time is  null then 1 else 0 end) cancel_num
#   from tb_get_car_order
#   group by date(order_time)
# ) t
# 3.筛选国庆头3天
select *
from (
  SELECT dt
  ,round(sum(finish_num)over(order by dt rows 6 preceding)/7,2) finish_num_7d
  ,round(sum(cancel_num)over(order by dt rows 6 preceding)/7,2) cancel_num_7d
  from (
    select date(order_time) dt,
    sum(case when start_time is not null then 1 else 0 end) finish_num
    ,sum(case when start_time is  null then 1 else 0 end) cancel_num
    from tb_get_car_order
    group by date(order_time)
  ) t
) t1
where dt between '2021-10-01' and '2021-10-03'
order by dt