Вопросы по теме «Агрегатные функции, GROUP BY и HAVING в SQL»
Сначала изучите урок: Агрегатные функции, GROUP BY и HAVING в SQL
Всего: 7 вопросов
1. Как работают агрегатные функции с GROUP BY и чем HAVING отличается от WHERE?
Как работают агрегатные функции с 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(*), 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 и в каком порядке выводятся группы?
Как работает 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равны и образуют одну группу (вWHERENULL = 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()?
Как добавить итоговую строку к результату 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 и как исправить ошибку 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 с 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, как найти дубликаты с помощью 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.