题解|窗口函数在WHERE后运行#国庆近7日日均取消订单量#
国庆期间近7日日均取消订单量
https://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703
# SELECT
# DATE_FORMAT(order_time,'%Y-%m-%d') AS dt,
# (CASE WHEN start_time IS NOT NULL THEN 1 ELSE 0) AS finishi_num,
# (CASE WHEN start_time IS NULL THEN 1 ELSE 0) AS cancel_num
# FROM tb_get_car_order
SELECT *
FROM(
SELECT dt,
ROUND(SUM(finish_num) OVER(ORDER BY dt ROWS 6 PRECEDING)/7,2)AS finish_num_7d,
ROUND(SUM(cancel_num) OVER(ORDER BY dt ROWS 6 PRECEDING)/7,2)AS cancel_num_7d
FROM(
SELECT
DATE_FORMAT(order_time,'%Y-%m-%d') 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 THEN 1 ELSE 0 END) AS cancel_num
FROM tb_get_car_order
GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d')
)t1
)t2
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt
#### 不加最外层的SELECT *就会报错， 原因是窗口函数在WHERE 之后运行！！！！
# SELECT dt,
# ROUND(SUM(finish_num) OVER(ORDER BY dt ROWS 6 PRECEDING)/7,2)AS finish_num_7d,
# ROUND(SUM(cancel_num) OVER(ORDER BY dt ROWS 6 PRECEDING)/7,2)AS cancel_num_7d
# FROM(
# SELECT
# DATE_FORMAT(order_time,'%Y-%m-%d') 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 THEN 1 ELSE 0 END) AS cancel_num
# FROM tb_get_car_order
# GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d')
# )t1
# WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
# ORDER BY dt
