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

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

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

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

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

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

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

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

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

Например, для определения среднего значения поля MARK (оценки) по всем записям таблицы EXAM_MARKS можно исполь­зовать запрос с функцией AVG следующего вида:

SELKCT AVERAGE (MARK) PROM EXAM_MARKS;

Для подсчета общего количества строк в таблице следует ис­пользовать функцию COUNT со звездочкой.

SELECT COUNTO

FROM EXAM MARKS;

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

SELECT COUNT (DISTINCT SUBJ_IDj FROM SUBJECT;

Предложение GROUP BYGROUP BY (группировать по) позво­ляет группировать записи в подмножества, определяемые зна­чениями какого-либо поля, и применять агрегирующие функ­ции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе.

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

SELECT STUDENT_ID, MAX(MARKj FROM EXAM_MARKS GROUP BY STUDEHT_ID;

Выбираемые из таблицы EXAMMARKS записи группируют­ся по значениям поля STUDENT__ID, указанного в предложении

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

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

SELECT STUDENT_ID,  SUBJ_ID,   MAX (MARKj FROM EXAM_MARKS GROUP BY STUDENT_ID, SUBJ_ID;

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

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

При необходимости часть сформированных с ПОМОЩЬЮ GROUP BY групп может быть исключена с помощью предложе­ния HAVING.

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

SELECT SUBJ_NAME,  MAX(HOURj FROM SUBJECT GROUP BY SUBJ_NAME HAVING MAX  (HOURj   >= 72;

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

Упражнения

1.   Напишите запрос для подсчета количества студентов, сдававших экзамен по предмету обучения с идентификатором, равным 20.

2. Напишите запрос, который позволяет подсчитать в таблице EXAMMARKS количество различных предметов обучения.

3. Напишите запрос, который выполняет выборку для каждого сту­дента значения его идентификатора и минимальной из получен­ных им оценок.

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

5. Напишите запрос, выполняющий вывод фамилии первого в алфа­витном порядке (по фамилии) студента, фамилия которого начи­нается на букву «И».

6. Напишите запрос, который выполняет вывод (для каждого пред­мета обучения) наименования предмета и максимального значе­ния номера семестра, в котором этот предмет преподается.

7. Напишите запрос, который выполняет вывод данных для каждого конкретного дня сдачи экзамена о количестве студентов, сдавав­ших экзамен в этот день.

8. Напишите запрос для получения среднего балла для каждого курса по каждому предмету.

9. Напишите запрос для получения среднего балла для каждого сту­дента.

10. Напишите запрос для получения среднего балла для каждого экза­мена.

11. Напишите запрос для определения количества студентов, сдавав­ших каждый экзамен.

12. Напишите запрос для определения количества изучаемых предме­тов на каждом курсе.