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

# 国庆头3天里的近7日日均订单完成量和日均订单取消量
# 2021-10-01 ～ 2021-10-03 - 7
# 窗口函数滚动计算

WITH RECURSIVE cal(dt) AS (
  SELECT DATE('2021-09-25')
  UNION ALL
  SELECT DATE_ADD(cal.dt, INTERVAL 1 DAY)
  FROM cal
  WHERE cal.dt < DATE('2021-10-03')
),
daily AS (
  SELECT
    DATE(order_time) AS dt,
    SUM(CASE 
          WHEN start_time IS NOT NULL THEN 1 
          ELSE 0 
        END) AS finish_num,
    SUM(CASE 
          WHEN start_time IS NULL AND finish_time IS NOT NULL THEN 1 
          ELSE 0 
        END) AS cancel_num
  FROM tb_get_car_order
  WHERE order_time >= '2021-09-25'
    AND order_time <  '2021-10-04'
  GROUP BY DATE(order_time)
),
d AS (
  SELECT
    c.dt,
    COALESCE(di.finish_num, 0) AS finish_num,
    COALESCE(di.cancel_num, 0) AS cancel_num
  FROM cal c
  LEFT JOIN daily di
    ON c.dt = di.dt
),
w AS (
  SELECT
    dt,
    ROUND(SUM(finish_num) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7.0, 2) AS finish_num_7d,
    ROUND(SUM(cancel_num) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7.0, 2) AS cancel_num_7d
  FROM d
)
SELECT *
FROM w
WHERE dt >= '2021-10-01' AND dt < '2021-10-04'
ORDER BY dt;