PostgreSQL 高级SQL(二) filter子句

2020-07-12 15:24 abce 阅读(5999) 评论(1) 编辑收藏举报

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

本章所用到案例数据来自于上一章节，如果有想使用该数据的读者可以查看上一章节。

这一章节我们想要了解的是PG聚合操作中使用到的filter子句，这个filter子句是ANSI SQL标准中的关键字，并不是PG的专用SQL关键字。如果我们想了解中国、美国、日本、法国、德国、加拿大从1960~2018年中每隔十年的GDP总值情况，我们可能会写出着这样的SQL,

SELECT
    country_name,
    SUM ( CASE WHEN YEAR >= 1960 AND YEAR < 1970 THEN gdp ELSE NULL END ) AS "1960~1969",
    SUM ( CASE WHEN YEAR >= 1970 AND YEAR < 1980 THEN gdp ELSE NULL END ) AS "1970~1979",
    SUM ( CASE WHEN YEAR >= 1980 AND YEAR < 1990 THEN gdp ELSE NULL END ) AS "1980~1989",
    SUM ( CASE WHEN YEAR >= 1990 AND YEAR < 2000 THEN gdp ELSE NULL END ) AS "1990~1999",
    SUM ( CASE WHEN YEAR >= 2000 THEN gdp ELSE NULL END ) AS "2000~至今"
FROM
    country_gdp_year_final 
WHERE
    country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
GROUP BY
    country_name;

从上图可以看出美国的经济体量和我们中国的经济体量不是在一个数量级的，中国每隔十年的GDP实现一倍的增速，美国一直飞速发展时期，中国要实现美国的GDP的话粗略估计需要至少30年的时间甚至更久；

回归正题，我们今天的主角是filter子句，ANSI SQL加入filter关键词的主要目的就是替代case when子句，简化case when参与的聚合语句，增加可可读性，我们用同样的filter 子句实现上面的case when参与的聚合操作。

SELECT
    country_name,
    SUM ( gdp )     FILTER ( WHERE  YEAR >= 1960 AND YEAR < 1970 ) AS "1960~1969",
    SUM ( gdp )   FILTER ( WHERE  YEAR >= 1970 AND YEAR < 1980 ) AS "1970~1979",
    SUM ( gdp )   FILTER ( WHERE  YEAR >= 1980 AND YEAR < 1990 )   AS "1980~1989",
    SUM ( gdp )   FILTER ( WHERE  YEAR >= 1990 AND YEAR < 2000 ) AS "1990~1999",
    SUM ( gdp )   FILTER ( WHERE  YEAR >= 2000 ) AS "2000~至今"
FROM
    country_gdp_year_final  WHERE  country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
GROUP BY
    country_name;

从上面的结果我们可以看出filter子句和case when 子句在聚合函数中使用是等价的，并且filter子句的可读性更好，让人一眼就能看出SQL的目的和作用，

下面我们看一下上面俩个语句的的执行计划：

从上面的结果我们可以看出来俩种语句不仅结果一样而且产生的执行计划也是一致的，并且俩个语句值进行了一次全表扫描就计算出了结果，在平时的开发中，很多开发者为了实现相同的结果可能要进行五次全表扫描，很可能会写出以下的相同查询结果但是不同性能的SQL

SELECT ff.country_name,ff."1960~1969",aa."1970~1979",bb."1980~1989",cc."1990~1999",dd."2000~至今"
FROM
    (
    SELECT country_name,SUM ( gdp ) AS "1960~1969" 
    FROM
        country_gdp_year_final ff 
        WHERE  YEAR >= 1960 AND YEAR < 1970 AND  country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' )   
    GROUP BY
        country_name 
    ) AS ff 
    LEFT JOIN (
    SELECT country_name,SUM ( gdp ) AS "1970~1979" 
    FROM
        country_gdp_year_final 
        WHERE  YEAR >= 1970 AND YEAR < 1980 AND  country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' )   
    GROUP BY
        country_name 
    ) AS aa  ON aa.country_name = ff.country_name
    LEFT JOIN (
    SELECT country_name,SUM ( gdp ) AS "1980~1989" 
    FROM
        country_gdp_year_final 
        WHERE  YEAR >= 1980 AND YEAR < 1990 AND  country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' ) 
    GROUP BY
        country_name 
    ) AS bb  ON bb.country_name = ff.country_name 
    LEFT JOIN (
    SELECT country_name,SUM ( gdp ) AS "1990~1999" 
    FROM
        country_gdp_year_final 
        WHERE  YEAR >= 1990 AND YEAR < 2000and  country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' )   
    GROUP BY
        country_name 
    ) AS cc  ON cc.country_name = ff.country_name 
    LEFT JOIN (
    SELECT country_name,SUM ( gdp ) AS "2000~至今" 
    FROM
        country_gdp_year_final 
    WHERE   YEAR >= 2000 AND  country_code IN ( 'CHN', 'JPN', 'USA', 'DEU', 'CAN', 'FRA' )   
    GROUP BY
    country_name 
    ) AS dd  ON dd.country_name = ff.country_name;