Вы действительно понимаете SQL-предложения GROUP BY и HAVING?

В SQL есть некоторые вещи, которые мы просто принимаем как должное, не задумываясь о них должным образом.

Одной из этих вещей являются предложения GROUP BY и менее популярные предложения HAVING . Давайте посмотрим на простой пример. В этом примере мы повторим пример базы данных, которую мы видели в предыдущей статье о потрясающих функциях LEAD (), LAG (), FIRST_VALUE (), LAST_VALUE () :

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

До появления оконных функций агрегации производились только с помощью GROUP BY . Типичный вопрос, который мы могли бы задать нашей базе данных с использованием SQL:

Каковы топ-3 средних государственных долгов в процентах от ВВП для тех стран, чей ВВП на душу населения составлял более 40 000 долларов в год в течение последних четырех лет.

Уф. Некоторые (академические) бизнес-требования.

В SQL (диалект PostgreSQL) мы написали бы:

select code, avg(govt_debt)
from countries
where year > 2010
group by code
having min(gdp_per_capita) >= 40000
order by 2 desc
limit 3

Или со встроенными комментариями

-- The average government debt
select code, avg(govt_debt)
 
-- for those countries
from countries
 
-- in the last four years
where year > 2010
 
-- yepp, for the countries
group by code
 
-- whose GDP p.c. was over 40'000 in every year
having min(gdp_per_capita) >= 40000
 
-- The top 3
order by 2 desc
limit 3

Результат:

code     avg
------------
JP    193.00
US     91.95
DE     56.00

Запомните 10 простых шагов для полного понимания SQL :

FROM генерирует набор данных
WHERE уменьшает сгенерированный набор данных
GROUP BY агрегирует сокращенный набор данных
HAVING уменьшает агрегированный набор данных
SELECT преобразует сокращенный набор агрегированных данных
ORDER BY сортирует преобразованный набор данных
LIMIT .. OFFSET создает отсортированный набор данных

… Где LIMIT .. OFFSET может быть очень разных вкусов .

Пустое предложение GROUP BY

Весьма частным случаем GROUP BY является явное или неявное пустое предложение GROUP BY . Вот вопрос, который мы могли бы задать нашей базе данных:

Есть ли вообще страны с ВВП на душу населения более 50 000 долларов?

А в SQL мы написали бы:

select true answer
from countries
having max(gdp_per_capita) >= 50000

В результате

answer
------
t

Конечно, вы могли бы вместо этого использовать предложение EXISTS ( пожалуйста, не используйте COUNT(*) в этих случаях ):

select exists(
  select 1 
  from countries 
  where gdp_per_capita >= 50000
);

И мы снова получим:

answer
------
t

… Но давайте сосредоточимся на простом предложении HAVING .

Не все знают, что HAVING может использоваться сам по себе или что даже означает иметь HAVING сам по себе. Уже стандарт SQL 1992 позволял использовать HAVING без GROUP BY , но это было до введения GROUPING SETS в SQL: 1999 , когда семантика этого синтаксиса была задним числом однозначно определена:

7.10 <пункт>

1

<having clause> ::= HAVING <search condition>

Синтаксические правила

1) Пусть HC будет <имеющим предложение>. Пусть TE будет <табличным выражением>, которое сразу содержит
HC. Если TE не содержит сразу <group by предложение>, то GROUP BY () является неявным.

Это интересно. Существует неявная GROUP BY ( ) , если мы опускаем явное предложение GROUP BY . Если вы захотите немного углубиться в стандарт SQL, вы найдете:

01

02

03

04

05

06

07

08

09

10

11

12

13

14

15

16

17

18

<group by clause> ::=

    GROUP BY <grouping specification>

<grouping specification> ::=

    <grouping column reference>

  | <rollup list>

  | <cube list>

  | <grouping sets list>

  | <grand total>

  | <concatenated grouping>

<grouping set> ::=

    <ordinary grouping set>

  | <rollup list>

  | <cube list>

  | <grand total>

<grand total> ::= <left paren> <right paren>

Итак, GROUP BY ( ) по сути группируется по «общему итогу», что и происходит интуитивно, если мы просто посмотрим на самый высокий в истории ВВП на душу населения:

select max(gdp_per_capita)
from countries;

Который дает:

max
--------
52409.00

Вышеуказанный запрос также неявно такой же, как этот (который не поддерживается PostgreSQL):

select max(gdp_per_capita)
from countries
group by ();

Потрясающие ГРУППОВЫЕ КОМПЛЕКТЫ

В этом разделе статьи мы покидаем землю PostgreSQL, входя в землю SQL Server, поскольку PostgreSQL постыдно не реализует ничего из следующего (пока).

Теперь мы не можем понять общий итог (пустое предложение GROUP BY ( ) ) без краткого рассмотрения стандартных GROUPING SETS SQL: 1999. Некоторые из вас, возможно, слышали о функциях группировки CUBE() или ROLLUP() , которые являются просто синтаксическим сахаром для часто используемых GROUPING SETS . Давайте попробуем ответить на этот вопрос в одном запросе:

