SQLite GROUP BY: группировка строк и агрегатные функции

Когда мне нужно получить не просто список строк, а сводную статистику — сколько треков в каждом альбоме, какова суммарная длина, где максимум — я сразу тянусь к GROUP BY. Это предложение превращает плоскую выборку в осмысленные итоги. Разберём, как оно устроено, какие агрегатные функции с ним работают и как фильтровать уже сгруппированные результаты

Как работает предложение GROUP BY

Предложение GROUP BY является необязательным предложением оператора SELECT. Оно группирует выбранный набор строк в итоговые строки по значениям одного или нескольких столбцов

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

Следующий оператор иллюстрирует синтаксис предложения SQLite GROUP BY:

SELECT column_1, aggregate_function(column_2)
FROM table
GROUP BY column_1, column_2;

Предложение GROUP BY располагается после предложения FROM оператора SELECT. Если оператор содержит предложение WHERE, предложение GROUP BY должно следовать после предложения WHERE

После предложения GROUP BY указывается столбец или список столбцов, разделённых запятыми, которые используются для определения группы

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

GROUP BY с функцией COUNT: примеры запросов

Для демонстрации используем таблицу tracks из учебной базы данных

GROUP BY с функцией COUNT

Следующий оператор возвращает идентификатор альбома и количество треков в каждом альбоме. Он использует предложение GROUP BY для группировки треков по альбому и применяет функцию COUNT() к каждой группе:

SELECT albumid, COUNT(trackid)
FROM tracks
GROUP BY albumid;

Для сортировки групп можно использовать предложение ORDER BY следующим образом:

SELECT albumid, COUNT(trackid)
FROM tracks
GROUP BY albumid
ORDER BY COUNT(trackid) DESC;

GROUP BY и предложение INNER JOIN

Можно запрашивать данные из нескольких таблиц с помощью предложения INNER JOIN, а затем использовать предложение GROUP BY для группировки строк в набор итоговых строк

Например, следующий оператор объединяет таблицу tracks с таблицей albums для получения названий альбомов и использует предложение GROUP BY с функцией COUNT для получения количества треков в каждом альбоме:

SELECT tracks.albumid, title, COUNT(trackid)
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY tracks.albumid;

Фильтрация групп с помощью HAVING

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

Например, чтобы получить альбомы, содержащие более 15 треков, используется следующий оператор:

SELECT tracks.albumid, title, COUNT(trackid)
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY tracks.albumid
HAVING COUNT(trackid) > 15;

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

Агрегатные функции SUM, MAX, MIN и AVG в GROUP BY

GROUP BY с функцией SUM

Функцию SUM можно использовать для вычисления итогов по группе. Например, чтобы получить общую длину и размер в байтах для каждого альбома, используется функция SUM для суммирования миллисекунд и байтов:

SELECT albumid, SUM(milliseconds) length, SUM(bytes) size
FROM tracks
GROUP BY albumid;

GROUP BY с функциями MAX, MIN и AVG

Следующий оператор возвращает идентификатор альбома, название альбома, максимальную длину, минимальную длину и среднюю длину треков в таблице tracks:

SELECT tracks.albumid, title, min(milliseconds), max(milliseconds), round(avg(milliseconds), 2)
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY tracks.albumid;

Функция round() здесь ограничивает среднее значение двумя знаками после запятой — это удобно при выводе результатов пользователю

Группировка по нескольким столбцам

В предыдущих примерах в предложении GROUP BY использовался один столбец. SQLite позволяет группировать строки по нескольким столбцам одновременно

Например, чтобы сгруппировать треки по типу медиа (MediaTypeId) и жанру (GenreId), используется следующий оператор:

SELECT MediaTypeId, GenreId, COUNT(TrackId)
FROM tracks
GROUP BY MediaTypeId, GenreId;

SQLite использует комбинацию значений столбцов MediaTypeId и GenreId в качестве группы, например (1,1) и (1,2). Затем применяется функция COUNT для возврата количества треков в каждой группе

