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

SELECT dt,
    ROUND(avg_finish_7d, 2) AS finish_num_7d,
    ROUND(avg_unfinish_7d, 2) AS cancel_num_7d
FROM (
    SELECT dt, 
        AVG(finish_order_cnt) OVER(ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS avg_finish_7d,
        AVG(unfinish_order_cnt) OVER(ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS avg_unfinish_7d
    FROM (
        SELECT DATE_FORMAT(order_time, '%Y-%m-%d') AS dt,
            COUNT(IF(NOT ISNULL(mileage), order_id, NULL)) AS finish_order_cnt,
            COUNT(IF(ISNULL(mileage), order_id, NULL)) AS unfinish_order_cnt
        FROM tb_get_car_order
        WHERE DATE_FORMAT(order_time, '%Y-%m-%d') BETWEEN '2021-09-25' AND '2021-10-03'
        GROUP BY dt
    )un_finish_cnt
)sum_7d
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
GROUP BY dt
ORDER BY dt