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

# dt、近七日日均订单完成量、日均订单取消量
# 构造 p-date 订单完成量、订单取消量(根据start_time是否为空判断完成or取消)  
# select date(start_time) as dt,
# count(start_time) as finish_num,
# sum(if(start_time is null,1,0)) as cancel_num
# from tb_get_car_order
# # where date(start_time) is not null   # 筛选不为空的
# group by date(start_time)

# 标记订单状态(完成or未完成)
with table1 as
(
    select date_format(order_time,'%Y-%m-%d') as dt,
    if(start_time is null,0,1) as if_finish,
    if(start_time is null,1,0) as if_cancel
    from tb_get_car_order
    # where date(start_time) is not null
),
table2 as (
    select dt,
    sum(if_finish) as finish_num,
    sum(if_cancel) as cancel_num
    from table1
    group by dt
),
# 构造近七日日均订单完成量、日均订单取消量
table3 as(
    select dt,
    round(avg(finish_num)over(order by dt rows 6 preceding),2) as finish_num_7d,
    round(avg(cancel_num)over(order by dt rows 6 preceding),2) as cancel_num_7d
    from table2
)
select *
from table3
where dt>='2021-10-01' and dt<='2021-10-03'
# where dt between '2021-10-01' and '2021-10-03' 
# 注意点1：时间筛选要用order_time，不能用start_time
# 注意点2: 最后再用where单独筛选一遍需要的时间，而不是过程中where筛选...