SQL窗口函数

1 简介

窗口函数也称为OLAP 函数A。为了让大家快速形成直观印象，才起了这样一个容易理解的名称。OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。

2 窗口函数语法

基本语句

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

其中[]中的内容可以省略。
## 能够作为窗口函数的函数

1. 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
2. RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
   ## 语法的使用方法
   ### 专用窗口函数
   rank函数

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

PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了product_type。ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了sale_price。此外，窗口函数中的ORDER BY 与SELECT 语句末尾的ORDER BY 一样，可以通过关键字ASC/DESC 来指定升序和降序。省略该关键字时会默认按照ASC，也就是升序进行排序。
使用窗口函数时起到关键作用的是PARTITION BY 和GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。

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

之前我们得到的是按照商品种类分组后的排序，而这次变成了全部商品的排序。像这样，当希望先将表中的数据分为多个部分（窗口），再使用窗口函数时，可以使用PARTITION BY 选项。
## 窗口函数区别
1、RANK
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
比如有3 条记录排在第1 位时：1 位、1 位、1 位、4 位
2、DENSE_RANK
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
比如有3 条记录排在第1 位时：1 位、1 位、1 位、2 位……
3、ROW_NUMBER
赋予唯一的连续位次。
比如从有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;

注意点：原则上窗口函数只能在SELECT子句中使用。因为在DBMS 内部，窗口函数是对WHERE 子句或者GROUP BY 子句处理后的“结果”进行的操作。大家仔细想一想就会明白，在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的。在得到排序结果之后，如果通过WHERE 子句中的条件除去了某些记录，或者使用GROUP BY 子句进行了汇总处理，那好不容易得到的排序结果也无法
使用了。
### 作为窗口函数使用的聚合函数
先来看一个将SUM 函数作为窗口函数使用的例子。

SELECT product_id, product_name, sale_price,
　SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;

实现了累加和。
使用AVG

SELECT product_id, product_name, sale_price,
	AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;

实现了求当前记录的平均值。
### 实现移动平均
指定“最靠近的3行”作为汇总对象

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

这里我们使用了ROWS（“行”）和PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的3 行”。
使用关键字FOLLOWING（“之后”）替换PRECEDING，就可以指定“截止到之后~ 行”作为框架了。

如果希望将当前记录的前后行作为汇总对象时，就可以像代码清单8-7那样，同时使用PRECEDING（“之前”）和FOLLOWING（“之后”）关键字来实现。

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

在上述代码中，我们通过指定框架，将“1 PRECEDING”（之前1 行）和“1 FOLLOWING”（之后1 行）的区间作为汇总对象。具体来说，就是将如下3 行作为汇总对象来进行计算。