Группировка данных (GROUP BY): синтаксис и структура

Представьте, что вы работаете с тысячами строк данных — как найти в них смысл? Тут на сцену выходит оператор GROUP BY — настоящий дирижёр в оркестре SQL. Он берёт разрозненные данные и превращает их в чёткую мелодию: считает, группирует, подводит итоги. Хотите узнать, сколько заказов сделал каждый клиент, сколько студентов на каждом курсе или как распределяются зарплаты по отделам? Всё это — работа GROUP BY. Сегодня мы разберёмся, как с ним подружиться и извлекать максимум пользы из ваших таблиц.

Группировка — это процесс объединения строк, которые имеют одинаковые значения в одном или нескольких столбцах, в логические группы. Она позволяет применять агрегатные функции отдельно к каждой группе.

Представьте, что у вас есть таблица сотрудников, и вы хотите узнать среднюю зарплату по каждому отделу. Один отдел — одна группа. SQL использует GROUP BY, чтобы разделить таблицу сотрудников на группы по отделам, а затем применяет AVG() к каждой группе.

Синтаксис GROUP BY

Главное правило использования группировки в SQL: если вы используете GROUP BY, то каждый столбец, который не является частью агрегатной функции, должен быть указан в GROUP BY.

Синтаксис:

SELECT столбец1, 
       агрегатная_функция(столбец2)
FROM таблица
GROUP BY столбец1;

Шаги работы:

1. Укажите столбец, по которому хотите сгруппировать данные, в GROUP BY.
2. Используйте агрегатные функции для вычисления значений в группах.
3. Все столбцы в SELECT, но не входящие в агрегатные функции, должны быть перечислены в GROUP BY. Да, SQL строг в этом плане, и если вы забудете, он напомнит вам об этом ошибкой.

Пример: Группировка студентов по факультетам

Допустим, у нас есть таблица students, которая хранит данные о студентах:

Теперь мы хотим узнать средний балл (GPA) по каждому факультету. Для этого напишем запрос с GROUP BY:

SELECT faculty, AVG(gpa) AS avg_gpa
FROM students
GROUP BY faculty;

Результат:

SQL сначала разделил данные на группы по значению столбца faculty, а затем применил функцию AVG() к каждой группе.

Особенности работы с GROUP BY

1. Требования к столбцам в SELECT

SQL требует, чтобы все столбцы, которые вы указываете в SELECT, но не используете в агрегатных функциях (например, SUM(), COUNT()), были упомянуты в GROUP BY. Это связано с тем, что без группировки SQL не может понять, какие значения выводить.

Попробуйте выполнить следующий запрос и получите ошибку:

SELECT name, AVG(gpa)
FROM students
GROUP BY faculty;

Ошибка: столбец name не указан в GROUP BY. Чтобы исправить это, добавьте name в GROUP BY:

SELECT name, AVG(gpa)
FROM students
GROUP BY faculty, name;

Но это уже сгруппирует данные на уровне конкретных студентов — не совсем то, что нам нужно было изначально.

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

Вы можете группировать данные не только по одному столбцу, но и по нескольким. Например, в дополнение к факультетам хотим группировать студентов по их именам. Просто добавьте второй столбец в GROUP BY:

SELECT faculty, name, AVG(gpa) AS avg_gpa
FROM students
GROUP BY faculty, name;

Изначальная таблица:

Результат:

3. Группировка с использованием нескольких агрегатных функций

Не ограничивайте себя только одной функцией! Например, хотим подсчитать количество студентов на факультете и вычислить средний балл:

SELECT faculty,
       COUNT(*) AS student_count, 
       AVG(gpa) AS avg_gpa
FROM students
GROUP BY faculty;

Исходная таблица:

Результат:

Особенности группировки в SQL: что можно и что нельзя выбирать

Запросы с группировкой писать легко, но скорее всего половина ваших запросов работать не будет. Группировка происходит немного не так, как мы привыкли делать у себя в голове.

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

• вычисляться агрегатными функциями на основе колонок группы.
• браться из GROUP BY - по ним должна быть группировка.

Если вы выполняете GROUP-BY-запрос по таблице студентов, то в результирующей таблице у вас не может быть конкретных студентов! У вас может быть средний рост, средний вес, средний бал. Такой код работать не будет:

SELECT faculty, name
FROM students
GROUP BY faculty;

Давайте попробуем разобраться почему.

Оператор GROUP BY faculty разобьет студентов таблицы students на группы с одинаковым faculty у каждой группы. Т.к. у всех студентов группы одинаковый faculty, то можно сказать, что атрибут faculty есть у группы студентов. Но name у всех студентов разные. Поэтому атрибута name у группы нет.

Оператор GROUP BY faculty, gender разобьет студентов таблицы students на группы с одинаковым faculty и gender у каждой группы. Поэтому у всех студентов каждой группы будут одинаковые faculty и gender. Поэтому можно сказать, что у самой группы студентов есть атрибуты faculty и gender. Но единого name у группы по прежнему нет.

Так писать можно:

SELECT faculty, gender
FROM students
GROUP BY faculty, gender;

Можно даже так:

SELECT
    faculty, 
    gender, 
    AVG(age) as group_avg_age,  -- Значение вычисляется на основе age значений группы студентов
    MAX(high) as group_high     -- Значение вычисляется на основе high значений группы студентов
FROM students
GROUP BY faculty, gender;

Но просто использовать age и high в SELECT в нашем случае нельзя.

Типичные ошибки при использовании GROUP BY

Когда вы начнёте писать запросы с GROUP BY, вот несколько подводных камней, которые могут встретиться на пути:

1. Указаны не все столбцы в SELECT. Помните, каждый столбец, который не является агрегатом, должен быть указан в GROUP BY. Иначе SQL не поймёт, как его отображать.

2. Группировка по NULL. NULL значения считаются отдельной группой. Если в вашем столбце есть NULL, SQL создаст группу для NULL.

3. Избыточные группы. Если вы случайно добавите слишком много столбцов в GROUP BY, вы можете получить слишком детализированные результаты, которые сложнее анализировать.

Теперь вы знаете как эффективно группировать данные с помощью GROUP BY. Это один из самых мощных инструментов в SQL, который позволяет легко работать с агрегированными данными и создавать структурированные отчёты. Далее мы продолжим углубляться в магию группировки и узнаем, как настраивать дополнительные фильтры с помощью HAVING.

2

Задача

SQL SELF, 8 уровень, 0 лекция

Недоступна

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

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

Открыть

2

Задача

SQL SELF, 8 уровень, 0 лекция

Недоступна

Подсчёт количества продуктов по категориям

Подсчёт количества продуктов по категориям

Открыть