GROUP BY, HAVING и UNION в SQL: примеры и отличия

GROUP BY и агрегатные функции

Агрегатная функция считает одно значение по многим строкам. COUNT считает количество, SUM сумму, AVG среднее, MIN и MAX наименьшее и наибольшее значение. Без GROUP BY функция считает по всей таблице, а с GROUP BY отдельно по каждой группе строк, у которых совпадает значение в указанной колонке.

Например, в базе SQL-песочницы запрос SELECT status, COUNT(*) AS orders_count FROM orders GROUP BY status ORDER BY orders_count DESC; показывает, сколько заказов в каждом статусе. Оплаченных (paid) девять, ожидающих оплаты (pending) и отменённых (cancelled) по два, неудачный (failed) один. AS задаёт колонке псевдоним, и результат проще читать.

Колонка в SELECT с GROUP BY должна стоять либо в самом GROUP BY, либо внутри агрегатной функции. MySQL 8 по умолчанию отвечает на нарушение этого правила ошибкой 1055, а SQLite выполняет такой запрос и берёт значение из любой строки группы. Поэтому запрос, который прошёл в песочнице, на MySQL может упасть. Порядок групп без ORDER BY тоже не обещан, и в разных базах он разный.

• СтатьяЗапросы SELECT: WHERE, ORDER BY и LIMIT

HAVING и чем он отличается от WHERE

HAVING задаёт условие для групп, которые собрал GROUP BY, и в этом условии стоит агрегатная функция. WHERE её не принимает, потому что работает раньше: он отбирает отдельные строки до группировки, и считать итог ещё не по чему. Запрос с COUNT(*) в WHERE заканчивается ошибкой, в MySQL это Invalid use of group function.

Правило такое: если в условии есть агрегатная функция, нужен HAVING, а если её нет, условие пишут в WHERE. В одном запросе они работают вместе. Части записывают в порядке SELECT, FROM, WHERE, GROUP BY, HAVING, и в конце может стоять ORDER BY.

В уроке я показываю HAVING на учебной базе sakila, которую MySQL Installer ставит вместе с примерами. Из таблицы платежей payment запрос отбирает в WHERE платежи после заданной даты, группирует их по номеру аренды rental_id и в HAVING оставляет аренды, где SUM(amount) не меньше заданной суммы.

Выполняет запрос база в другом порядке, чем он записан, и из этого порядка видно, почему агрегат нельзя поставить в WHERE.

• 1. FROM и JOIN

  Сначала собирается набор строк из таблиц.

• 2. WHERE

  Отсекает строки, которые не подходят под условие. Групп и итогов на этом шаге ещё нет.

• 3. GROUP BY

  Собирает оставшиеся строки в группы.

• 4. HAVING

  Отсекает группы по условию с агрегатной функцией.

• 5. SELECT и ORDER BY

  Вычисляет колонки результата и сортирует его. Поэтому псевдоним из SELECT нельзя использовать в WHERE, а в ORDER BY можно.

Пример: ищем двойное списание

В базе SQL-песочницы есть обращение в поддержку "Списали деньги дважды". Проверить его можно запросом с GROUP BY и HAVING: сгруппировать платежи по заказам и найти заказы, где успешных платежей больше одного. Все запросы ниже выполняются в песочнице как есть.

• 1. Посчитать платежи по заказам

  SELECT order_id, COUNT(*) AS payments_count FROM payments GROUP BY order_id HAVING COUNT(*) > 1; Находятся два заказа, 107 и 111, и у каждого по два платежа.

• 2. Оставить только успешные платежи

  SELECT order_id, COUNT(*) AS payments_count FROM payments WHERE status = 'success' GROUP BY order_id HAVING COUNT(*) > 1; Условие WHERE status = 'success' отсекает строки до группировки, и заказ 107 пропадает: оба его платежа неудачные, это была повторная попытка оплатить. Остаётся заказ 111 с двумя успешными платежами на одну и ту же сумму.

• 3. Сверить с поддержкой и ошибками

  По заказу 111 в таблице support_tickets два открытых обращения, а в app_errors две ошибки "Повторная отправка платежа". Номер заказа, запрос и найденные строки идут в баг-репорт.

• 4. Сумма по покупателям

  SELECT customer_id, SUM(amount) AS total FROM orders WHERE status = 'paid' GROUP BY customer_id HAVING SUM(amount) > 4000; Оплаченных заказов больше чем на 4000 у двух покупателей: у первого на 4580, у шестого на 7750.

• ПесочницаПопробовать запросы прямо в браузере, без регистрации
• ПесочницаВыполнить в песочнице запрос про двойное списание

UNION, UNION ALL и NULL

UNION ставит результаты двух SELECT друг под другом в одну таблицу. Путать его с JOIN не стоит. JOIN приклеивает к строкам одной таблицы колонки другой по общему полю, а UNION добавляет строки одного запроса под строки другого, и общее поле ему не нужно. Так собирают в один список строки по разным условиям или из похожих таблиц, например для отчёта или проверки тестовых данных.

