题解|窗口函数在WHERE后运行#国庆近7日日均取消订单量#

# 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