SQL笔记| 用窗口函数进行行间比较

告别关联子查询：使用SQL对同一行数据进行行列间比较很简单，只需要在WHERE子句里写上比较对象的列名就可以了。与此相比，比较不同行的数据就要费些功夫了。这时，我们要用到一个更强有力的工具——窗口函数。

增加、减少、没有变化

需要对数据做行间比较的的典型业务场景，是使用记录了时间序列数据的表进行时间序列分析。
如果是用面向过程语言来解决，方法很简单，1.按年递增的顺序排序。2.循环地将当前行的sale列与前一行的sale列进行比较。使用SQL时的思路跟这个不太一样，因为SQL中没有循环和变量，所以无法实现同样的操作。在SQL中，过去的做法通常是在表Sales的基础上，再加一张存储了上一年数据的表（S2)，然后使用关联子查询进行比较。不过，在现在的SQL中，我们可以使用窗口函数来实现。

-- 求与上一年营业额一样的年份（1）：使用关联子查询
SELECT year,sale
 FROM Sales S1 
 WHERE sale = (SELECT sale FROM Sales S2 WHERE S2.year = s1.year -1 )
ORDER BY year;
-- 求与上一年营业额一样的年份（2）：使用窗口函数
SELECT year,current_sale
 FROM (SELECT year,sale as current_sale,SUM(sale) OVER(ORDR BY year RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS pre_sale  FROM Sales ) TMP 
 WHERE current_sale=pre_sale
ORDER BY year;

关联子查询通过把要比较多数据偏移一行，来代替面向过程语言中的循环（关联子查询也因此被称为循环查询）。
这里的关键是使用窗口函数生成的pre_sale列。这里显示的恰好是“向前”偏移了一年多sale列。帧子句中的RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING条件的含义是“限定为当前行年份的上一年”。帧子句是以当前行为起点，限制统计对象的记录范围的窗口函数功能。如果想把条件限定为“当前行年份的下一年”，将PRECEDING改为FOLLOWING就可以了。
在这里，窗口函数的意义是可以在不修改原始表（这里指表Sales）的情况下，在结果中显示新的列（pre_sale列）。这可以说是一种保持信息完整或非破损的处理。虽然这里的SUM(sale)使用了SUM函数，但那其实只是表象，它并没有像聚合函数SUM那样缩减（聚合）表的记录个数。这样一来，我们就可以在子查询的外部比较使用窗口函数生成的虚拟列（pre_sale列）和原始表中的列。

-- 求出增加了还是减少了，或是没有变化（1）：使用关联子查询
SELECT year,current_sale，CASE WHEN current_sale=pre_sale THEN '->'
	WHEN current_sale>pre_sale THEN '↑'
	WHEN current_sale<pre_sale THEN '↓'
	ELSE '-' END AS  var
 FROM (SELECT year,sale as current_sale,（SELECT sale FROM Sales S2 WHERE S2.year = s1.year -1) AS pre_sale  FROM Sales ) TMP 
 ORDER BY year;
-- 求出增加了还是减少了，或是没有变化（2）：使用窗口函数
SELECT year,current_sale，CASE WHEN current_sale=pre_sale THEN '->'
	WHEN current_sale>pre_sale THEN '↑'
	WHEN current_sale<pre_sale THEN '↓'
	ELSE '-' END AS  var
 FROM (SELECT year,sale as current_sale,SUM(sale) OVER(ORDR BY year RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS pre_sale  FROM Sales ) TMP 
 ORDER BY year;

时间轴有间断时：和过去最临近的时间进行比较

-- 查询与过去最临近的年份营业额相同的年份（1）：使用关联子查询
SELECT year,sale 
 FROM Sales2 S1
 WHERE sale = (SELECT sale FROM Sales2 S2 WHERE S2.year = (SELECT MAX(year) FROM Sales2 S3 WHERE S1.year > s3.year))
ORDER BY year;  -- 关联子查询的嵌套会变深，性能会变差。
-- 查询与过去最临近的年份营业额相同的年份（2）：使用窗口函数
SELECT year,sale 
 FROM （ SELECT year，sale AS current_sale, SUM(sale) OVER (ORDER BY year BETWEEN1 PRECEDING AND 1 PRECEDING ) AS pre_sale FROM Sales2 ) TMP
 WHERE 	current_sale=pre_sale
ORDER BY year;

窗口函数与关联子查询

使用关联子查询的代码，和使用窗口函数的代码有如下区别。
1.虽然使用窗口函数的代码中也使用子查询，但这个子查询并不是“关联”子查询。因此，子查询本身也可以单独执行，代码具有很高的可读性，操作也容易理解。通过仅执行子查询，我们还可以轻松进行调试。
2.使用窗口函数的代码仅对表扫描一次就可以了。性能会更好。
总而言之，使用窗口函数的代码读/写起来更简单、性能更好。
为什么可以用串口函数替代关联子查询？关联子查询和窗口函数实现的功能是一样的，都是分割集合，以记录为单位进行循环。