题解|窗口函数在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

全部评论

相关推荐

程序员小白条:找的太晚,别人都是大三实习,然后大四秋招春招的,你大四下了才去实习,晚1年
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务