PostgreSQL 高级SQL(四) 滑动窗口函数

本文是转载，原文地址是：https://www.jianshu.com/p/184419ee68c5

 

上章节我们讲述的窗口函数都属于静态窗口，然而我们很多场景是需要滑动窗口，比如我们需要查看这样的一张报表，这张报表包含国家名字，年份，GDP,当前年份与上一年、下一年的GDP均值，也就是说GDP均值这一列随着行数的推移，动态移动变化的，那么我们可以借助PG的滑动窗口来完成这个功能，SQL如下

SELECT
	country_name,
	"year",
	gdp,
	AVG ( gdp ) OVER ( PARTITION BY country_name ORDER BY "year" DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) 
FROM
	country_gdp_year_final ff  
WHERE
	country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
	AND "year" BETWEEN 2012 AND 2017;

（获取每年与前后俩年的均值GDP）

preceding 中文意思:前面的

following 中文意思 :后面的

上面的 rows between 1 preceding and 1 following 定义的滑动窗口包含三行，当前行，当前行的前一行，当前行的后一行

我们可以计算一下每行的平均值的意义，首先我们的窗口限制在国家这个字段窗口里面，并且按照年份降序排序，

中国2017年avg(gdp)=(2017GDP+2016GDP)/2 因为没有选择2018年所以2017年的前一年是不存在的，后一年是2016年

中国2016年avg(gdp)=(2017GDP+2016GDP+2015GDP)/3 2016年是当前年份

中国2015年avg(gdp)=(2016GDP+2015GDP+2014GDP)/3 2015年是当前年份

......

中国2012年avg(gdp)=(2013GDP+2012GDP)/2 2012年是当前年份,因为没有选择2011年，所以2012年的后一年不存在，前一年是2013

我们可以通过列转行函数array_agg来进行一下更加直观的认识

SELECT
	country_name,
	"year",
	gdp,
	ARRAY_AGG ( gdp ) OVER ( PARTITION BY country_name ORDER BY "year" DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) 
FROM
	country_gdp_year_final ff 
WHERE
	country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
	AND "year" BETWEEN 2012 AND 2017;

 

PG里面的滑动窗口还有一个关键词：unbounded(无届的)，这个关键词可以放在preceding ，following之前

SELECT
	country_name,
	"year",
	gdp,
	ARRAY_AGG ( "year" ) OVER ( PARTITION BY country_name ORDER BY "year" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) 
FROM
	country_gdp_year_final 
WHERE
	country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
	AND "year" BETWEEN 2012 AND 2017;

　　

总结：从上面的示例中我们可以看出滑动窗口函数的强大，但是需要记住的一点是：移动窗口需要配合order by子句一起使用，如果没有order by的话，就会出现问题，因为数据没有事先排序，那么滑动窗口计算出来的数据就会出出现随机，我们可以将order by 子句去掉来看一下结果

SELECT
	country_name,
	"year",
	gdp,
	ARRAY_AGG ( "year" ) OVER ( PARTITION BY country_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) 
FROM
	country_gdp_year_final 
WHERE
	country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
	AND "year" BETWEEN 2012 AND 2017;

如果我们想要获取的不仅仅是平均值还有最大值和最小值这俩列，我们可以这样使用

SELECT
	country_name,
	"year",
	gdp,
	AVG ( gdp ) OVER ( PARTITION BY country_name ORDER BY "year" DESC ),
	MIN ( gdp ) OVER ( PARTITION BY country_name ORDER BY "year" DESC ),
	MAX ( gdp ) OVER ( PARTITION BY country_name ORDER BY "year" DESC ) 
FROM
	country_gdp_year_final 
WHERE
	country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
	AND "year" BETWEEN 2012 AND 2017;

我们看一下上面的SQL代码，可以看到 over()括号里的代码都是一样的，在此，PG为我们提供一个提取窗口子句的功能，我们可以将上面的代码转换为下面等价可读性更好的代码

SELECT
	country_name,
	"year",
	gdp,
	AVG ( gdp ) OVER ( myWindows ),
	MIN ( gdp ) OVER ( myWindows ),
	MAX ( gdp ) OVER ( myWindows ) 
FROM
	country_gdp_year_final  
WHERE
	country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
	AND "year" BETWEEN 2012 AND 2017 WINDOW myWindows AS ( PARTITION BY country_name ORDER BY "year" DESC );

可以看到使用window提取子句后的代码可读性更好，但是使用window的时候，上面的滑动窗口是不可以使用window提取子句的，目前PG不支持