С агрегатными функциями связано множество разных задач. Например, они помогают вывести общее число топиков для каждого пользователя. Так это может выглядеть:
SELECT COUNT(*) FROM topics WHERE user_id = 3;
SELECT COUNT(*) FROM topics WHERE user_id = 4;
-- ...
При этом здесь мы сталкиваемся с одной сложностью — невозможно выполнить данную задачу за один запрос, используя только функции. Нам придется делать выборку для каждой категории индивидуально, а это долго и неудобно. Если пользователей тысячи, то такое решение вопроса неприемлемо в принципе.
Подобные задачи возникают настолько часто, что для них существует специальная форма GROUP BY
. В этом уроке мы изучим, как работает эта функция.
GROUP BY
Эта функция группирует строки по определенному признаку и выполняет подсчеты внутри каждой группы независимо от других групп:
SELECT user_id, COUNT(*) FROM topics GROUP BY user_id;
user_id | count
---------+-------
71 | 1
80 | 1
84 | 3
92 | 1
60 | 1
97 | 2
98 | 1
44 | 1
40 | 1
43 | 1
В запросе выше мы создали группы записей по значению поля user_id
. Эти данные можно представить себе как набор виртуальных таблиц, каждая из которых содержит все записи по одному пользователю. Подсчет количества идет по каждому пользователю независимо от других. К результатам такой выборки можно применять сортировку и лимитирование:
SELECT user_id, COUNT(*) FROM topics GROUP BY user_id ORDER BY count DESC LIMIT 3;
user_id | count
---------+-------
84 | 3
97 | 2
57 | 2
С помощью сортировки мы можем обращаться не только к полям самой таблицы, но и к вычисленному значению. По умолчанию имя этого виртуального поля совпадает с именем функции, но его можно изменить с помощью механизма псевдонимов:
SELECT user_id, COUNT(*) AS topics_count
FROM topics
GROUP BY user_id
ORDER BY topics_count DESC
LIMIT 3;
Псевдонимы создаются не только для агрегатных значений, но и для любых имен в запросе. Переименовываются даже существующие поля. Общая структура имени выглядит так: <expression> AS <name>
.
У псевдонимов есть одно удобное свойство. Если определить их в одном месте, они становятся доступны в других частях SQL-запроса:
SELECT first_name AS name FROM users ORDER BY name;
Теперь попытаемся выполнить следующий запрос:
SELECT user_id, created_at, COUNT(*) AS topics_count FROM topics GROUP BY user_id;
Запрос завершится с ошибкой:
ERROR: column "topics.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT user_id, created_at, COUNT(*) AS topics_count FROM topics G...
Чтобы лучше понять работу GROUP BY
, разберемся, почему запрос выше не сработает.
Дело в том, что группировка обращается к записям в таблице и создает из них независимые группы записей, по которым проводится анализ.
Группа записей — не то же самое, что одна запись. Мы не можем просто взять и указать имя любого поля — база данных сама не выберет какое-то значение из этой группы. Такое поведение создает неоднозначность и не несет в себе смысла.
СУБД отслеживает такие ошибки и просит выполнить одно из двух действий:
Действие 1 — указать поле created_at
в выражении GROUP BY
. Тогда значение поля для каждой записи из группы будет одинаковым — в этом и суть группировки. Значит, СУБД однозначно определит, что нужно добавить в результат:
SELECT user_id, created_at, COUNT(*) AS topics_count
FROM topics
GROUP BY user_id, created_at;
user_id | created_at | topics_count
---------+-------------------------+--------------
40 | 2018-12-05 18:40:05.603 | 1
67 | 2018-12-06 05:23:40.65 | 1
Такой запрос выполнит группировку сначала по user_id
, а затем по дате создания. Даты создания у всех топиков почти наверняка уникальны, поэтому вся таблица разобьется на группы по одному элементу. Смысла в таком запросе не очень много, гораздо полезнее сделать то же самое с разбивкой по дням или месяцам. Тогда можно будет увидеть, сколько топиков создает конкретный пользователь каждый день:
-- В этом запросе используется функция EXTRACT,
-- которая извлекает значения из даты: например, номер дня или месяца
SELECT user_id, EXTRACT(day from created_at) AS day, COUNT(*) AS topics_count
FROM topics
GROUP BY user_id, day
ORDER BY user_id;
user_id | day | topics_count
--------+-----+--------------
1 | 5 | 1
1 | 6 | 1
4 | 6 | 1
6 | 5 | 1
7 | 6 | 2
8 | 5 | 1
9 | 6 | 1
Действие 2 — использовать created_at
внутри агрегатной функции. В таком случае мы получим результат на основе анализа всех значений в рамках группы. Например, добавление вызова MAX(created_at)
посчитает дату последнего добавленного топика для каждой группы:
SELECT user_id, MAX(created_at), COUNT(*) AS topics_count
FROM topics
GROUP BY user_id;
user_id | max | topics_count
--------+-------------------------+--------------
40 | 2018-12-05 18:40:05.603 | 1
67 | 2018-12-06 05:23:40.65 | 1
49 | 2018-12-06 14:55:08.99 | 1
43 | 2018-12-06 00:20:11.835 | 1
HAVING
Иногда встречаются более сложные ситуации, в которых нужно проводить анализ только по некоторым группам. Предположим, что мы хотим выбрать всех пользователей, у которых количество топиков больше одного. Эта задача сводится к поиску групп, в которых более одной записи.
Подобный запрос невозможно сделать через WHERE
, потому что эти условия применяются к записям исходной выборки, еще до создания самих групп.
В этой задаче понадобится дополнение к GROUP BY
, которое называется HAVING
:
SELECT user_id, COUNT(*) FROM topics
GROUP BY user_id
HAVING COUNT(*) > 1;
user_id | count
---------+-------
84 | 3
97 | 2
57 | 2
30 | 2
83 | 2
7 | 2
38 | 2
1 | 2
(8 rows)
Подчеркнем, что HAVING
нужен для отбора групп по какому-то агрегатному признаку — например, количеству записей в группе. Если вам надо посмотреть значение конкретного поля, используйте именно WHERE
.
Группировка — это мощный, но в то же время сложный инструмент, который помогает анализировать данные в таблицах. Не заморачивайтесь над тем, чтобы выучить группировку от и до прямо сейчас. Опытные разработчики пользуются ей не каждый день и сами постоянно подсматривают в документацию.
Важно понимать спектр задач, для которых группировка подходит, а остальное — дело техники и умения читать документацию. Это общее правило, характерное и для многих других аспектов баз данных.
Выводы
В этом уроке мы изучили форму GROUP BY
и узнали, как работает эта функция. Теперь вы лучше понимаете агрегатные функции и можете выполнять даже сложные задачи за один запрос.
Дополнительные материалы
Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты
- Статья «Как учиться и справляться с негативными мыслями»
- Статья «Ловушки обучения»
- Статья «Сложные простые задачи по программированию»
- Вебинар «Как самостоятельно учиться»