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