题目：

1）请统计国庆头3天里，每天的近7日 日均订单完成量和日均订单取消量，保留2位小数

2）结果按日期升序排序

考察知识点：

1）窗口函数中的 移动平均计算

题解：有“拆解版”，和“一步到位版”

【拆解版】

步骤1：找出每天的订单完成量和订单取消量

SELECT DATE(order_time) AS dt, /* 转化成日期 */
        COUNT(mileage) AS finish, /* 完成量 */
        COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END) AS cancel 
        /* 取消量：当mileage为空时，则代表该订单是未发生的、被取消的 */
FROM tb_get_car_order
GROUP BY DATE(order_time)

步骤2：找出每天的“近7天的日均完成量和取消量”，这里则需要用到移动平均窗口

基于步骤1的查询结果，对其再嵌套一层查询，得出日均完成量和取消量

SELECT dt,
		AVG(finish) OVER (ORDER BY dt ASC ROWS 6 PRECEDING) AS finish_num_7d, 
        /* 近7天的日均完成量 */
        AVG(cancel) OVER (ORDER BY dt ASC ROWS 6 PRECEDING) AS cancel_num_7d
        /* 近7天的日均取消量 */
FROM

  	/* 步骤1的查询结果 */
	(SELECT DATE(order_time) AS dt,
        	COUNT(mileage) AS finish,
        	COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END) AS cancel
	 FROM tb_get_car_order
	 GROUP BY DATE(order_time)) AS t1

步骤3：最后，从步骤2的结果中，筛选出国庆头3天的数据即可

很简单了，WHERE子句来筛选，ROUND函数对计算量进行四舍五入，并按照日期升序排序即可

SELECT dt, 
	   ROUND(finish_num_7d, 2) AS finish_num_7d, 
       ROUND(cancel_num_7d, 2) AS cancel_num_7d
FROM 

  /* 步骤2的查询结果 */
  (SELECT dt,
          AVG(finish) OVER (ORDER BY dt ASC ROWS 6 PRECEDING) AS finish_num_7d, 
          AVG(cancel) OVER (ORDER BY dt ASC ROWS 6 PRECEDING) AS cancel_num_7d
  FROM
  
   	  /* 步骤1的查询结果 */
      (SELECT DATE(order_time) AS dt,
              COUNT(mileage) AS finish,
              COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END) AS cancel
       FROM tb_get_car_order
       GROUP BY DATE(order_time)) AS t1) AS t2
     
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt ASC;

【一步到位版】

从上述步骤2可知，关键一步在于对步骤1的GROUP BY结果后进行计算移动平均的窗口操作，这一步其实可以直接合并到步骤1当中

# 步骤2中的AVG(finish)，其实就是AVG(COUNT(mileage))：

AVG( COUNT(mileage) ) OVER (ORDER BY DATE(order_time) ASC ROWS 6 PRECEDING)

# 同理，AVG(cancel)则是：
AVG( COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END) ) OVER (ORDER BY DATE(order_time) ASC ROWS 6 PRECEDING)

因此，可得出查询语句：

SELECT DATE(order_time) AS dt,
        COUNT(mileage) AS finish, /* 每天的完成量 */
        COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END) AS cancel, /* 每天的取消量 */
        ROUND(AVG(COUNT(mileage)) OVER (ORDER BY DATE(order_time) ASC ROWS 6 PRECEDING), 2) AS finish_num_7d, 
        /* 每天的近7天日均完成量，并且直接保留2位小数 */
        ROUND(AVG(COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END)) OVER (ORDER BY DATE(order_time) ASC ROWS 6 PRECEDING), 2) AS cancel_num_7d
        /* 每天的近7天日均取消量，并且直接保留2位小数 */
FROM tb_get_car_order
GROUP BY DATE(order_time)

最后，再嵌套一层子查询，筛选出国庆头3天即可（好像也不是一步到位啊我靠）：

SELECT dt, finish_num_7d, cancel_num_7d
FROM

  (SELECT DATE(order_time) AS dt,
          COUNT(mileage) AS finish,
          COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END) AS cancel,
          ROUND(AVG(COUNT(mileage)) OVER (ORDER BY DATE(order_time) ASC ROWS 6 PRECEDING), 2) AS finish_num_7d,
          ROUND(AVG(COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END)) OVER (ORDER BY DATE(order_time) ASC ROWS 6 PRECEDING), 2) AS cancel_num_7d
  FROM tb_get_car_order
  GROUP BY DATE(order_time)) AS t1
 
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt ASC