Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Группировка по выборке Основы SQL

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

Сначала выберем уникальных пользователей в таблице 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;
Как работает group by
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 или применять к ним агрегатную функцию.


Дополнительные материалы

  1. GROUP BY

Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ
1000
упражнений
2000+
часов теории
3200
тестов

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы
профессия
от 25 000 ₸ в месяц
Разработка веб-приложений на Django
10 месяцев
с нуля
Старт 28 ноября
профессия
от 14 960 ₸ в месяц
Ручное тестирование веб-приложений
4 месяца
с нуля
Старт 28 ноября
профессия
от 25 000 ₸ в месяц
Разработка приложений на языке Java
10 месяцев
с нуля
Старт 28 ноября
профессия
от 24 542 ₸ в месяц
новый
Сбор, анализ и интерпретация данных
9 месяцев
с нуля
Старт 28 ноября
профессия
от 25 000 ₸ в месяц
Разработка веб-приложений на Laravel
10 месяцев
с нуля
Старт 28 ноября
профессия
от 39 525 ₸ в месяц
Разработка фронтенд- и бэкенд-компонентов для веб-приложений
16 месяцев
с нуля
Старт 28 ноября
профессия
от 25 000 ₸ в месяц
Разработка бэкенд-компонентов для веб-приложений
10 месяцев
с нуля
Старт 28 ноября
профессия
новый
Автоматизированное тестирование веб-приложений на JavaScript
8 месяцев
c опытом
Старт 28 ноября

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»