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

WITH daliy_tb AS(
    SELECT DATE(order_time) dt, 
        SUM(CASE WHEN fare IS NULL THEN 0 ELSE 1 END) finish_cnt,
        SUM(CASE WHEN fare IS NULL THEN 1 ELSE 0 END) cancel_cnt
    FROM tb_get_car_order
    GROUP BY DATE(order_time)
),
rolling_tb AS(
    SELECT dt,
        ROUND(AVG(finish_cnt) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) finish_num_7d,
        ROUND(AVG(cancel_cnt) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) cancel_num_7d
    FROM daliy_tb
)
SELECT * FROM rolling_tb
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt ASC