Групповые операции

Запросы позволяют не только отбирать нужную информацию из таблиц и представлять её путём создания вычисляемых полей, но и производить группировку записей и статистические (итоговые) вычисления по их подмножествам. Всё вместе это называется групповыми операциями. Именно наличие групповых операций определяет, что перед нами сложный запрос на выборку.

Сложные запросы создают на основе известного нам бланка запроса, только теперь в нем появляется дополнительная строка – Групповая операция (ГО). Для добавления этой строки в бланк надо активизировать команду Групповые операции.

Потом в тех полях, по которым производится группировка, надо в строке ГО выбрать из списка соответствующую операцию Группировка. В тех полях, по которым следует провести статистические вычисления, надо выбрать одну из статистических операций (Sum, Avg, Min, Max, Count).

Рассмотрим подробно основные из групповых операций.

- Группировка

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

Рассмотрим группировку на примере таблицы Сотрудники (главная таблица варианта №3, см. приложение). Если сгруппировать записи только по полю НазвОтдела, то мы получим в итоге следующий набор записей:

Название отдела

Сбыт

Маркетинг

Склад

Как мы видим, из 5 записей осталось 3, поскольку в таблице у нас только 3 различных отдела.

Если сгруппировать записи по полям НазвОтдела и РабТелефон, то мы получим уже следующее:

Название отдела	Рабочий телефон
Сбыт	346-52-14
Маркетинг	346-99-85
Склад	257-42-23
Сбыт	346-52-15

Только на складе были 2 сотрудника с одинаковым номером телефона. Во всех остальных случаях пара значений НазвОтдела-РабТелефон была различной.

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

- Sum

Представляет собой сумму по сгруппированному набору записей. В случае таблицы Сотрудники вычислим общий размер зарплаты по каждому отделу. БЗ примет вид

Поле:	НазвОтдела	Зарплата
…	…	…
ГО:	Группировка	Sum

В результате получим

Название отдела	Sum-Зарплата
Сбыт	39000,00р.
Маркетинг	44732,50р.
Склад	26570,15р.

- Avg

Вычисляет среднее значение по записям (имеется в виду среднее арифметическое). Рассчитаем среднюю зарплату сотрудников по отделам и получим

Название отдела	Avg-Зарплата
Сбыт	19500,00р.
Маркетинг	44732,50р.
Склад	13285,08р.

- Min, Max

Выдаёт минимальное и максимальное значение поля в сгруппированных записях. Например, минимальная зарплата в отделах будет следующей

Название отдела	Min-Зарплата
Сбыт	14000,00р.
Маркетинг	44732,50р.
Склад	19570,15р.

- Count

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

Название отдела	Count-Зарплата
Сбыт
Маркетинг
Склад

- Выражение

Выбрав эту операцию, разработчик может сам, вручную написать формулу вычисление статистических данных. Формула, как и в случае с вычисляемым полем, пишется в строке бланка запроса Поле. Например, запрос вида

Поле:	НазвОтдела	Зарплата
…	…	…
ГО:	Группировка	Sum

эквивалентен запросу вида

Поле:	НазвОтдела	Sum-ЗП: Sum([Зарплата])
…	…	…
ГО:	Группировка	Выражение

Такая возможность сделана для того, чтобы разработчик мог получать более сложные статистические результаты. Например, пусть в нашей таблице Сотрудники зарплата хранится без вычета подоходного налога в 13%. А мы хотим узнать сумму зарплат по отделам уже с вычетом налога (так называемый «чистый» доход). Тогда нам нужно записать следующее:

Поле:	НазвОтдела	Sum-ЧистЗП: 0,87*Sum([Зарплата])
…	…	…
ГО:	Группировка	Выражение

При записи выражения со статистическими операциями существуют следующие ограничения.

1. Нельзя одну статистическую операцию вставлять в другую, например, Sum(Avg([Зарплата])).

2. Нельзя использовать поле вне статистической операции, если по нему не была произведена группировка. Запись Avg([Зарплата])*[Производит_ть], к примеру, допускает неоднозначную трактовку: какую производительность должна взять СУБД для отдела сбыта – 85% Звягина или 70% Сучкова? Вместо этого можно записать Avg([Зарплата]*[Производит_ть]).

- Условие

Строка ГО в БЗ неслучайно располагается выше строк задания критериев отбора (Условие отбора, или, …). При выборе любой из описанных выше групповых операций все критерии будут проверяться для уже сгруппированных записей.

Пример. Пусть для таблицы Сотрудники нам нужен общий размер зарплаты по отделам в том случае, если он превышает 30 тысяч рублей. Запрос

Поле:	НазвОтдела	Зарплата
…	…	…
Групповая операция:	Группировка	Sum
…	…	…
Условие отбора:		> 30000

выдаёт

Название отдела	Sum-Зарплата
Сбыт	39000,00р.
Маркетинг	44732,50р.

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

Условия такого вида называются постусловиями (условия ПОСЛЕ группировки). Они записываются как обычные условия.

Однако в ряде случаев более уместным и правильным оказывается применение предусловий, когда набор записей фильтруется ДО группировки. В этом случае для поля в БЗ следует создать отдельный столбец и в строке ГО выбрать операцию Условие. Теперь критерии, записанные в строках задания условий в таком столбце, будут проверяться до группировки.

Например, нужны суммы по отделам только тех зарплат, которые превышают 10000р. Запрос

Поле:	НазвОтдела	Зарплата	Зарплата
…	…	…	…
Групповая операция:	Группировка	Sum	Условие
…	…	…	…
Условие отбора:			> 10000

выдаёт

Название отдела	Sum-Зарплата
Сбыт	39000,00р.
Маркетинг	44732,50р.
Склад	19570,15р.

Видим, что зарплата Демьяновского не вошла в итоговую сумму складских зарплат.

Заметим, что наличие предусловий не исключает возможности наличия постусловий и наоборот.

В строке ГО можно указать лишь одну итоговую функцию. А как быть, если надо найти и сумму, и среднее, и максимальное значение, и еще что-то? Решение простое: одно и то же поле можно включить в БЗ несколько раз.

Дата добавления: 2015-08-27