PostgreSQL 高级SQL(三) 窗口函数

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

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

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!

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

over() 均值列的获取

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

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

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；

放大where条件的结果

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

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 子句获取国家三年的均值

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

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，而是不同的三个值呢，我们在这需要澄清以下，这个查询所做的事情是计算到时间序列中一个特定时间点的平均值，

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;

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;

获取2015-2017三年各个国家均值，按照年份升序排序