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

SELECT  dt, finish_num_7d, cancel_num_7d
FROM
    (
        SELECT
            dt,
            ROUND(
                SUM(finish_cnt) OVER (
                    ORDER BY
                        dt rows 6 preceding
                )/7,
                2
            ) finish_num_7d,
            ROUND(
                SUM(cancel_cnt) OVER (
                    ORDER BY
                        dt rows 6 preceding
                )/7,
                2
            ) cancel_num_7d
        FROM
            (
                SELECT
                    DATE(order_time) dt,
                    SUM(IF(mileage IS NOT NULL, 1, 0)) finish_cnt,
                    SUM(IF(mileage IS NULL, 1, 0)) cancel_cnt
                FROM
                    tb_get_car_order
                WHERE
                    DATE(order_time) BETWEEN '2021-09-25' AND '2021-10-03'
                GROUP BY
                    1
            ) t1
    ) t2
WHERE
    dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt