Агрегирование и групповые функции
Агрегирующие функции позволяют получать из таблицы сводную (агрегированную) информацию, выполняя операции над группой строк таблицы. Для задания в SELECT-запросе агрегирующих операций используются следующие ключевые слова:
COUNT определяет количество строк или значений поля, выбранных посредством запроса и не являющихся NULL-значениями;
SUM вычисляет арифметическую сумму всех выбранных значений данного поля;
AVG вычисляете среднее значение :для всех выбранных: значений данного поля.
MAX-вычисляет-наибольшее из всех выбранных значении данного поля;
MIN вычисляет наименьшее из всех выбранных значений данного поля.
В SELECT-запросе агрегирующие функции используются аналогично именам полей, при этом последние (имена полей) используются в качестве аргументов этих функций.
Функция AVG предназначена для подсчета среднего значения поля на множестве записей таблицы.
Например, для определения среднего значения поля MARK (оценки) по веем записям таблицы EXAM_MARKS можно использовать запрос с функцией AVG следующего вида:
SELECT AVG(MARK)
FROM EXAM_MARKS;
Для подсчета общего количества строк в таблице следует использовать функцию COUNT со звездочкой:
SELECT COUNT (*)
FROM XAM_MARKS;
При подсчете значений конкретных атрибутов аргументы DISTINCT и ALL позволяют соответственно исключать и включать дубликаты обрабатываемых функцией COUNT значений. При этом необходимо учитывать, что при использовании опции ALL неопределенные значения атрибута (NULL) все равно нё войдут в число подсчитываемых значений.
|
|
|
SELECT COUNT(DISTINCT SUBJ_ID)
FROM SUBJECT;
Предложение GROUP BY (ГРУППИРОВАТЬ ПО) позволяет группировать записи в подмножества, определяемые значениями какого либо поля, и применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе.
Предположим, требуется найти максимальное значение оценки, полученной каждым студентом. Запрос будет выглядеть следующим образом:
SELECT STUDENT_ID, MAX(MARK)
FROM EXAM_МАRKS
GROUP BY STUDENT_ID;
Выбираемые из таблицы EXAM_MARKS записи группируются по значениям поля STUDENT_ID указанного в предложении GROUP BY, и для каждой группы находится максимальное значение поля MARK. Предложение GROUP BY позволяет применять агрегирующие функции к каждой группе, определяемой общим значением поля или полей, указанных в этом предложении. В приведенном запросе рассматриваются группы записей, сгруппированные по идентификаторам студентов.
В конструкции GROUP BY для группирования может быть использовано более одного столбца. Например:
SELECT STUDENT_ID, SUBJ_ID, MAX(MARK)
FROM EXAM_MARKS
GROUP BY STUDENT_ID, SUBJ_ID;
В этом случае строки вначале группируются по значениям первого столбца, а внутри этих групп — в подгруппы по значениям второго столбца. Таким образом, GROUP ВY не только устанавливает столбцы по которым осуществляется группировка но и указывает. порядок разбиения столбцов на группы.
|
|
|
Следует иметь в виду, что после ключевого слова SELECT должны быть использованы. только те имена столбцов, которые указаны в предложении GROUP ВY.
При необходимости часть сформированных с помощью GROUP BY групп может быть исключена с помощью предложения HAVING.
Предложение HAVING определяет критерий, по которому группы следует включать в выходные-данные (по аналогии с предложением WHERE, которое осуществляет это для отдельных строк).
SELECT SUBJ_NAME, MAX(HOUR)
FROM SUBJECT
GROUP BY SUBJ_NAME
HAVING MAX(HOUR) > 72;
В условии, задаваемом предложением HAVING, должны быть указаны только поля или выражения, которые на выходе имеют единственное значение для каждой выводимой группы.
УПРАЖНЕНИЯ
1. Напишите запрос для подсчета количества студентов, сдававших экзамен по предмету обучения с идентификатором 20.
2. Напишите запрос, который позволяет подсчитать в таблице EXAM_MARKS количество различных предметов обучения.
3. Напишите запрос, который для каждого студента выполняет выборку его идентификатора и минимальной из полученных им оценок.
|
|
|
4. Напишите запрос, который для каждого студента выполняёт выборку его идентификатора и максимальной из полученных им оценок.
5. Напишите запрос, выполняющий вывод первой по алфавиту фамилии студента, начинающейся на букву 'И'.
6. Напишите запрос, который для каждого предмета обучения выводит наименование предмета и максимальное значение номера семестра, в котором этот предмет преподается.
7. Напишите запрос, который для каждого конкретного дня сдачи экзамена выводит данные о количестве студентов, сдававших экзамен в этот день.
8. Напишите запрос, выдающий средний балл для Каждого студента.
9. Напишите запрос, выдающий средний балл для каждого экзамена.
10. Напишите запрос, определяющий количество сдававших студентов для каждого экзамена.
11. Напишите запрос для определения количества предметов, изучаемых на каждом курсе.
12. Для каждого университета напишите запрос выводящий суммарную стипендию обучающихся в нем студентов, с последующей сортировкой списка по этому значению.
13. Для каждого семестра напишите запрос, выводящий общее количество часов, отводимое на изучение соответствующих предметов.
14. Для каждого студента напишите запрос выводящий среднее значение оценок, полученных им на всех экзаменах.
|
|
|
15. Для каждого студента напишите запрос, выводящий среднее значение оценок полученных им по каждому предмету.
16. Напишите запрос, выводящий количество студентов, проживающих в каждом городе. Список отсортировать в порядке убывания количества студентов.
количества студентов
17. Для каждого университета напишите запрос, выводящий количество обучающихся в нем студентов, с последующей сортировкой списка по этому количеству.
18. Для каждого университета напишите запрос, выводящий количество работающих в нем преподавателей, с последующей сортировкой списка по этому количеству.
19. Для каждого университета напишите запрос, выводящий сумму стипендии, выплачиваемой студентам каждого курса.
20. Для каждого города напишите запрос, выводящий максимальный рейтинг университетов, в нем расположенных, с последующей сортировкой списка по значениям рейтингов.
21. Для каждого дня сдачи экзаменов напишите запрос, выводящий среднее значение всех экзаменационных оценок.
22. Для каждого дня сдачи экзаменов напишите запрос, выводящий максимальные оценки, полученные по каждому предмету.
23. Для каждого дня сдачи экзаменов напишите запрос, выводящий общее количество студентов, сдававших экзамены.
24. Для каждого дня сдачи экзаменов напишите запрос, выводящий общее количество экзаменов, сдававшихся каждым студентом.
25. Для каждого преподавателя напишите запрос, выводящий количество преподаваемых им предметов.
26. Для каждого предмета напишите запрос, выводящий количество преподавателей, ведущих по нему занятия
27. Напишите запрос, выполняющий вывод количества студентов, имеющих только отличные оценки.
28. Напишите запрос, выполняющий вывод количества экзаменов; сданных (с положительной оценкой) студентом с идентификатор ром 32.
Дата добавления: 2019-02-12; просмотров: 1397; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!