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

2020-07-12 16:27 abce 阅读(3609) 评论(0) 编辑收藏举报

本文是转载，原文地址是：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 );