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

WITH t AS
(
SELECT DATE(order_time) dt,
ROUND(SUM(COUNT(fare))OVER(ORDER BY DATE(order_time) RANGE INTERVAL 6 DAY PRECEDING)/7,2) finish_num_7d,
ROUND(SUM(COUNT(*)-COUNT(fare))OVER(ORDER BY DATE(order_time) RANGE INTERVAL 6 DAY PRECEDING)/7,2) cancel_num_7d
FROM tb_get_car_order
GROUP BY dt
)
SELECT *
FROM t
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt