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

国庆期间近7日日均取消订单量

http://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703

国庆期间近7日日均取消订单量

明确题意:

统计国庆头3天里,每天的近7日日均订单完成量和日均订单取消量,按日期升序排序。


问题分解:

  • 计算每天的近7日订单的日均完成量和日均取消量:
    • 计算每天的订单完成量和取消量(生成子表t_finish_cancel_daily):
      • 统计每个订单是否完成(生成子表t_order_status):
        • 筛选必要的时间窗(题目只要国庆头3天的近7日):WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03'
        • 生成日期:DATE(order_time) as dt
        • 生成完成状态:IF(start_time IS NULL, 0, 1) as is_finish
      • 按日期分组:GROUP BY dt
      • 计算每天的订单完成量:SUM(is_finish) as finish_num
      • 计算每天的订单取消量:COUNT(1) - SUM(is_finish) as cancel_num
    • 计算近7日日均(滑动窗口平均):
      • 7日日均完成量:AVG(finish_num) over(ORDER BY dt ROWS 6 preceding) as finish_num_7d
      • 7日日均取消量:AVG(cancel_num) over(ORDER BY dt ROWS 6 preceding) as cancel_num_7d
      • 保留2位小数:ROUND(x, 2)
  • 基于上述结果,筛选国庆头3天数据:WHERE dt >= '2021-10-01'

细节问题:

  • 表头重命名:as

完整代码:

SELECT dt, finish_num_7d, cancel_num_7d
FROM (
    SELECT dt,
        ROUND(AVG(finish_num) over(ORDER BY dt ROWS 6 preceding), 2) as finish_num_7d,
        ROUND(AVG(cancel_num) over(ORDER BY dt ROWS 6 preceding), 2) as cancel_num_7d
    FROM (
        SELECT dt, SUM(is_finish) as finish_num, COUNT(1) - SUM(is_finish) as cancel_num
        FROM (
            SELECT DATE(order_time) as dt, IF(start_time IS NULL, 0, 1) as is_finish
            FROM tb_get_car_order
            WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03'
        ) as t_order_status
        GROUP BY dt
    ) as t_finish_cancel_daily
)as t_finish_cancel_7d
WHERE dt >= '2021-10-01';
SQL大厂真题 文章被收录于专栏

大厂真题手把手教你怎么解~

全部评论
不懂就问,请问大佬,万一出现某天没有订单的情况,也就是日期出现断档,是不是就不能用滑动窗口函数了?
点赞
送花
回复 分享
发布于 2022-02-28 14:25
查了一下,窗口好像可以用时间偏移,order by dt asc range between interval 6 day preceding and current row,这样好像也是可以的 有一个疑问,问一下大佬,如果1号到3号有没有订单的,这种情况应该怎么处理呀
点赞
送花
回复 分享
发布于 2022-04-14 00:49
京东
校招火热招聘中
官网直投

相关推荐

5 2 评论
分享
牛客网
牛客企业服务