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

明确题意：

统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。

问题分解：

• 计算每天的近7日订单的日均完成量和日均取消量：
  • 计算每天的订单完成量和取消量（生成子表t_finish_cancel_daily）：
    • 统计每个订单是否完成（生成子表t_order_status）：
      • 筛选必要的时间窗（题目只要国庆头3天的近7日）：WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03'
      • 生成日期：DATE(order_time) as dt
      • 生成完成状态：IF(start_time IS NULL, 0, 1) as is_finish
    • 按日期分组：GROUP BY dt
    • 计算每天的订单完成量：SUM(is_finish) as finish_num
    • 计算每天的订单取消量：COUNT(1) - SUM(is_finish) as cancel_num
  • 计算近7日日均（滑动窗口平均）：
    • 7日日均完成量：AVG(finish_num) over(ORDER BY dt ROWS 6 preceding) as finish_num_7d
    • 7日日均取消量：AVG(cancel_num) over(ORDER BY dt ROWS 6 preceding) as cancel_num_7d
    • 保留2位小数：ROUND(x, 2)
• 基于上述结果，筛选国庆头3天数据：WHERE dt >= '2021-10-01'

细节问题：

• 表头重命名：as

完整代码：

SELECT dt, finish_num_7d, cancel_num_7d
FROM (
    SELECT dt,
        ROUND(AVG(finish_num) over(ORDER BY dt ROWS 6 preceding), 2) as finish_num_7d,
        ROUND(AVG(cancel_num) over(ORDER BY dt ROWS 6 preceding), 2) as cancel_num_7d
    FROM (
        SELECT dt, SUM(is_finish) as finish_num, COUNT(1) - SUM(is_finish) as cancel_num
        FROM (
            SELECT DATE(order_time) as dt, IF(start_time IS NULL, 0, 1) as is_finish
            FROM tb_get_car_order
            WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03'
        ) as t_order_status
        GROUP BY dt
    ) as t_finish_cancel_daily
)as t_finish_cancel_7d
WHERE dt >= '2021-10-01';