SQL笔记| 窗口函数

必知必会的窗口函数

如果熟练掌握了窗口函数，从某种意义上来说，我们就可以像使用面向过程语言那样操作数据。窗口函数能够大幅扩展SQL编程的可能性，是一个非常重要的工具。
窗口函数的应用方法有很多，特别是在进行行间比较时必须依赖相关子查询的情况下，通过使用窗口函数，我们可以去掉相关的子查询，让SQL语句变得更加优雅。

1、什么是窗口

窗口函数出现于20世纪90年代到21世纪初，当时它也被称为“OLAP"(联机分析处理，online analytical procession)函数。
匿名窗口:

SELECT products_id,products_name,sale_price,
	AVG(sale_price) OVER (ORDER BY products_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg 
FROM Products;

有名称的窗口:

SELECT products_id,products_name,sale_price,
	AVG(sale_price) OVER W  AS moving_avg 
FROM Products 
WINDOW W AS (ORDER BY products_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ;

这里显式定义了窗口，并对其应用了AVG函数。这里所说的窗口，就是针对通过FROM子句选择的记录集，使用ORDER BY排序和使用ROWS BETWEEN定义帧之后所形成的数据集。
有名称的窗口可以被重复使用:

SELECT products_id,products_name,sale_price,
	AVG(sale_price) OVER W  AS moving_avg， 
	SUM(sale_price) OVER W  AS moving_sum， 
	COUNT(sale_price) OVER W  AS moving_count， 
	MAX(sale_price) OVER W  AS moving_max， 
FROM Products 
WINDOW W AS (ORDER BY products_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ;

注意：有名称的窗口函数可以用在postgresql和mysql中，但在oracle中使用就会发生错误。

2、一张图看懂窗口函数

窗口函数让人难以理解的原因之一是1个窗口函数中包括多个操作。窗口函数实际上包含下面3个功能。
1）使用PARTITION BY子句分割记录集合。
2）使用ORDER BY子句对记录排序。
3）使用帧子句定义一当前记录为中心的子集。
这个“当前记录”源自“游标”（cursor）的引入——关系数据库一直使用游标向面向过程语言传递数据。
之所以需要游标，是因为关系数据库的表中的记录是无序的，操作的基本单位是记录的集合，也就是一次一集合（set at a time）的操作方式，而面向过程语言的记录是有序的，操作的基本单位是一行记录，也就是一次一记录（record at a time）的操作方式，我们需要用游标来填补二者之间的差异。
在面向过程语言中，根据键对记录集合进行排序，通过for语句或while语句循环记录集合，一行一行地移动当前记录进行处理，这种操作方法至今没有变过。即使在引入地址隐藏和面向对象后，也没有发生改变。在这一点上，窗口函数可以说是将面向过程语言的思想引入到了SQL中。

3、使用帧子句将其他行移至当前行

求过去最临近的值

--过去最临近的日期，也就是上一行
SELECT sample_data  AS cur_date,load_val AS cur_load,
	MIN(sample_date) OVER (ORDER BY sample_date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS latest_date ,
	MIN(load_val ) OVER (ORDER BY sample_date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS latest_load 	
FROM LoadSample;
--向后移动一行
SELECT sample_data  AS cur_date,load_val AS cur_load,
	MIN(sample_date) OVER W AS latest_date ,
	MIN(load_val ) OVER W AS latest_load 	
FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING );
--可以设置“一天前”或“两天前”这样基于列值（而不是行）的帧
SELECT sample_data  AS cur_date,load_val AS cur_load,
	MIN(sample_date) OVER W AS latest_date ,
	MIN(load_val ) OVER W AS latest_load 	
FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC RANGE BETWEEN interval '1' PRECEDING AND  interval '1' PRECEDING );

下面是帧子句中可以使用的选项，大家可以参考。
ROWS：按行设置移动单位。
RANGE：按列值设置移动单位。使用ORDER BY子句来指定基准列。
n PRECEDING：仅向前（行号较小的方向）移动n行。n为正整数
n FOLLWING：仅向后（行号较大的方向）移动n行。n为正整数
UNBOUNDED PRECEDING：一直移动到最前面
UNBOUNDED FOLLOWING：一直移动到最后面
CURRENT ROW：当前行

4、窗口函数的内部动作

查看SQL语句内部动作的手段通常是查看“执行计划”（execution plan）。所谓执行计划，其实就是一份由数据库提供的计划书，以帮着我们确定DBNS在执行SQL语句时，以什么样的访问路径获取数据、执行什么样的计算是最高效的。PostgreSql的执行计划中出现了SORT关键字，MySQL的执行计划中出现了Using filesort关键字，它们都表示排序。
窗口函数的本质是排序。

5、习题

SELECT server，sample_date,SUM(load_val) OVER() AS sum_load 
 FROM ServerLoadSample ;

sum_load列的所有行的值都是同一个值，把整张表作为一个大组进行了处理求和。

SELECT server，sample_date,SUM(load_val) OVER(PARTITION BY server) AS sum_load 
 FROM ServerLoadSample ;

sum_load列的按照server列分组，并对各组执行SUM函数的计算。