很棒的PostgreSQL 9.4 / SQL：2003 FILTER子句，用于聚合函数

有时，当使用SQL聚合数据时，我们很乐意添加一些其他过滤器。 例如，考虑以下世界银行数据：

人均GDP（现价美元）

2009    2010    2011    2012
CA      40,764  47,465  51,791  52,409	
DE      40,270  40,408  44,355  42,598	
FR      40,488  39,448  42,578  39,759	
GB      35,455  36,573  38,927  38,649	
IT      35,724  34,673  36,988  33,814	
JP      39,473  43,118  46,204  46,548	
RU       8,616  10,710  13,324  14,091	
US      46,999  48,358  49,855  51,755

和表结构：

CREATE TABLE countries (
  code CHAR(2) NOT NULL,
  year INT NOT NULL,
  gdp_per_capita DECIMAL(10, 2) NOT NULL
);

现在，假设我们希望找到每年GDP高于40,000的国家/地区的数量。

使用标准SQL：2003，现在还使用新发布的PostgreSQL 9.4 ，我们现在可以利用新的FILTER子句，该子句允许我们编写以下查询：

SELECT
  year,
  count(*) FILTER (WHERE gdp_per_capita >= 40000)
FROM
  countries
GROUP BY
  year

上面的查询现在将产生：

year   count
------------
2012   4
2011   5
2010   4
2009   4

事实并非如此！ 与往常一样， 您只需将OVER()子句添加到末尾， 即可将任何聚合函数也用作窗口函数 ：

SELECT
  year,
  code,
  gdp_per_capita,
  count(*) 
    FILTER (WHERE gdp_per_capita >= 40000) 
    OVER   (PARTITION BY year)
FROM
  countries

结果将如下所示：

year   code   gdp_per_capita   count
------------------------------------
2009   CA           40764.00       4
2009   DE           40270.00       4
2009   FR           40488.00       4
2009   GB           35455.00       4

jOOQ 3.6也将为聚合函数支持新的FILTER子句

对于jOOQ用户来说是个好消息。 您可以使用jOOQ直观地编写相同的查询，例如：

DSL.using(configuration)
   .select(
        COUNTRIES.YEAR,
        count().filterWhere(
            COUNTRIES.GDP_PER_CAPITA.ge(40000)
        ))
   .from(COUNTRIES)
   .groupBy(COUNTRIES.YEAR)
   .fetch();

…和

DSL.using(configuration)
   .select(
        COUNTRIES.YEAR,
        COUNTRIES.CODE,
        COUNTRIES.GDP_PER_CAPITA
        count().filterWhere(
                   COUNTRIES.GDP_PER_CAPITA.ge(40000))
               .over(partitionBy(COUNTRIES.YEAR)))
   .from(COUNTRIES)
   .fetch();

最好的是，如果您不使用PostgreSQL，jOOQ（通常）会为您模拟上述子句。 等效查询为：

SELECT
  year,
  count(CASE WHEN gdp_per_capita >= 40000 THEN 1 END)
FROM
  countries
GROUP BY
  year

在这里阅读更多关于PostgreSQL 9.4的新功能

翻译自: https://www.javacodegeeks.com/2015/01/the-awesome-postgresql-9-4-sql2003-filter-clause-for-aggregate-functions.html