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

WITH t1 AS (
    SELECT
        DATE (order_time) AS dt,
        start_time
    FROM tb_get_car_order
    HAVING dt BETWEEN '2021-09-25' AND '2021-10-03'
),
t2 AS (
    SELECT
        dt,
        SUM(IF(start_time IS NULL,0,1)) AS yes_num,
        SUM(IF(start_time IS NULL,1,0)) AS no_num
    FROM t1
    GROUP BY dt
    ORDER BY dt
),
t3 AS (
    SELECT max(dt) AS dt,
           ROUND(sum(yes_num) / 7,2) AS finish_num_7d,
           ROUND(sum(no_num) / 7,2) AS cancel_num_7d
    FROM t2
    WHERE dt BETWEEN '2021-09-25' AND '2021-10-01'
    UNION ALL
    SELECT max(dt) AS dt,
           ROUND(sum(yes_num) / 7,2) AS finish_num_7d,
           ROUND(sum(no_num) / 7,2) AS cancel_num_7d
    FROM t2
    WHERE dt BETWEEN '2021-09-26' AND '2021-10-02'
    UNION ALL
    SELECT max(dt) AS dt,
           ROUND(sum(yes_num) / 7,2) AS finish_num_7d,
           ROUND(sum(no_num) / 7,2) AS cancel_num_7d
    FROM t2
    WHERE dt BETWEEN '2021-09-27' AND '2021-10-03'
)SELECT * FROM t3;

# 这道题的关键在于结果记得除以 7 day！