Sometimes when aggregating data with SQL, we’d love to add some additional filters. For instance, consider the following world bank data:
GDP per capita (current US$)
1 2 3 4 5 6 7 8 9 | 2009 2010 2011 2012CA 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 |
And the table structure:
1 2 3 4 5 | CREATE TABLE countries ( code CHAR(2) NOT NULL, year INT NOT NULL, gdp_per_capita DECIMAL(10, 2) NOT NULL); |
Now, let’s assume we’d like to find the number of countries with a GDP higher than 40,000 for each year.
With standard SQL:2003, and now also with the newly released PostgreSQL 9.4, we can now take advantage of the new FILTER clause, which allows us to write the following query:
1 2 3 4 5 6 7 | SELECT year, count(*) FILTER (WHERE gdp_per_capita >= 40000)FROM countriesGROUP BY year |
The above query will now yield:
1 2 3 4 5 6 | year count------------2012 42011 52010 42009 4 |
And that’s not it! As always, you can use any aggregate function also as a window function simply by adding an OVER() clause to the end:
1 2 3 4 5 6 7 8 9 | SELECT year, code, gdp_per_capita, count(*) FILTER (WHERE gdp_per_capita >= 40000) OVER (PARTITION BY year)FROM countries |
The result would then look something like this:
1 2 3 4 5 6 | year code gdp_per_capita count------------------------------------2009 CA 40764.00 42009 DE 40270.00 42009 FR 40488.00 42009 GB 35455.00 4 |
jOOQ 3.6 will also support the new FILTER clause for aggregate functions
Good news for jOOQ users. You can write the same query with jOOQ intuitively as such:
1 2 3 4 5 6 7 8 9 | DSL.using(configuration) .select( COUNTRIES.YEAR, count().filterWhere( COUNTRIES.GDP_PER_CAPITA.ge(40000) )) .from(COUNTRIES) .groupBy(COUNTRIES.YEAR) .fetch(); |
… and
01 02 03 04 05 06 07 08 09 10 | 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(); |
And the best thing is that jOOQ (as usual) emulates the above clause for you if you’re not using PostgreSQL. The equivalent query would be:
1 2 3 4 5 6 7 | SELECT year, count(CASE WHEN gdp_per_capita >= 40000 THEN 1 END)FROM countriesGROUP BY year |
Read more about what’s new in PostgreSQL 9.4 here
| Reference: | The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