Каковы самые высокие значения ВВП на душу населения в год ИЛИ на страну

В SQL мы напишем:

select code, year, max(gdp_per_capita)
from countries
group by grouping sets ((code), (year))

Что дает два сцепленных набора записей:

code    year    max
------------------------
NULL    2009    46999.00 <- grouped by year
NULL    2010    48358.00
NULL    2011    51791.00
NULL    2012    52409.00
 
CA      NULL    52409.00 <- grouped by code
DE      NULL    44355.00
FR      NULL    42578.00
GB      NULL    38927.00
IT      NULL    36988.00
JP      NULL    46548.00
RU      NULL    14091.00
US      NULL    51755.00

Это мило, не правда ли? По сути, это то же самое, что этот запрос с UNION ALL

select code, null, max(gdp_per_capita)
from countries
group by code
union all
select null, year, max(gdp_per_capita)
from countries
group by year;

На самом деле, это абсолютно то же самое, так как последний явно объединяет два набора сгруппированных записей … то есть два набора группирования. Эта страница документации по SQL Server также очень хорошо объясняет это .

И самый мощный из них: CUBE ()

Теперь представьте, что вы хотели бы добавить «общий итог», а также наивысшее значение для страны и года, производя четыре различных составных набора. Чтобы ограничить результаты, мы также отфильтруем ВВП менее 48000 для этого примера:

select
  code, year, max(gdp_per_capita), 
  grouping_id(code, year) grp
from countries
where gdp_per_capita >= 48000
group by grouping sets (
  (), 
  (code), 
  (year), 
  (code, year)
)
order by grp desc;

Этот красивый запрос теперь произведет все возможные комбинации группировки, которые мы можем себе представить, включая итоговую сумму, чтобы произвести:

code    year    max         grp
---------------------------------
NULL    NULL    52409.00    3 <- grand total
 
NULL    2012    52409.00    2 <- group by year
NULL    2010    48358.00    2
NULL    2011    51791.00    2
 
CA      NULL    52409.00    1 <- group by code
US      NULL    51755.00    1
 
US      2010    48358.00    0 <- group by code and year
CA      2012    52409.00    0
US      2012    51755.00    0
CA      2011    51791.00    0
US      2011    49855.00    0

И поскольку это довольно распространенная операция в отчетах и в OLAP, мы можем просто написать то же самое, используя функцию CUBE() :

select
  code, year, max(gdp_per_capita), 
  grouping_id(code, year) grp
from countries
where gdp_per_capita >= 48000
group by cube(code, year)
order by grp desc;

Совместимость

Хотя первая пара запросов также работала с PostgreSQL, те, которые используют GROUPING SETS будут работать только на 4 из 17 RDBMS, которые в настоящее время поддерживаются jOOQ . Эти:

DB2
оракул
SQL Server
Sybase SQL Anywhere

jOOQ также полностью поддерживает ранее упомянутые синтаксисы. Вариант GROUPING SETS можно записать так:

// Countries is an object generated by the jOOQ
// code generator for the COUNTRIES table.
Countries c = COUNTRIES;
 
ctx.select(
       c.CODE,
       c.YEAR,
       max(c.GDP_PER_CAPITA),
       groupingId(c.CODE, c.YEAR).as("grp"))
   .from(c)
   .where(c.GDP_PER_CAPITA.ge(new BigDecimal("48000")))
   .groupBy(groupingSets(new Field[][] {
       {},
       { c.CODE },
       { c.YEAR },
       { c.CODE, c.YEAR }
   }))
   .orderBy(fieldByName("grp").desc())
   .fetch();

… Или версия CUBE() :

ctx.select(
       c.CODE,
       c.YEAR,
       max(c.GDP_PER_CAPITA),
       groupingId(c.CODE, c.YEAR).as("grp"))
   .from(c)
   .where(c.GDP_PER_CAPITA.ge(new BigDecimal("48000")))
   .groupBy(cube(c.CODE, c.YEAR))
   .orderBy(fieldByName("grp").desc())
   .fetch();

… И в будущем мы будем эмулировать GROUPING SETS посредством их эквивалентных запросов UNION ALL в тех базах данных, которые изначально не поддерживают GROUPING SETS .

Ссылка:

Вы действительно понимаете SQL-предложения GROUP BY и HAVING? от нашего партнера JCG Лукаса Эдера в блоге JAVA, SQL и JOOQ .

Вы действительно понимаете SQL-предложения GROUP BY и HAVING?

Пустое предложение GROUP BY

Потрясающие ГРУППОВЫЕ КОМПЛЕКТЫ

И самый мощный из них: CUBE ()

Совместимость

Категории

Последние статьи

Рефакторинг Hudson God Class

Альтернативы синтаксиса Java лямбда

Morphia и MongoDB: развивающиеся структуры документов

OpenShift Express: развертывание приложения Java EE (с поддержкой AS7)

Интеграция jqGrid, REST, AJAX и Spring MVC