Агрегирование и групповые функции

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

Используются следующие ключевые слова:

COUNTопределяет количество строк или значений поля, выбранных посредством запроса, и не являющихся NULL-значениями;

SUM– вычисляет арифметическую сумму всех выбранных значений данного поля;

AVGвычисляет среднее значение для всех выбранных значений данного поля;

MAXвычисляет наибольшее из всех выбранных значений данного поля;

MINвычисляет наименьшее из всех выбранных значений данного поля.

В SELECT-запросе агрегирующие функции используются аналогично именам полей, при этом последние (имена полей) используются в качестве аргументов этих функций.

Функция AVGпредназначена для подсчета среднего значения поля на множестве записей таблицы.

Примеры.

1. Определить среднее значение поля MARK (оценки) по всем записям таблицы EXAM_MARKS:

SELECT AVG(MARK)

FROMEXAM_MARKS;

1. Подсчитать общее количество строк в таблице:

SELECT COUNT(*)

FROMEXAM_MARKS;

Аргументы DISTINCTи ALLпозволяют, соответственно, исключать и включать дубликаты обрабатываемых функцией COUNTзначений, при этом необходимо учитывать, ч то при использовании опции ALLзначения NULLвсе равно не войдут в число подсчитываемых значений.

SELECT COUNT(DISTINCTSUBJ_ID)

FROMSUBJECT;

Предложение GROUP BY(ГРУППИРОВАТЬ ПО) позволяет группировать записи в подмножества, определяемые значениями какого-либо поля, и применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе.

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

SELECTSTUDENT_ID, MAX(MARK)

FROMEXAM_MARKS

GROUP BYSTUDENT_ID;

Комментарий. Выбираемые из таблицы EXAM_MARKS записи группируются по значениям поля STUDENT_ID, указанного в предложении GROUP BY, и для каждой группы находится максимальное значение поля MARK. Предложение GROUP BYпозволяет применять агрегирующие функции к каждой группе, определяемой общим значением поля (или полей), указанных в э том предложении. В приведенном запросе рассматриваются группы записей, сгруппированные по идентификаторам студентов.

В конструкции GROUP BYдля группирования может быть использовано более одного столбца. Например:

SELECTSTUDENT_ID, SUBJ_ID, MAX(MARK)

FROMEXAM_MARKS

GROUP BYSTUDENT_ID, SUBJ_ID;

В этом случае строки вначале группируются по значениям первого столбца, а внутри этих групп – в подгруппы по значениям второго столбца. Таким образом, GROUP BYне только устанавливает столбцы, по которым осуществляется группирование, но и указывает порядок разбиения столбцов на группы.

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

При необходимости часть сформированных с помощью GROUP BYгрупп может быть исключена с помощью предложения HAVING. Предложение HAVINGопределяет критерий, по которому группы следует включать в выходные данные, по аналогии с предложением WHERE, которое осуществляет это для отдельных строк.

SELECTSUBJ_NAME, MAX(HOUR)

FROMSUBJECT

GROUP BYSUBJ_NAME

HAVING MAX(HOUR) >= 72;

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