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

SELECT *
FROM (
SELECT dt, 
ROUND(SUM(t1.finish_num) OVER(ORDER BY t1.dt ROWS 6 PRECEDING) / 7, 2) AS finish_num_7d, # 4.使用滑动聚合窗口函数每七日累计，计算日平均订单完成量、取消量
ROUND(SUM(t1. cancel_num) OVER(ORDER BY t1.dt ROWS 6 PRECEDING) / 7, 2) AS cancel_num_7d 
FROM(
SELECT DATE(order_time) AS dt, 
SUM(IF(tbo.start_time IS NOT NULL, 1, 0)) AS finish_num,  # 3.按日期分组统计每天的订单完成量、订单取消量
SUM(IF(tbo.start_time IS NULL, 1, 0)) AS cancel_num
FROM tb_get_car_order AS tbo
INNER JOIN tb_get_car_record AS tbr
ON tbo.order_id = tbr.order_id # 1.进行表联结
WHERE DATE(tbo.order_time) BETWEEN '2021-09-25' AND '2021-10-03' # 2.筛选出近7日的所有数据
GROUP BY DATE(order_time)) AS t1) AS t2
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' # 5.筛选国庆三天的记录
ORDER BY dt; # 6.按日期进行升序排序