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

SELECT *
FROM		-- 3、计算出每天的近7日日均订单完成量和日均订单取消量
    (SELECT 
    dt,
    ROUND(SUM(all_finish) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)/7, 2) finish_num_7d,
    ROUND(SUM(all_cancel) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)/7, 2) cancel_num_7d
    FROM		-- 2、再计算每天有多少完成订单和取消订单
        (SELECT
        DATE(order_time) dt,
        SUM(finish_order) all_finish,
        SUM(cancel_order) all_cancel
        FROM		-- 1、先标记出哪些是完成订单，哪些是取消订单
            (SELECT
            order_time,
            CASE WHEN fare IS NOT NULL THEN 1 ELSE 0 END finish_order,
            CASE WHEN fare IS NULL THEN 1 ELSE 0 END cancel_order
            FROM
            tb_get_car_order
            ) t1
        GROUP BY DATE(order_time)
        ORDER BY DATE(order_time)
        ) t2
    ) t3
WHERE dt BETWEEN DATE('2021-10-1') AND DATE('2021-10-3')