MySQL-learning05 高级处理

5.1 窗口函数

窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。

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

注意：

• PARTITION BY设定窗口对象，类似GROUP BY的分组，但是不会进行汇总，即不会改变表中记录的行数，GROUP BY只会显示一行。
• ORDER BY指定按按哪一列、何种顺序进行排序。也是默认升序，可用ASC或DESC来设定升序或降序。

5.2 窗口函数种类

5.2.1 专用窗口函数

1.RANK函数
计算排序时，存在相同位次的记录会跳过之后的位次
如：有 3 条记录排在第 1 位时 (1,1,1,4）
2.DENSE_RANK函数
计算排序时，存在相同位次的记录不会跳过之后的位次
如：有 3 条记录排在第 1 位时 (1,1,1,2)
3.ROW_NUMEBER函数
赋予唯一的连续位次
如：有 3 条记录排在第 1 位时 (1,2,3,4)

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  

5.2.2 聚合函数在窗口函数上的使用

聚合函数（sum,max,min,avg…）在开窗函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的（默认是当前行及所在行之前的行）聚合函数值。举例如下：

SELECT product_name
      ,product_type
      ,sale_price
	  ,SUM(sale_price) OVER (ORDER BY sale_price) AS ranking
  FROM product;	

注意：当ORDER BY中的列的值相同时会当成同一行进行处理，如上图中打孔器和叉子的sale_price都是500，进行SUM时会都加进去。
聚合函数也可以设定汇总范围。该汇总范围也叫框架(frame)。

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )               
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行
FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行

5.3 GROUPING运算符

GROUPING运算符有：rollup,cube,grouping sets()，但是MySQL目前只支持rollup。
常规的GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP关键字。

SELECT product_name,product_type,regist_data,SUM(sale_price)
  FROM product
 GROUP BY product_type WITH ROLLUP;

合计那一行的聚合类型为Null,其他值和上一行一致。

5.4 练习题

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

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

答： 新增记录的Current_max_price列的值为新增记录（含此新增记录）之前的最大sale_price。

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

SELECT product_name
			 ,regist_data
			 ,SUM(sale_price) OVER (PARTITION BY regist_data) AS SUM(sale_price)
  FROM product
 ORDER BY regist_data;

5.3
① 窗口函数不指定PARTITION BY的效果是什么？
答：相当于以整体来分组。
② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。