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

问题分解： 1.求出每天订单的完成数以及取消数

SELECT
DATE(order_time)dt,
SUM((CASE WHEN start_time IS NOT NULL THEN 1 ELSE 0 END))AS finish_num,
SUM((CASE WHEN start_time IS NULL THEN 1 ELSE 0 END))AS cancel_num
FROM
tb_get_car_order a JOIN tb_get_car_record b
ON a.order_id=b.order_id
GROUP BY
dt

2.利用窗口函数求出平均每7日的订单完成数以及取消数

ROUND(AVG(finish_num) OVER(rows 6 preceding),2)finish_num_7d
ROUND(AVG(cancel_num) OVER(rows 6 preceding),2)cancel_num_7d

3.筛选条件为：2021-10-01~2021-10-03，并按照日期排序

WHERE
dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY
dt

4.综合代码为

SELECT
*
FROM
(SELECT
dt,
ROUND(AVG(finish_num) OVER(rows 6 preceding),2)finish_num_7d,
ROUND(AVG(cancel_num) OVER(rows 6 preceding),2)cancel_num_7d
FROM
(SELECT
DATE(order_time)dt,
SUM((CASE WHEN start_time IS NOT NULL THEN 1 ELSE 0 END))AS finish_num,
SUM((CASE WHEN start_time IS NULL THEN 1 ELSE 0 END))AS cancel_num
FROM
tb_get_car_order a JOIN tb_get_car_record b
ON a.order_id=b.order_id
GROUP BY
dt)t1)t2
WHERE
dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY
dt

大神们竟然用几行代码就写出来了，我竟然要写这么多