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

• 首先计算每天的完成量与未完成量，利用窗口函数计算每一天的近七天的平均值，再筛选符合要求的天数
• 问题：似乎无法避免近七天的要求，即使使用range，如果有一天没有订单可能会报错？
• 方法2：使用自连接直接对于某一天计算它对应七天内的订单情况，SELECT中加入子循环利用日期差函数，

SELECT *
FROM (
    SELECT dt
            ,ROUND(SUM(finish_num) OVER(ORDER BY dt  RANGE INTERVAL 6 DAY PRECEDING) / 7, 2) AS finish_num_7d
            ,ROUND(SUM(cancel_num) OVER(ORDER BY dt  RANGE INTERVAL 6 DAY PRECEDING) / 7, 2) AS cancel_num_7d
    FROM(
        SELECT DATE(event_time) AS dt
                , COUNT(start_time) AS finish_num
                , COUNT(*) - COUNT(start_time) AS cancel_num
        FROM tb_get_car_order
        JOIN tb_get_car_record USING(order_id)
        GROUP BY dt
    ) data1
) data2
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'