Группировка по нескольким столбцам особенно полезна, когда нужно построить перекрёстную статистику — например, сколько треков каждого жанра доступно в каждом формате

Группировка по дате

Рассмотрим таблицу invoices из учебной базы данных

Следующий оператор возвращает количество счетов по годам:

SELECT STRFTIME('%Y', InvoiceDate) InvoiceYear, COUNT(InvoiceId) InvoiceCount
FROM invoices
GROUP BY STRFTIME('%Y', InvoiceDate)
ORDER BY InvoiceYear;

В этом примере:

• Функция STRFTIME('%Y', InvoiceDate) возвращает год из строки с датой
• Предложение GROUP BY группирует счета по годам
• Функция COUNT() возвращает количество счетов в каждом году (или группе)

Мне нравится этот пример тем, что он показывает: GROUP BY работает не только с именами столбцов, но и с выражениями — в данном случае с результатом функции STRFTIME

Типичные ошибки при работе с GROUP BY

При использовании GROUP BY важно помнить несколько правил, нарушение которых приводит к ошибкам или неожиданным результатам

Столбцы в SELECT без агрегации. Если в SELECT указан столбец, который не входит в GROUP BY и не обёрнут в агрегатную функцию, SQLite вернёт результат, но значение в этом столбце будет произвольным — из одной из строк группы. Другие СУБД в такой ситуации выдают ошибку

Путаница между WHERE и HAVING. WHERE не может ссылаться на результат агрегатной функции — для этого существует HAVING. Попытка написать WHERE COUNT(trackid) > 15 приведёт к ошибке

Порядок предложений. Предложения в операторе SELECT должны следовать в строгом порядке: FROM → WHERE → GROUP BY → HAVING → ORDER BY. Нарушение порядка вызывает синтаксическую ошибку

Группировка по псевдониму. В SQLite можно использовать псевдоним (alias) столбца в предложении GROUP BY, тогда как в ряде других СУБД это недопустимо. Это удобно, но снижает переносимость запроса

Когда применять GROUP BY: выбор подходящего запроса

GROUP BY оправдан, когда задача требует агрегированной статистики по категориям: подсчёт записей, суммирование значений, поиск максимумов и минимумов внутри групп. Если нужна просто уникальная выборка без агрегации — лучше использовать SELECT DISTINCT

Хороший признак правильного применения GROUP BY: в SELECT присутствует хотя бы одна агрегатная функция. Если агрегатных функций нет, стоит пересмотреть запрос

Также стоит следить за производительностью: GROUP BY на больших таблицах без индексов по группируемым столбцам может работать медленно. Индекс по столбцу из GROUP BY существенно ускоряет выполнение запроса

Ответы на эти вопросы могут быть для вас полезными

В чём разница между WHERE и HAVING при использовании GROUP BY? WHERE фильтрует строки до группировки, HAVING — после. Агрегатные функции (COUNT, SUM и другие) нельзя использовать в WHERE, только в HAVING

Можно ли группировать сразу по нескольким столбцам? Да. Достаточно перечислить столбцы через запятую в предложении GROUP BY. SQLite будет считать уникальной группой каждую уникальную комбинацию значений этих столбцов

Можно ли использовать GROUP BY без агрегатных функций? Технически да, но результат будет аналогичен SELECT DISTINCT. На практике GROUP BY используется именно в связке с агрегатными функциями

Как сгруппировать данные по году из поля с датой? Используйте функцию STRFTIME('%Y', date_column) как в предложении SELECT, так и в GROUP BY. SQLite поддерживает группировку по выражениям, а не только по именам столбцов

Почему SQLite не выдаёт ошибку, если в SELECT есть столбец не из GROUP BY? SQLite следует более мягким правилам, чем стандарт SQL: он допускает такие запросы, возвращая произвольное значение из группы. Это удобно, но может приводить к непредсказуемым результатам — лучше явно включать все негрупповые столбцы в агрегатные функции