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

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

# 国庆期间近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大厂真题 文章被收录于专栏

07-10 23:19

5 2 评论