Вопросы по теме «Агрегатные функции, GROUP BY и HAVING в SQL»

Всего: 7 вопросов

1. 

Как работают агрегатные функции с GROUP BY и чем HAVING отличается от WHERE?

Агрегатные функции COUNT, SUM, AVG, MIN, MAX вычисляют одно значение по набору строк. Все они пропускают NULL, кроме COUNT(*), который считает строки целиком (COUNT(1) ему равнозначен). На пустой выборке COUNT возвращает 0, а SUM, AVG, MIN, MAX — NULL.

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

SELECT shop, SUM(kg) AS total_kg
FROM sales
WHERE product <> 'яблоки'   -- отбор строк до группировки
GROUP BY shop
HAVING SUM(kg) > 30           -- отбор групп после группировки
ORDER BY shop;
  • WHERE фильтрует строки до группировки, агрегатные функции в нём запрещены (ERROR 1111: Invalid use of group function). Условия на обычные столбцы пишут здесь: в группировку попадёт меньше строк, может использоваться индекс.
  • HAVING фильтрует группы после GROUP BY и может использовать агрегаты. Без GROUP BY он решает, вернуть ли единственную группу.
  • В SELECT с GROUP BY допустимы только столбцы группировки и агрегаты. Иначе при ONLY_FULL_GROUP_BY (по умолчанию с MySQL 5.7.5) — ERROR 1055; исправление: добавить столбец в GROUP BY, обернуть в агрегат или в ANY_VALUE().
  • С MySQL 8.0 GROUP BY не сортирует результат неявно — нужен ORDER BY.
2. 

Чем отличаются COUNT(*), COUNT(столбец) и COUNT(DISTINCT столбец) и что возвращают агрегатные функции на пустой выборке?

  • COUNT(*) считает строки, в том числе строки с NULL; COUNT(1) ему равнозначен и не быстрее.
  • COUNT(столбец) считает значения, не равные NULL: для price в sales — 6 из 8. Для столбца NOT NULL, например id, результат совпадает с COUNT(*).
  • COUNT(DISTINCT столбец) считает различные значения без NULL: COUNT(DISTINCT shop) = 3, COUNT(DISTINCT product) = 4. COUNT(DISTINCT shop, product) = 8 — несколько столбцов внутри это расширение MySQL; в PostgreSQL пишут COUNT(DISTINCT (shop, product)).

Если под условие не подошла ни одна строка, COUNT возвращает 0, а SUM, AVG, MIN, MAX — NULL:

SELECT COUNT(*), SUM(kg) FROM sales WHERE shop = 'Пятёрочка';  -- 0, NULL

В Java COUNT читают через rs.getLong(): MySQL возвращает BIGINT, и (Integer) rs.getObject(1) бросит ClassCastException. rs.getInt() для NULL вернёт 0, поэтому «продаж нет» от «продали 0 кг» отличают через rs.wasNull() или COALESCE(SUM(kg), 0) в запросе.

3. 

Как работает GROUP BY по нескольким столбцам и выражениям, куда попадают NULL и в каком порядке выводятся группы?

При группировке по нескольким столбцам группа — уникальная комбинация их значений. В sales каждая пара «магазин — продукт» встречается один раз, поэтому GROUP BY shop, product даст 8 групп по одной строке. В GROUP BY можно писать и выражения:

SELECT shop, price IS NULL AS no_price, COUNT(*) AS positions, SUM(kg) AS total_kg
FROM sales
GROUP BY shop, price IS NULL
ORDER BY shop, no_price;

Получится 5 групп: АТБ с ценой (2 позиции, 24 кг) и без (1, 20), Брусничка (2, 68), Дигма с ценой (2, 54) и без (1, 34) — в сумме те же 200 кг.

  • NULL: для GROUP BY все NULL равны и образуют одну группу (в WHERE NULL = NULL не истинно); по возрастанию MySQL ставит её первой.
  • Порядок групп: MySQL 5.7 неявно сортировал по столбцам GROUP BY, с MySQL 8.0 сортировки нет — нужен явный ORDER BY.
  • Сортировка по агрегату: ORDER BY выполняется после SELECT, поэтому в нём доступны агрегат и его псевдоним: GROUP BY product ORDER BY total_kg DESC LIMIT 1 вернёт лидера — яблоки, 84 кг.
4. 

Как добавить итоговую строку к результату GROUP BY с помощью WITH ROLLUP и зачем нужна функция GROUPING()?

