Иногда при объединении данных с SQL мы хотели бы добавить несколько дополнительных фильтров. Например, рассмотрим следующие данные мирового банка:
ВВП на душу населения (в текущих ценах)
|
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 |
И структура таблицы:
|
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); |
Теперь предположим, что мы хотели бы найти число стран с ВВП выше 40 000 за каждый год.
Со стандартным SQL: 2003, а теперь и с недавно выпущенным PostgreSQL 9.4 мы можем теперь воспользоваться новым предложением FILTER , которое позволяет нам написать следующий запрос:
|
1
2
3
4
5
6
7
|
SELECT year, count(*) FILTER (WHERE gdp_per_capita >= 40000)FROM countriesGROUP BY year |
Приведенный выше запрос теперь даст:
|
1
2
3
4
5
6
|
year count------------2012 42011 52010 42009 4 |
И это еще не все! Как всегда, вы можете использовать любую агрегатную функцию также как оконную функцию, просто добавив в конец предложение OVER() :
|
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 |
Результат будет выглядеть примерно так:
|
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 также будет поддерживать новое предложение FILTER для агрегатных функций
Хорошие новости для пользователей jOOQ. Вы можете написать тот же запрос с jOOQ интуитивно как таковой:
|
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(); |
… и
|
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(); |
И лучше всего то, что jOOQ (как обычно) эмулирует вышеприведенное предложение для вас, если вы не используете PostgreSQL. Эквивалентный запрос будет:
|
1
2
3
4
5
6
7
|
SELECT year, count(CASE WHEN gdp_per_capita >= 40000 THEN 1 END)FROM countriesGROUP BY year |
Подробнее о том, что нового в PostgreSQL 9.4, читайте здесь
| Ссылка: | Замечательное предложение FILTER PostgreSQL 9.4 / SQL: 2003 для агрегатных функций от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и AND JOOQ . |