DW学习--MySQL05（SQL高级处理）

1 窗口函数

1.1 窗口函数基础

• 窗口函数：

  • 介绍：窗口函数也称OLAP函数，OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理
  • 作用：进行排序、生成序列号等一般的聚合函数无法实现的高级操作
  • 语法：

  <窗口函数> OVER ([PARTITION BY <列名>]
  					ORDER BY <排序用列名>) 
  

• 关键字 PARTITON BY：

  • 作用：分组（横向上对表进行分组），用来设定排序的对象范围。即选择看哪个“窗口”
  • 窗口：通过PARTITION BY分组后的记录集合，称为“窗口”。这也是窗口函数的由来。
  • 和GROUP BY的区别：PARTITION BY不具备GROUP BY的汇总功能

• 关键字 ORDER BY：

  • 作用：排序（决定纵向排序的规则），指定哪一列、按照什么顺序进行排序。即在“窗口”内，按照哪种规则排序
  • 和SELECT语句中的ORDER BY关系：窗口函数中的ORDER BY和它一样，可以通过关键字ASC/DESC来指定升序和降序。

因此，窗口函数具有两种功能，分组和排序

• 例子：指定了PARTITION BY

  SELECT product_name
     	  ,product_type
     	  ,sale_price
     	  ,RANK() OVER (PARTITION BY product_type
                       	ORDER BY sale_price) AS ranking
  FROM Product 
  

  
  分析：

  • 【例子】没指定PARTITION BY

  SELECT product_name
     	  ,product_type
     	  ,sale_price
     	  ,RANK() OVER (ORDER BY sale_price) AS ranking              	
  FROM Product 
  

  

1.2 窗口函数种类

• 窗口函数的种类：
  • 能作为窗口函数的聚合函数 ：SUM、AVG、COUNT、MAX、MIN
  • 专用窗口函数：RANK、DENSE_RANK、ROW_NUMBER等
• 专用窗口函数：
  • RANK()函数：计算排序时，若存在相同位次的记录，则会跳过之后的位次。
    【例子】有3条记录排在1位时：1位、1位、1位、4位…
  • DENSE_RANK()函数：计算排序，若存在相同位次的记录，也不会跳过之后的位次。
    【例子】有3条记录排在1位时：1位、1位、1位、2位…
  • ROW_NUMBER()函数：赋予唯一的连续位次
    【例子】有3条记录排在1位时：1位、2位、3位、4位…
  • 【例子】以上3中函数的结果：

  SELECT  product_name
    	   ,product_type
    	   ,sale_price
     	   ,RANK() OVER (ORDER BY sale_price) AS ranking
     	   ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
         ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product
  

  

  注意：
  1.专用窗口函数不用参数，所以括号通常都是空的。
  2.窗口函数的位置只能在SELECT子句中。
  原因：（在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。）（在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的在得到排序结果之后，如果通过 WHERE 子句中的条件除去了某些记录，或者使用 GROUP BY 子句进行了汇总处理，那好不容易得到的排序结果也无法使用了。）

• 作为窗口函数的聚合函数：
  • 语法：括号里不为空，其他的和专用窗口函数语法一样
  • 区别：得出的结果是一个累积的聚合函数值
  • 【例子】SUM和AVG

  SELECT  product_id
         ,product_name
  	   ,sale_price
         ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
         ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  
  FROM product;  
  

  
  

1.3 计算移动平均

• 框架：窗口函数是将表以窗口为单位进行分割，并在其中排序的函数，其中还可以在窗口内指定更详细的汇总范围，这个汇总范围称为**“框架”**
• 计算移动平均的语法：

  <窗口函数> OVER (ORDER BY <排序用的列名>
  				 ROWS n PRECEDING)
  <窗口函数> OVER (ORDER BY <排序用的列名>
  				 ROWS BETWEEN n PRECEDING AND n FOLLOWING)
  

  • 关键字 PRECEDING：将框架指定为“截止到<排序用的列>之前n行”，包括自身行
  • 关键字 FOLLOWING：将框架指定为“截止到<排序用的列>之后n行”，包括自身行
  • 关键字 BETWEEN n PRECEDING AND n FOLLOWING：将框架指定为“截止到<排序用的列>之前n行+之后n行，包括自身行
• 【例子】： PRECEDING

  SELECT  product_id
  	   ,product_name
  	   ,sale_price
         ,AVG(sale_price) OVER (ORDER BY product_id
                             ROWS 2 PRECEDING) AS moving_avg
  FROM product  
  

  
  分析：商品编号为“0004”的“菜刀”以下的记录和窗口函数的计算结果并不相同。这是因为指定了框架，将汇总对象限定为了“最靠近的 3 行”。
  
• 移动平均：这种最靠近几行的统计方法，被称为“移动平均（moving average）”

2 GROUPING 运算符

• GROUPING运算符：
  • 作用：只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想要同时得到，可以使用GROUPING运算符。

2.1 同时得到合计行

• 方法： 用GROUP BY别计算出合计行和按条件汇总的结果，然后通过UNION ALL连接在一起。

• 【例子】得出下图

  

  # 只用GROUP BY
  SELECT product_type, SUM(sale_price)
   FROM Product
  GROUP BY product_type;
  # 得不出总合计行
  

  

  SELECT '合计' AS product_type, SUM(sale_price)
    FROM Product
  UNION ALL
  SELECT product_type, SUM(sale_price)
    FROM Product
  GROUP BY product_type;
  

  

2.2 ROLLUP

• 作用：计算合计和小计
• 【例子】分别得出商品价格的小计和合计

  SELECT  product_id
  	   ,product_name
         ,sale_price
         ,AVG(sale_price) OVER (ORDER BY product_id
                             ROWS 2 PRECEDING) AS moving_avg
         ,AVG(sale_price) OVER (ORDER BY product_id
                             ROWS BETWEEN 1 PRECEDING 
                                      AND 1 FOLLOWING) AS moving_avg  
  FROM product  
  

  

3 习题

1.请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。

SELECT  product_id
       ,product_name
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
  FROM product

• 答案：
  
  代码分析：SELECT语句的意思是，按照“product_id”的升序排序，并计算出截止到当前行的sale_price最大值。

2.继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）

• 答案：

  SELECT regist_date,product_name,sale_price
     	, SUM(sale_price) OVER (ORDER BY regist_date) as sum_sale_price
  FROM product;
  

  

3.思考题

① 窗口函数不指定PARTITION BY的效果是什么？

② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。

• 答案：
  • ①见本文1.1中的例子，不指定PARTITION BY，则没有了对表横向的分组。
  • ②原因：
    只能在SELECT子句中使用：（在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY子句处理后的“结果”进行的操作。）（在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的在得到排序结果之后，如果通过WHERE 子句中的条件除去了某些记录，或者使用GROUP BY子句进行了汇总处理，那好不容易得到的排序结果也无法使用了。）
    在ORDER BY 子句使用：在ORDER BY子句的执行是在SELECT语句执行完毕后的，因此并不会影响窗口函数的执行，对结果也没有影响。