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

# 解法一
#   with
#     combineday as (
#         select
#             '2021-10-01' as date
#         union
#         select
#             '2021-10-02'
#         union
#         select
#             '2021-10-03'
#     ), # 自行定义国庆前三日
#     dataList as (
#         select
#             tb_get_car_record.uid,
#             order_id,
#             start_time,
#             finish_time
#         from
#             tb_get_car_record
#             join tb_get_car_order using (order_id)
#     ) #根据order_id将tb_get_car_record,tb_get_car_order进行连接
# select
#     date,
#     round(count(start_time)/7,2),#已完成的订单总量
#     round(sum(if(start_time is null ,1 ,0))/7,2)#取消订单量
# from
#     combineday,
#     dataList
# where
#     datediff (date, date(finish_time)) between 0 and 6
# group by date
#解法二：瑕疵在于你得确保9月份的25-30日均有数据
select 
    * 
from (
    select 
        groupdate,
        round(sum(daynum)over(order by groupdate rows 6 preceding)/7,2) finish_num_7d, #根据每天的聚合日期求出7天内的订单总数,
        round(sum(daycancelnum)over(order by groupdate rows 6 preceding)/7,2)cancel_num_7d # 根据每天的聚合日期求出7天内的取消订单总数
    from 
        (
        select 
            date(finish_time) as groupdate ,
            sum(case when start_time is null then 0 else 1 end ) as daynum,#求出每天的完成订单量
            sum(case when start_time is null then 1 else 0 end) as daycancelnum#求出每天取消的订单量
        from 
            tb_get_car_record join tb_get_car_order 
            using(order_id)
        group by
            date(finish_time)
        )t1
    ) t2
where
    groupdate between '2021-10-01' and  '2021-10-03'

本题有两种解法，

解法一：自定义一个国庆前三天的数据表，与复合表进行连接，判定条件是datediff(最后日期，开始日期)<=6,再根据已有的国庆三天的数据聚合，就可以得出数据。

解法二：根据复合表通过order_id 进行一个连接，然后根据finish_time进行聚合，先求出每天订单量和取消订单量，在通过窗口函数求出7天综合的单量和取消订单量 (rows 6 preceding)，但是日期的筛选条件不能放到子查询里面，因为如果在子查询内限制了日期会影响窗口函数的求和计算。