Модификатор WITH ROLLUP добавляет после групп строку с общим итогом; в столбце группировки у неё стоит NULL. Функция GROUPING(shop) (MySQL 8.0.1+) возвращает 1 именно для итоговой строки, и её можно подписать:

SELECT IF(GROUPING(shop), 'Итого', shop) AS shop_name,
       SUM(kg) AS total_kg
FROM sales
GROUP BY shop WITH ROLLUP;   -- АТБ 44, Брусничка 68, Дигма 88, Итого 200
  • COALESCE(shop, 'Итого') вместо GROUPING() подходит, только если в самом столбце нет NULL: иначе обычная NULL-группа тоже получит подпись «Итого».
  • До MySQL 8.0.12 WITH ROLLUP нельзя было сочетать с ORDER BY.
  • Запись WITH ROLLUP есть только в MySQL и MariaDB; PostgreSQL, Oracle и SQL Server используют стандартную форму GROUP BY ROLLUP(shop).
5. 

Какие столбцы можно указать в SELECT вместе с GROUP BY и как исправить ошибку 1055 (ONLY_FULL_GROUP_BY)?

В списке SELECT запроса с GROUP BY могут стоять только столбцы группировки и агрегатные функции. Запрос SELECT shop, product, SUM(kg) FROM sales GROUP BY shop; падает с ERROR 1055 ... incompatible with sql_mode=only_full_group_by: в группе АТБ три продукта, и сервер не выбирает один наугад. Режим включён по умолчанию с MySQL 5.7.5; раньше такой запрос возвращал продукт из произвольной строки группы.

Три способа исправить:

  • добавить столбец в GROUP BY;
  • обернуть его в агрегат: MAX(product) или GROUP_CONCAT(product ORDER BY product SEPARATOR ', ') — все продукты группы одной строкой (у АТБ «виноград, груши, яблоки»);
  • явно согласиться на любое значение: ANY_VALUE(product).

Результат GROUP_CONCAT обрезается до group_concat_max_len (по умолчанию 1024 байта) с предупреждением, а не с ошибкой. ERROR 1055 не возникает и при функциональной зависимости: при GROUP BY id по первичному ключу в SELECT можно перечислить любые столбцы этой таблицы.

6. 

Как вывести строку с максимальным значением в каждой группе, если MAX с GROUP BY возвращает только число?

MAX(kg) с GROUP BY shop вернёт только число, а добавить product в SELECT нельзя — будет ERROR 1055. Нужен подзапрос, который находит максимум каждой группы, и отбор строк по паре «группа — максимум»:

SELECT shop, product, kg
FROM sales
WHERE (shop, kg) IN (SELECT shop, MAX(kg) FROM sales GROUP BY shop);

Результат — четыре строки: виноград АТБ (20), яблоки «Бруснички» (54) и две строки «Дигмы» — виноград и лимоны по 34 кг. При одинаковом максимуме в группе возвращаются все такие строки.

В MySQL 8.0+ ту же задачу решают оконной функцией ROW_NUMBER() OVER (PARTITION BY shop ORDER BY kg DESC): она нумерует строки внутри каждого магазина и позволяет оставить ровно одну строку на группу.

7. 

Чем GROUP BY отличается от DISTINCT, как найти дубликаты с помощью HAVING и можно ли использовать HAVING без GROUP BY?

  • GROUP BY и DISTINCT. Без агрегатных функций результат одинаковый: SELECT DISTINCT shop FROM sales и SELECT shop FROM sales GROUP BY shop вернут три магазина, и MySQL часто выполняет их одинаково. DISTINCT только убирает повторы строк результата, а GROUP BY нужен, чтобы посчитать что-то по каждой группе и отфильтровать группы через HAVING.
  • Дубликаты. Группируют по столбцам, которые должны быть уникальными, и оставляют группы, где больше одной строки: SELECT product, COUNT(*) FROM sales GROUP BY product HAVING COUNT(*) > 1; вернёт яблоки (3), виноград (2) и лимоны (2). GROUP_CONCAT(id) покажет id повторяющихся строк.
  • Агрегат в HAVING не обязан стоять в SELECT: SELECT shop FROM sales GROUP BY shop HAVING COUNT(*) = 3; вернёт АТБ и «Дигму».
  • HAVING без GROUP BY. Вся выборка — одна группа, и HAVING решает, вернуть ли её: SELECT SUM(kg) FROM sales HAVING SUM(kg) > 100; вернёт 200, при условии > 500 — пустой результат. Условия на обычные столбцы пишут в WHERE.
Страница 1 из 1