Раздел 35 · SQLite с нуля

GROUP BY и HAVING

junior~20 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

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

Раскладываем чеки по стопкам

Вернёмся к кассиру в конце смены. Теперь начальница просит выручку не общую, а отдельно по каждому бариста. Кассир раскладывает чеки на стопки: в одну все чеки Ани, в другую чеки Гоши, в третью Тимура. Потом считает каждую стопку отдельно. Сколько стопок, столько строк в отчёте.

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

sqlite> SELECT category, count(*) AS n, avg(price) AS avg_price FROM drinks GROUP BY category;
╭──────────┬───┬───────────╮
│ category │ n │ avg_price │
╞══════════╪═══╪═══════════╡
│ другое   │ 1 │     180.0 │
│ кофе     │ 5 │     182.0 │
│ холодное │ 1 │     200.0 │
│ чай      │ 2 │     130.0 │
╰──────────┴───┴───────────╯

Четыре категории, четыре строки. В группе «чай» два напитка, но средняя цена 130: у Матча-латте цены нет, и avg, как мы уже знаем, NULL пропускает.

Правило, которое стоит запомнить сразу: в SELECT запроса с GROUP BY разумно писать только столбцы группировки и агрегатные функции. Столбец category одинаков у всех строк группы, его можно показать. А какое название напитка показать для группы из пяти кофе? Вопрос без ответа, к нему вернёмся в конце урока.

Группировка по выражению

Группировать можно не только по столбцу, но и по любому выражению. В orders время заказа записано с точностью до секунды, а нужен отчёт по дням. Функция date() отрезает время, и по её результату строки складываются в дни:

sqlite> SELECT date(ordered_at) AS day, count(*) AS orders FROM orders GROUP BY day;
╭────────────┬────────╮
│    day     │ orders │
╞════════════╪════════╡
│ 2026-10-01 │      3 │
│ 2026-10-02 │      2 │
│ 2026-10-03 │      2 │
│ 2026-10-04 │      2 │
│ 2026-10-05 │      2 │
│ 2026-10-06 │      1 │
╰────────────┴────────╯

В GROUP BY стоит псевдоним day из списка SELECT. SQLite это позволяет, и запрос читается легче. Подробно про date() и её родственников поговорим в уроке про функции и даты.

Несколько столбцов

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

sqlite> SELECT staff_id, paid, count(*) AS n FROM orders GROUP BY staff_id, paid;
╭──────────┬──────────┬───╮
│ staff_id │   paid   │ n │
╞══════════╪══════════╪═══╡
│        1 │ наличные │ 1 │
│        2 │ карта    │ 2 │
│        2 │ наличные │ 1 │
│        3 │ карта    │ 3 │
│        3 │ наличные │ 1 │
│        4 │ карта    │ 4 │
╰──────────┴──────────┴───╯

У Ольги (staff_id 1) и Гоши (staff_id 4) по одной строке: Ольга принимала только наличные, а Гоше никто не платил наличными. Группы появляются только для тех комбинаций, которые есть в данных.

WHERE фильтрует строки, HAVING фильтрует группы

Теперь два похожих вопроса. Первый: сколько в каждой категории напитков дороже 150 рублей? Это условие на отдельный напиток, его проверяют до раскладки по стопкам. Такие условия пишут в WHERE:

sqlite> SELECT category, count(*) AS n FROM drinks WHERE price > 150 GROUP BY category;
╭──────────┬───╮
│ category │ n │
╞══════════╪═══╡
│ другое   │ 1 │
│ кофе     │ 3 │
│ холодное │ 1 │
╰──────────┴───╯

Чай пропал целиком: оба чайных напитка не прошли фильтр, и для чая не осталось ни одной строки.

Второй вопрос: какие категории содержат больше одного напитка? Это условие на всю стопку, его можно проверить только после подсчёта. Для условий на группы есть HAVING:

