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

# 解法1: 滑动窗口RANGE版
SELECT DISTINCT
    dt,
    ROUND(finish/7,2) finish_num_7d,
    ROUND((total-finish)/7,2) cancel_num_7d
FROM(
    SELECT
        DATE(order_time) dt,
        COUNT(start_time) OVER(ORDER BY DATE(order_time) RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) finish,
        COUNT(*) OVER(ORDER BY DATE(order_time) RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) total
    FROM
        tb_get_car_order
)t
WHERE
    dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt

# 解法2:表连接+分组统计
# SELECT
#     dt,
#     ROUND(COUNT(IF(DATEDIFF(dt,order_time) BETWEEN 0 AND 6,start_time,NULL))/7,2),
#     ROUND((COUNT(IF(DATEDIFF(dt,order_time) BETWEEN 0 AND 6,1,NULL)) - 
#     COUNT(IF(DATEDIFF(dt,order_time) BETWEEN 0 AND 6,start_time,NULL)))/7,2)
# FROM
#     tb_get_car_order
# JOIN(
#     SELECT DISTINCT 
#         DATE(order_time) dt 
#     FROM 
#         tb_get_car_order 
#     WHERE 
#         DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-03'
# )t
# GROUP BY
#     dt
# ORDER BY
#     dt