Обычный UNION работает как DISTINCT и убирает повторяющиеся строки. Чтобы получить все строки вместе с повторами, пишут UNION ALL. В уроке я объединяю два запроса, которые находят один и тот же адрес, и UNION выводит его один раз, а UNION ALL дважды.

Чтобы запросы склеились, у них должно быть одинаковое число колонок, в том же порядке и с совместимыми типами: под числом число, под текстом текст. Если колонок разное количество, запрос не выполнится. Названия колонок результата берутся из первого SELECT, а ORDER BY пишут один раз в конце, и он сортирует весь результат.

Пример на базе песочницы ищет заказы, которые попадали в ошибки приложения или в обращения поддержки. SELECT order_id FROM app_errors WHERE order_id IS NOT NULL UNION SELECT order_id FROM support_tickets WHERE order_id IS NOT NULL; UNION вернёт восемь номеров заказов. UNION ALL на тех же данных вернёт 16 строк, потому что заказ 111, например, встречается в двух таблицах четыре раза.

Условие IS NOT NULL здесь нужно, потому что у части записей номера заказа нет. NULL означает, что значения нет вовсе, и это не то же самое, что пустая строка или пробел. Найти такие записи можно только через IS NULL и IS NOT NULL. Условие order_id = NULL не находит ничего, потому что сравнение с NULL не даёт ни истины, ни лжи.

К NULL стоит относиться как к подсказке. Если пользователь заполнил поле в форме, а в базе в этой колонке NULL, данные потерялись по дороге с фронтенда, и это баг. Прежде чем идти с ним к разработчику, стоит исследовать данные в базе и описать дефект подробно.

• СтатьяJOIN в SQL: INNER, LEFT и RIGHT на примерах

Импорт и экспорт базы в MySQL Workbench

Дамп – это слепок базы данных в файле, из которого её можно восстановить на другом компьютере. В работе он встречается, когда заказчик присылает копию своей базы, чтобы тестировщик воспроизвёл проблему у себя. Чувствительные данные из такой копии обычно удаляют, а часть заказчиков не присылает дамп вовсе.

Весной 2023 года я столкнулся с ошибкой в Workbench 8.0.30. Если в системе стоит русская локаль, программа не подключается к локальному серверу, а после обходного решения пропадает панель администрирования вместе с импортом и экспортом. Тогда я советовал скачать на странице загрузки, во вкладке Archives, одну из ранних версий 8.0, например 8.0.15. Если ваша версия подключается к серверу и показывает вкладку Administration, менять её не нужно.

Сообщение об ошибке в SQL почти всегда говорит, что не так и в какой строке. Ошибка синтаксиса обычно означает опечатку, забытую точку с запятой или незакрытую скобку, а Workbench подчёркивает такое место ещё до запуска. Сначала стоит 10–20 минут поискать решение самому, на форумах и в открытом баг-трекере MySQL, и только потом спрашивать опытного коллегу или ментора.

• Экспорт

  На вкладке Administration открыть Data Export, выбрать базу, указать папку для файла и нажать Start Export.

• Импорт

  Там же открыть Data Import/Restore, выбрать файл или папку с дампом и базу, в которую его загрузить, и нажать Start Import. Дамп лучше загружать в новую базу с отдельным именем. Когда импорт закончится, она появится на вкладке Schemas.

• Из командной строки

  Дамп делает утилита mysqldump: mysqldump -u root -p shop > shop.sql. Обратно в существующую базу shop его загружает клиент mysql: mysql -u root -p shop < shop.sql.

Видеоурок, песочница и задания

Этот урок я записал в 2023 году как дополнение к блоку о MySQL в курсе "Тестировщик с нуля" на YouTube. В нём разобраны NULL, UNION, HAVING, импорт и экспорт, а GROUP BY идёт как часть HAVING. В бесплатном базовом курсе по тестированию есть урок на эту тему, и после теории в нём идут задания.

SQL-базы и доступ к ним есть не на каждом проекте, но тестировщику стоит этот доступ просить: проверки на уровне данных находят дефекты, которых не видно во фронтенде. Запросы этой страницы можно выполнить в SQL-песочнице, шпаргалка по SQL разбирает GROUP BY, HAVING и порядок выполнения запроса, а в тренажёре запросы пишут на живых данных.

• Урок курсаЭтот урок с заданиями – бесплатно
• Мой видеоурокимпорт и экспорт данных, операторы HAVING и UNION
• ПесочницаПопробовать запросы прямо в браузере, без регистрации
• ШпаргалкаSQL, язык запросов
• ТренажёрSQL-запросы
• СтатьяJOIN в SQL: INNER, LEFT и RIGHT на примерах
• ПесочницаSQL для тестировщика: зачем он нужен

Курсы по теме

• бесплатный

  Тестирование ПО с нуля. Теория + Практика. Базовый уровень

  Первое рабочее место тестировщика

  89 уроков113 441
• платный

  Рабочий день тестировщика: тренажер по SQL

  Один рабочий день с SQL

  43 урока205

Вопросы и ответы