Агрегированные запросы в процессе выполнения разбивают исходный набор данных на группы схожих между собой записей и для каждой группы рассчитывают для заданных столбцов числовые характеристики с применением следующих статистических (агрегирующих) функций:
COUNT
Подсчитывает количество значений выбранного столбца.
SUM
Рассчитывает сумму значений выбранного столбца.
AVG
Рассчитывает среднее арифметическое значений выбранного столбца.
VAR
Рассчитывает дисперсию значений выбранного столбца.
STDEV
Рассчитывает среднеквадратичное отклонение значений от среднего значения выбранного столбца.
MAX
Находит значение максимального элемента выбранного столбца.
MIN
Находит значение минимального элемента выбранного столбца.
FIRST
Возвращает первую запись выбранного столбца.
LAST
Возвращает последнюю запись выбранного столбца.
Замечание: Все перечисленные выше функции, кроме FIRST и LAST, игнорируют пустое значение (NULL). Функции FIRST и LAST возвращают записи в том порядке, в котором они были введены. Сортировка записей не влияет на выполнение этих функций.
Для построения агрегированного запроса необходимо открыть конструктор запросов и дать команду Групповые операции (меню Вид) или нажать одноименную кнопку на панели инструментов. В результате в бланке запроса появится дополнительная строка Групповая операция (рис. 98), позволяющая для каждого столбца определить одну из следующих групповых операций:
Рис. 98. Вид бланка запроса в режиме групповых операций
· Группировка
По значению элементов выбранного столбца производится разбиение исходного множества записей на группы строк с одинаковым значением выбранного столбца.
· Статистическая функция
По значениям выбранного столбца производится расчет значения выбранной статистической функции.
· Выражение
По значениям выбранного столбца производится расчет сложного выражения, содержащего в себе одну или несколько статистических функций.
· Условие
Определяет условия на значения выбранного столбца для строк, которые будут участвовать в разбиении на группы; строки, не удовлетворяющие наложенным условиям, в расчетах участвовать не будут.
Пример №5. В базе данных (файл Пример 5.mdb) имеется таблица СТУДЕНТ с данными об обучающихся на факультете студентах и полученных ими оценках по математике (рис. 99).
Рис. 99. Исходные данные для примера №5
Задача №1. Определить количество обучающихся на факультете студентов.
Решение:
Рис. 100. Запрос для решения задачи №1 (пример №5)
Результат выполнения запроса:
Рис. 101. Результат выполнения запроса для решения задачи №1 (пример №5)
Комментарий: Для решения задачи достаточно определить количество записей в таблице СТУДЕНТ с помощью статистической функции COUNT. Принимая во внимание, что все статистические функции игнорируют пустые значения (NULL), в качестве аргумента функции следует выбрать столбец, гарантированно не содержащий пустых значений. Обычно наилучшим претендентом на роль такого столбца является первичный ключ таблицы. В данном случае – это столбец СтудентID.
Задача №2. Для каждой группы определить количество студентов.
Решение:
Рис. 102. Запрос для решения задачи №2 (пример №5)
Результат выполнения запроса:
Рис. 103. Результат выполнения запроса для решения задачи №2(пример №5)
Комментарий: Для решения данной задачи необходимо разбить исходный набор данных на подгруппы по значениям столбца Номер группы, после чего посчитать количество записей в каждой подгруппе с помощью функции COUNT.
Задача №3. Рассчитать средний балл по математике по результатам всех студентов.
Решение:
Рис. 104. Запрос для решения задачи №3
Результат выполнения запроса:
Рис. 105. Результат выполнения запроса для решения задачи №3
Комментарий: Для решения задачи применяется статистическая функция AVG. Так как требуется рассчитать средний балл по всем результатам, то группировка записей не производится.
Задача №4. Рассчитать средний балл по математике для каждой группы.
Решение:
Рис. 106. Запрос для решения задачи №4
Результат выполнения запроса:
Рис. 107. Результат выполнения запроса для решения задачи №4
Комментарий: В отличие от предыдущей задачи, средний балл требуется рассчитать отдельно для каждой группы студентов, поэтому в запрос добавлена операция группировки данных по столбцу Номер группы. При выполнении запроса вначале происходит разбиение исходного множества строк на подгруппы с одинаковым значением поля Номер группы, после чего для каждой подгруппы вычисляется среднее значение.
Задача №5. Рассчитать средний балл по математике для групп 101 и 103.
Решение:
Рис. 108. Запрос для решения задачи №5
Результат выполнения запроса:
Рис. 109. Результат выполнения запроса для решения задачи №5
Комментарий: Отличие данной задачи от предыдущей заключается в требовании ограничить результаты указанными группами. Для выполнения требования в бланк запроса добавлены условия на значения поля Номер группы. Выполнение запроса, как и в предыдущем случае, начинается с разбиения исходного множества на подгруппы с одинаковым значением поля Номер группы, затем в каждой из подгрупп вычисляется среднее значение, после чего производится фильтрация в соответствии с наложенными условиями и вывод результатов.
Задача №6. Рассчитать средний балл по математике по результатам студентов групп 101 и 103.
Решение:
Рис. 110. Запрос для решения задачи №6
Результат выполнения запроса:
Рис. 111. Результат выполнения запроса для решения задачи №6
Комментарий: Отличие этой задачи от предыдущей заключается в том, что средний балл требуется рассчитать для студентов групп 101 и 103, не делая между ними различий. Для этого в строке Групповая операция выбирается значение Условие и накладываются условия фильтрации. Выполнение запроса начинается с фильтрации исходного множества строк, после чего осуществляется их группировка и вычисление итогового значения. Обратите внимание, что столбец, по которому осуществляется предварительная фильтрация данных, на экран не выводится – Access автоматически сбрасывает флажок Вывод на экран.
Задача №7. Определить количество студентов в каждой группе, не сдавших экзамен (не имеющих оценки) по математике.
Решение:
Рис. 112. Запрос для решения задачи №7
Результат выполнения запроса:
Рис. 113. Результат выполнения запроса для решения задачи №7
Комментарий: В данной задаче требуется разбить исходную выборку на подгруппы, после чего для каждой из них посчитать количество пустых значений. Проблема заключается в том, что статистические функции игнорируют пустые значения. Поэтому для поиска искомых величин необходимо разбить исходную выборку на подгруппы, найти в каждой подгруппе количество обучающихся студентов и вычесть из него количество студентов, получивших какую-нибудь оценку по математике, – это и будет искомой величиной.