Возможности группировки данных. Использование агрегатных функций

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

Наиболее часто используются следующие агрегатные функции:

• Count – подсчет количества записей, возвращаемых запросом

• Sum – вычисление суммы набора значений, содержащихся в заданном поле запроса

• Avg – вычисление арифметического среднего набора чисел, содержащихся в указанном поле запроса

• Max – вычисление максимального значения из набора значений, содержащихся в указанном поле запроса

• Min – вычисление минимального значения из набора значений, содержащихся в указанном поле запроса

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

• Сгруппировать данные продажи продуктов по дате продажи

• Задать функцию для подсчета количества продуктов (Count)

Для подключения групповых операций в запросе на выборку нажмите кнопку или выберите пункты меню Вид, Групповые операции. В бланке запроса появится дополнительная строка «Групповая операция» (Рис. 3-22).

Рассмотрим несколько примеров:

1. Рассчитать количество проданных продуктов за каждый день (Рис. 3-22, а) Обратите внимание, что поле, для которого установлена агрегатная функция, автоматически получает новое имя (Рис. 3-22, б).

2. Рассчитать количество проданных продуктов за текущий день В отличие от предыдущего запроса здесь нет необходимости группировать данные по всем дням. Достаточно выбрать записи, относящиеся к текущему дню, а затем посчитать количество продуктов. Т.е. в строке «Групповая операция» вместо значения Группировка выберем Условие (обратите внимание, что поле с условием не выводится на экран) (Рис. 3-23). Для задания текущего дня можно использовать функцию Date(), возвращающую каждый день новую дату – текущую. В этом случае запрос станет более универсальным чем, если бы мы задавали конкретную дату.

3. Рассчитать ежедневную сумму продаж продуктов, с учетом того, что ЦенаПродажи – это цена за единицу продукта. В этом запросе необходимо сгруппировать данные по дате продажи, создать вычисляемое поле: СуммаПродажи: Количество*ЦенаПродажи, а затем установить функцию Sum для вычисляемого поля (Рис. 3-24)

3.2.5. Вложенные запросы

Язык QBE позволяет строить вложенные запросы. То есть можно создать первый запрос, а затем построить второй запрос, вызвав первый запрос в качестве источника данных. В случае использования вложенных запросов, вычисление начинается с самого внутреннего запроса.

1. Получить названия и вес продуктов, проданных 2 января 2004г.

Этот пример мы рассмотрели в п.3.2.2. как условное соединение Рис. 3-16. Рассмотрим теперь этот пример как вложенный запрос. На первом шаге выберем продажи продуктов за 2.01.04, запрос сохраним под именем «ПродажаЗаДень» (Рис. 3-25). На втором шаге соединим запрос «ПродажаЗаДень» с таблицей Продукты для подстановки названий и единиц измерений продуктов (Рис. 3-26).

2. Какие продукты имеют максимальный срок хранения?

На первом шаге найдем максимальный срок хранения продуктов (Рис. 3-27, а, б). На втором шаге выберем продукты, соединив поле из запроса, с найденным максимальным сроком хранения, с полем СрокХранения(дней) таблицы Продукты (Рис. 3-27, в, г).