Выборка с группированием данных и вычислением функций агрегации

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

Задание 18. Допустим, что для каждого студента из БД УЧЕБА, представленной одной таблицей «Учёба», требуется подсчитать количество сданных экзаменов и средний балл по результатам сдачи всех экзаменов. Основанием для группирования является поле «номер студента» и «фамилия» (если мы хотим увидеть это поле в структуре запроса). Группировка по полю «фамилия» будет фиктивной, поскольку с каждым номером студента связана только одна фамилия. Вычисление групповых характеристик (количество и среднее значение) будет выполняться по полю «оценка».

 

Сформируйте запрос на выборку с группированием данных и вычислением групповых характеристик:

1) перенесите в структуру запроса поля «номер студента», «фамилия», «оценка», «оценка»;

2) выполните команду Вид | Групповые операции – в структуре запроса появится строка «Групповая операция». Группирование данных будет выполняться по полю «номер студента»; поле «фамилия» не создаст вложенной группы, так как уникальной характеристикой студента является его учетный номер.

Поэтому значением групповой операции для этих полей будет значение по умолчанию «Группировка»;

3) в строке «Групповая операция» для первого поля «оценка» щелкните мышью и выберите из списка функцию подсчета количества Count, для второго поля – функцию вычисления среднего значения Avg (рис. 19);

4) в таблице результата запроса полям, по которым вычисляются групповые характеристики, присвойте содержательные имена;

5) установите курсор в ячейку «Поле» и нажмите правую клавишу мыши;

6) в контекстном меню выполните команду Свойства и введите в диалоговом окне «Свойства поля» в строку «Подпись» имена вычисляемых полей «количество экзаменов» и «средний балл». Результаты запроса будут иметь вид, как на рис. 20;

7) завершите результаты формирования запроса;

8) сохраните запрос под именем «Группировка».

Рис. 19. Структура запроса на группирование данных

 

Рис. 20. Результаты запроса на группирование данных

 

Лабораторная работа №3

 

Тема: Закрепление изученного материала

 

Задания для самостоятельной работы

В предлагаемых ниже вариантах задач выполнить следующие задания:

1. Создать базу данных в соответствии с вариантом.

2. Определить ключевые поля.

2. Заполнить таблицу данными. Количество записей должно быть не менее 20.

3. Продумать и выполнить следующие типы запросов-выборок:

1) проекция на подмножество однородных атрибутов;

2) параметрический запрос на примере селекции по условию на основе операций сравнения (равно, не равно, больше, меньше, больше или равно, меньше или равно);

3) запрос-выборка, селекция по сложному условию на основе оператора конъюнкции And;

4) запрос-выборка, селекция по сложному условию на основе оператора дизъюнкции Or;

5) запрос-выборка, селекция по условию на основе оператора Between…And;

6) запрос-выборка, селекция по условию на основе оператора Like;

7) запрос-выборка, селекция по условию на основе оператора In;

8) запрос-выборка, содержащий вычисляемое поле;

9) запрос-выборка с сортировкой по двум-трем полям;

10) запрос-выборка с группировкой данных и вычислением статистических функций Count, Sum, Avg, Min, Max и др.

 

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

По данной лабораторной работе сформировать отчет с подробным описанием всех действий.

 

Вариант I. База данных Поставки (номер поставщика, фамилия поставщика, город, код детали, город, код детали, количество за месяц).

 

Вариант II. База данных Больница (номер пациента, фамилия пациента, адрес, номер хирурга, фамилия хирурга, дата операции, тип операции).

 

Вариант III. База данных Библиотека (номер абонента, фамилия абонента, номер книги, автор, название, год издания, дата выдачи, дата возврата).

Вариант IV. База данных Аренда (номер клиента, фамилия клиента, номер объекта, плата, фамилия собственника, дата начала, дата конца).

Вариант V. База данных Кадры (номер сотрудника, фамилия сотрудника, дата рождения, место работы, должность, прием, увольнение, учебное заведение, образование)