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

# 问题：请统计国庆头3天里，

# 生成3天时间序列:

with recursive t(dt) as (

select cast('2021-10-01' as date)

union all

select dt + interval 1 day

from t

where dt<'2021-10-03' )

# 每天的近7日日均订单情况，

#时间表左连接订单表，条件是datediff(dt,date(order_time)) between 0 and 6

,t2 as(

select dt,order_time,fare  

from t

left join tb_get_car_order

on datediff(dt,date(order_time)) between 0 and 6 )

# 每天的近7日日均订单完成量和日均订单取消量，

# 按日期升序排序。结果保留2位小数。

select dt

    ,round(sum(if(fare is null,0,1))/count(distinct date(order_time)),2) as finish_num_7d

    ,round(sum(if(fare is null,1,0))/count(distinct date(order_time)),2) as cancel_num_7d

from t2

group by dt

order by  dt