sqlite> SELECT category, count(*) AS n FROM drinks GROUP BY category HAVING count(*) > 1;
╭──────────┬───╮
│ category │ n │
╞══════════╪═══╡
│ кофе     │ 5 │
│ чай      │ 2 │
╰──────────┴───╯

Если перепутать и положить агрегат в WHERE, SQLite откажется:

sqlite> SELECT category, count(*) AS n FROM drinks WHERE count(*) > 1 GROUP BY category;
Parse error near line 1: misuse of aggregate: count()
  SELECT category, count(*) AS n FROM drinks WHERE count(*) > 1 GROUP BY categor
                                     error here ---^

WHERE смотрит на одну строку, а count(*) одной строки не бывает. Та же история с примерами разобрана в уроке про WHERE и HAVING.

В каком порядке выполняется запрос

Запрос пишут в одном порядке, а выполняется он в другом. Этот логический порядок объясняет всё, что мы видели:

  1. FROM: берём таблицу.
  2. WHERE: выбрасываем строки, которые не подходят.
  3. GROUP BY: раскладываем оставшиеся строки по группам.
  4. HAVING: выбрасываем группы, которые не подходят.
  5. SELECT: считаем столбцы и агрегаты для каждой группы.
  6. ORDER BY и LIMIT: сортируем и обрезаем результат.

Отсюда понятно, почему агрегат нельзя в WHERE: на втором шаге групп ещё нет. И почему фильтр по строкам выгоднее держать в WHERE: меньше строк дойдёт до группировки. Тот же порядок действует в PostgreSQL и в любой другой SQL-базе, а разбор типичных ошибок с группировкой есть в уроке про GROUP BY.

Голые столбцы: причуда SQLite

Вернёмся к вопросу, который мы отложили. Что будет, если в SELECT положить столбец, которого нет в GROUP BY и который не завёрнут в агрегат? PostgreSQL ответит ошибкой. SQLite выполнит запрос. Такой столбец называют голым.

С одним max() или min() у SQLite есть гарантия: голый столбец берётся из той строки, где нашёлся максимум или минимум. Так можно узнать самый дорогой напиток в каждой категории одним запросом:

sqlite> SELECT category, name, max(price) AS top FROM drinks GROUP BY category;
╭──────────┬────────────────┬─────╮
│ category │      name      │ top │
╞══════════╪════════════════╪═════╡
│ другое   │ Какао          │ 180 │
│ кофе     │ Раф            │ 240 │
│ холодное │ Тархун         │ 200 │
│ чай      │ Чай с чабрецом │ 130 │
╰──────────┴────────────────┴─────╯

Без max() и min() гарантии нет:

sqlite> SELECT category, name FROM drinks GROUP BY category;
╭──────────┬────────────────╮
│ category │      name      │
╞══════════╪════════════════╡
│ другое   │ Какао          │
│ кофе     │ Эспрессо       │
│ холодное │ Тархун         │
│ чай      │ Чай с чабрецом │
╰──────────┴────────────────╯

Почему для кофе показан Эспрессо, а не Латте? SQLite взял одну из пяти строк, какая попалась. В другой версии или после добавления индекса строка может быть другой. Пользуйся голыми столбцами только вместе с одним max() или min(), а запрос, который должен работать и в других базах, пиши без них.

Попробуй сам

В песочнице выручка по дням: каждая строка order_items это напиток в заказе, цена берётся из drinks. Соединение таблиц ты уже знаешь по уроку про JOIN.

Убери строку с HAVING и посмотри, какие дни вернулись. Потом добавь WHERE o.paid = 'карта' перед GROUP BY и сравни выручку.

Проверь себя

Задание

Дальше

В этом уроке мы уже воспользовались date(), чтобы превратить момент заказа в день. В SQLite десятки таких функций: для строк, чисел и особенно для дат, которые здесь хранятся как текст. Их разбираем в следующем уроке вместе с CAST.

домашка

Домашка