§ 6. Запросы, использующие группировку данных

Определения и обозначения

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

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

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

Условие отбора групп — условие, по которому из результата группировки выбираются группы, например группы с количеством записей больше заданного. Оно проверяет итог группы, а не каждую исходную строку. Это помогает оставить в отчёте только нужные категории, например поставщиков с большим числом поставок.

Как это устроено

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

Чтобы запрос понял, какие записи объединять, в нём указывают поле группировки. Все строки с одинаковым значением этого поля попадают в одну группу: например, записи с видом «книга» соберутся отдельно от записей с видом «тетрадь». Если выбрано несколько полей, учитывается их сочетание: разные отделы одного города будут разными группами, если указаны и отдел, и город.

После формирования групп агрегатная функция вычисляет показатель отдельно для каждой из них. COUNT считает записи или непустые значения указанного поля, SUM складывает значения, AVG находит среднее, а MIN и MAX — наименьшее и наибольшее. Различие в COUNT важно из-за пропусков: подсчёт поля не учитывает строки, где оно пустое, поэтому он может не совпасть с общим числом записей в группе.

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

Условия для записей и для групп решают разные задачи, поэтому их нельзя подменять друг другом. Условие отбора записей проверяет отдельные строки до объединения и определяет, какие данные участвуют в расчёте. Условие отбора групп действует после вычислений: например, оно оставляет только группы, в которых число заказов превышает десять.

Агрегатные функции в групповом запросе

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

Разбор задач

Количество товаров каждого вида

Условие. В таблице «Товары» нужно найти количество товаров каждого вида. Нужны отдельные результаты для разных значений поля «Вид», а не один итог по всей таблице. Поэтому сначала определим группы, а затем посчитаем записи внутри каждой из них.

1. Выбираем поле «Вид» как поле группировки, потому что именно по нему товары нужно разделить на категории.
2. Применяем COUNT к записям каждой группы, чтобы получить число товаров с данным видом.
3. В результате для каждого вида появляется отдельная строка с количеством; если COUNT применяется к полю, проверяем, нет ли в нём пропусков.

Ответ. Группируем таблицу по полю «Вид» и считаем записи в каждой группе с помощью COUNT. Так получаем количество товаров отдельно для каждого вида. Подсчёт всех строк группы не зависит от того, заполнено ли какое-либо другое поле товара.

Средняя зарплата по отделам

Условие. Для каждого отдела нужно вычислить среднюю зарплату сотрудников со стажем не менее трёх лет. Здесь заданы и ограничение на исходные записи, и способ разделить оставшиеся данные. Если сначала посчитать среднее по всем сотрудникам, результат включит неподходящих работников.

1. Сначала отбираем записи, где стаж составляет не менее трёх лет, чтобы исключить остальных сотрудников из расчёта.
2. Среди прошедших отбор записей группируем сотрудников по полю «Отдел».
3. Для каждой группы применяем AVG к полю с зарплатой; функция вычисляет среднее только по сотрудникам этого отдела, которые прошли отбор.

Ответ. Условие по стажу применяем до группировки, затем группируем записи по отделу и вычисляем AVG зарплаты. В итог попадут только сотрудники со стажем не менее трёх лет. Для каждого отдела будет выведено своё среднее значение.

Поставщики с числом поставок больше десяти

Условие. Для каждого поставщика требуется посчитать число поставленных товаров и оставить тех, у кого их больше десяти. Порог относится к результату подсчёта, а не к отдельной поставке. Значит, проверять его нужно после создания групп и вычисления COUNT.

1. Указываем поле «Поставщик» как поле группировки, чтобы собрать записи о поставках каждого поставщика.
2. Применяем COUNT к записям группы и получаем число поставленных товаров для каждого поставщика.
3. Добавляем условие отбора групп: результат подсчёта должен быть больше десяти; остальные группы исключаем из результата.

Ответ. Группируем записи по поставщику, считаем их с помощью COUNT и оставляем группы, где число больше десяти. Условие относится к уже полученному итогу. Поэтому его нельзя применять как проверку каждой исходной строки до подсчёта.

Типичные ошибки

Считать группировку сортировкой. Сортировка располагает строки в другом порядке, но не объединяет их и не вычисляет итоги. Путаница возникает потому, что обе операции могут сделать таблицу визуально удобнее. Проверьте себя: если строк столько же, сколько было, и итогов по категориям нет, выполнена только сортировка.

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

Путать условия отбора записей и групп. Первое условие ограничивает строки до вычислений, второе проверяет итог группы после агрегирования. Их путают, если не уточнить, к чему относится условие: к каждому объекту или к общей сумме либо количеству. Для проверки проговорите порядок: сначала отбор строк, затем группировка и подсчёт, потом отбор групп.

Считать COUNT по полю с пропусками как число всех записей. При подсчёте значений поля незаполненные ячейки могут не учитываться, поэтому итог окажется меньше количества строк в группе. Ошибка возникает, когда забывают, что COUNT может считать не только записи, но и непустые значения. Сравните результат с числом строк и проверьте, заполнено ли поле в каждой из них.

Связь с другими темами

Для работы с групповыми запросами понадобятся понятия таблицы, записи и поля: именно из строк и столбцов складываются исходные данные. Также нужно уметь задавать условия отбора записей, чтобы ограничивать таблицу до группировки. Чтение простых запросов помогает понять, какие поля и операции участвуют в получении результата.

Группировка полезна при создании сводных отчётов и анализе больших таблиц. С её помощью считают объекты по категориям, находят суммы и средние значения, сравнивают группы. Это превращает длинный перечень записей в компактную сводку, которую проще использовать для выводов.

Сортировка, фильтрация и группировка могут применяться вместе, но выполняют разные задачи. Фильтрация отбирает нужные записи, группировка объединяет их для подсчёта итогов, а сортировка упорядочивает результат. Понимание различий помогает выстроить запрос в нужной последовательности.

Что запомнить

• Группировка объединяет записи с одинаковыми значениями выбранного поля или сочетания полей.
• Агрегатная функция вычисляет итог отдельно для каждой группы.
• COUNT считает записи или непустые значения — в зависимости от того, что передано функции.
• SUM, AVG, MIN и MAX применяются к числовым или упорядочиваемым значениям соответствующего поля.
• Условие отбора записей ограничивает исходные данные до группировки.
• Условие отбора групп проверяет результаты группировки и агрегирования.
• Сортировка упорядочивает результат, но сама по себе не создаёт группы.