Агрегатные функции часто используются в запросах. Например, мы можем подсчитать количество курсов, на которые записался каждый пользователь.
Сначала выберем уникальных пользователей в таблице course_members:
SELECT DISTINCT user_id
FROM course_members
ORDER BY user_id;| user_id |
|---|
2 |
3 |
4 |
5 |
9 |
… |
View On DB Fiddle
Затем для каждого пользователя подсчитаем количество курсов. Запросы будут выглядеть так:
SELECT COUNT(*) FROM course_members WHERE user_id = 2;
SELECT COUNT(*) FROM course_members WHERE user_id = 3;
-- И так далее для остальных пользователейView On DB Fiddle
Проблема в том, что в нашей таблице слишком много пользователей. Писать отдельный запрос для каждого слишком долго, а еще есть вероятность кого-то пропустить.
Справиться с этой сложностью помогает группировка данных, которую мы изучим в этом уроке.
Оператор GROUP BY
Группировка данных позволяет объединить одинаковые значения в заданных полях в группы, а затем выполнять подсчеты для каждой группы.
Для группировки данных существует специальный оператор GROUP BY. Запрос с подсчетом курсов каждого пользователя будет выглядеть так:
SELECT
user_id,
COUNT(*)
FROM course_members
GROUP BY user_id
ORDER BY user_id;Мы могли бы создавать по отдельному запросу для каждого пользователя, но вместо этого использовали конструкцию GROUP BY user_id. В ней мы указали, что нам нужно объединить строки с одинаковыми идентификаторами user_id, вывести идентификатор и количество строк в каждой группе COUNT(*).
Для удобства мы отсортировали данные по идентификатору, но это необязательно:
| user_id | count |
|---|---|
2 |
1 |
3 |
1 |
4 |
1 |
5 |
3 |
9 |
1 |
View On DB Fiddle
Псевдонимы для столбцов
Система управления базами данных автоматически присвоила нашему второму столбцу имя count, совпадающее с именем функции. Но мы можем переименовать этот столбец, как и любой другой:
SELECT
user_id AS student,
COUNT(*) AS courses_count
FROM course_members
GROUP BY user_id
ORDER BY user_id;| student | courses_count |
|---|---|
2 |
1 |
3 |
1 |
4 |
1 |
5 |
3 |
9 |
1 |
… |
View On DB Fiddle
Чтобы присвоить столбцу псевдоним, нужно после его определения записать AS и указать желаемое имя. Оно должно начинаться с буквы и не должно содержать пробелов.
Как работает GROUP BY
Теперь попытаемся выполнить следующий запрос:
SELECT
user_id AS student,
created_at,
COUNT(user_id) AS courses_count
FROM course_members
GROUP BY user_id
ORDER BY user_id;Запрос завершится с ошибкой:
Query Error: error: column "course_members.created_at" must appear in the GROUP BY clause
or be used in an aggregate functionОшибка в том, что СУБД не понимает, что делать со столбцом created_at — либо он должен быть в конструкции GROUP BY, либо к нему надо применить агрегатную функцию.
Чтобы лучше понять работу GROUP BY, разберемся, почему запрос выше не работает.
Дело в том, что группировка обращается к записям в таблице. Она создает из них независимые группы записей, по которым проводится анализ.
При работе с группой мы можем выбрать один из двух вариантов:
-
Либо вывести поле, по которому проводим группировку. Его значение будет одинаковым для группы
-
Либо применить к полю какую-либо агрегатную функцию — например,
COUNT(),MAX(),MIN()илиAVG(). В этом случае СУБД будет знать, как обработать несколько разных значений.
Вернемся к примеру выше. В своем запросе мы не группируем данные по столбцу created_at — это не то, что нам нужно. Если бы мы это сделали, пришлось бы работать с группами, в которых совпадают идентификатор пользователя и дата создания.
Кроме того, мы не указали никакой агрегатной функции для этого столбца. СУБД не понимает, что именно нужно сделать с группой разных дат создания. Нужно посчитать среднее? Или взять максимальную дату?
Давайте исправим запрос. Выведем максимальное значение поля created_at для группы. Это будет дата последней регистрации пользователя на курс:
SELECT
user_id AS student,
COUNT(user_id) AS courses_count,
MAX(created_at) AS last_reg
FROM course_members
GROUP BY user_id
ORDER BY user_id;
| student | courses_count | last_reg |
|---|---|---|
2 |
1 |
2022-06-14T12:23:25.912Z |
3 |
1 |
2022-06-14T18:18:09.144Z |
4 |
1 |
2022-06-14T11:36:10.374Z |
5 |
3 |
2022-06-14T19:19:46.007Z |
9 |
1 |
2022-06-14T19:19:46.007Z |
… |
View on DB Fiddle
Выводы
В этом уроке мы изучили оператор GROUP BY. Теперь вы можете объединять одинаковые записи в группы и считать для этих групп агрегатные функции.
Важно помнить, что поля, которые вы будете выводить в запросе, нужно указывать в операторе GROUP BY или применять к ним агрегатную функцию.
Дополнительные материалы
Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты
- Статья «Как учиться и справляться с негативными мыслями»
- Статья «Ловушки обучения»
- Статья «Сложные простые задачи по программированию»
- Вебинар «Как самостоятельно учиться»
Для полного доступа к курсу нужен базовый план
Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.
