五、窗口函数

1. 窗口函数

1.1 窗口函数的概念

窗口函数也被称为OLAP(OnLine AnalyticalProcessing)函数，作用是对数据库的数据进行实时分析处理。

窗口函数的语句

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

• 其中PARTITION BY的作用类似于GROUP BY的分组功能，但是前者并不会改变表中行数，不具备汇总功能。

• ORDER BY用于规定窗口内使用什么规则来排序。

【例子】按照商品类型分组，并按照售价进行排序

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

1.2 窗口函数的种类

窗口函数大致分为两类：

• RANK，DENSE_RANK等排序用的专用窗口函数
• 将SUM、MAX、MIN等聚合函数用在窗口函数中

1.2.1 专用窗口函数

• RANK函数

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

【例子】

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

• DENSE_RANK函数

计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

【例子】

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

• ROW_NUMBER函数

赋予唯一的连续位次。

【例子】

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

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

聚合函数在开窗函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。

【例子】

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; 

由例子可知，聚合函数结果是，按我们指定的排序，这里是product_id，当前所在行及之前所有的行的合计或均值。即累计到当前行的聚合。

1.3 窗口函数的应用

在聚合函数在窗口函数使用时，计算的是累加到当前行上的所有数据的聚合，实际上，还可以指定更加详细的汇总范围，被称为框架。

语法

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

• PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行

• FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行

• BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”

【例子】

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