PostgreSQL 高级SQL(三) 窗口函数

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

 

这一章节我们将了解postgresql 中聚合函数后面的over()子句，可能大家在工作的时候或多或少也涉及过over()子句的使用。

我们如果要实现一张这样的报表，这张报表有四列，国家名字，年份，年份GDP，1960-2018年该国家的GDP均值，第四列的结果的目的就是要拿对应年份的GDP和总的均值的GDP做比较，那么我可能会写出来这样的SQL:

?

1 2 3 4 5 6 7 8

SELECT ff.country_name, "year", gdp, cc.averg   FROM country_gdp_year_final ff   LEFT JOIN (SELECT country_name, AVG(gdp) AS averg                FROM country_gdp_year_final               WHERE country_code = 'CHN'               GROUP BY country_name) cc     ON cc.country_name = ff.country_name  WHERE ff.country_code = 'CHN';

 

可以看到如果我们要在报表中多出一样这样的均值列的话，原生的SQL就只能以这种相似的方式来获取,然后做join，幸运的是PG为我们提供了这种原生的函数获取这种结果集，talk is cheap,show you the code!

?

1	select country_name,"year",gdp,avg(gdp) over() from country_gdp_year_final ff where country_code='CHN';

over()子句又称作窗口函数，这个窗口是将where 条件获取的总的集合假想成一个窗口，并且avg(gdp) over()获取的是这个假想集的平均值。

上面的例子中我们只是获取了一个国家的数据，如果我们将where条件换成下面的条件的话

?

1 2 3 4 5 6 7 8 9 10

SELECT     country_name,     "year",     gdp,     AVG ( gdp ) OVER ( ) FROM     country_gdp_year_final ff WHERE     country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' )     AND "year" BETWEEN 2015 AND 2017；

从结果可以看到最后一列的结果并不是我们想要的当前国家在2015-2017这三年的均值，而是六个国家这三年的均值，若要想要获得理想结果的话，我们只能借助于窗口函数的partition by 子句了，请看代码，

?

1 2 3 4 5 6 7 8 9 10

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

partition by country_name的作用是将总集合按照country_name划分为独立的窗口,中国的avg=中国三年的gdp和/3得到的，partition by 的作用就是划分更小的假想集窗口，在这个窗口里面求平均值。上面的结果中每个国家的结果是按照年份降序排序的，这是一种巧合而已，如果我们想要每个窗口的数据按照年升序排序的话，我们可以在over里面加入 order by year asc ,代码如下

?

1 2 3 4 5 6 7 8 9 10

SELECT     country_name,     "year",     gdp,     AVG ( gdp ) OVER ( PARTITION BY country_name ORDER BY YEAR ASC ) FROM     country_gdp_year_final ff  WHERE     country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' )     AND "year" BETWEEN 2015 AND 2017;

年份是按照年份升序排序了但是数据不是原来的数据了。。。我们可以看到中国的窗口平均值变化了，并且三年的均值是不一样的，很多新人看到这可能会很迷糊，为什么avg不是上面的三个一样的值11497786437410.0000，而是不同的三个值呢，我们在这需要澄清以下，这个查询所做的事情是计算到时间序列中一个特定时间点的平均值，

?

1 2 3 4 5

2015中国GDP平均值=2015年中国gdp/1,(按照时间升序计算到2015年中国均值GDP)   2016中国GDP平均值=(2015年中国gdp+2016年中国gdp)/2,(按照时间升序计算到2016年中共均值GDP)   2017中国GDP平均值=(2015年中国gdp+2016年中国gdp+2017年中国gdp)/3,(按照时间升序计算到2017年中国均值GDP)

如果我们想要的的是按照年份降序排序，但是均值是2015-2017三年的均值的话，我们可以去掉over里面的order by year,在where 后面加上这样查询order by country_name,year asc;

?

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     country_name,     "year",     gdp,     AVG ( gdp ) OVER ( PARTITION BY country_name ) FROM     country_gdp_year_final ff  WHERE     country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' )     AND "year" BETWEEN 2015 AND 2017 ORDER BY     country_name,YEAR ASC;