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

Практическая работа №5

Тема: Вычисления в запросах. Использование функций.

Цель: научиться создавать запросы с использованием функций, для произведения вычислений.

Ход работы

Вычисляемые поля

1. Переименование полей. Иногда для удобства работы требуется переименовать некоторые поля в запросе (например, при наличии одноименных полей в разных таблицах). При переименовании полей используется следующий синтаксис: НовоеИмя: СтароеИмя (Рис. 3-20)

2. Вычисляемые поля

Вычислить дату окончания срока хранения продуктов (Рис. 3-21) В БД «Магазин» хранятся: дата изготовления продукта (поле ДатаИзгот таблицы Поставки) и срок хранения продукта (в днях) (поле СрокХран(дней) таблицы Продукты). Если к дате прибавить количество дней, то получится новая дата. В нашем случае

а) ДатаОкончХран: [ДатаИзгот] + [СрокХран(дней)] или

б) ДатаОкончХран: [Поставки]![ДатаИзгот] + [Продукты]![СрокХран(дней)]

Заметим, что:

• в вычисляемых полях имена полей указываются в квадратных скобках - [ИмяПоля] (скобки можно отпустить, если в имени поля используются только буквы и/или цифры и нет ссылки на таблицу)

• на имена полей можно ссылаться

• только по имени, если нет одноименных полей в других таблицах запроса: [ИмяПоля]

• по имени таблицы и имени поля: [ИмяТаблицы]![ИмяПоля]

• для записи вычисляемых полей можно использовать построитель выражений. Для вызова построителя выражений нажмите кнопку на панели инструментов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Письменно ответить на вопросы:

1. Как создать запрос на основе нескольких таблиц?

2. Описать наиболее часто используемые агрегатные функции.

3. Для чего используется группировка данных.

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

5. Как можно произвести расчёт данных не по группам, а по данным из заданного условием диапазона?

Сделать вывод по проделанной работе.