Влияние NULL-значений в функции avg
Обобщение данных с помощью агрегатных функций
Цель работы:Изучение SQL-запросов на выборку данных, использующих агрегирующие и групповые функции
Теоретические сведения
Агрегирующие функции позволяют получать из таблицы сводную (агрегированную) информацию, выполняя операции над группой строк таблицы. Для задания в SELECT-запросе агрегирующих операций используются следующие ключевые слова:
• COUNT определяет количество строк или значений поля, выбранных посредством запроса, и не являющихся NULL-значениями;
• SUM - вычисляет арифметическую сумму всех выбранных значений данного поля;
• AVG вычисляет среднее значение для всех выбранных значений данного поля;
• МАХ вычисляет наибольшее из всех выбранных значений данного поля;
• MIN вычисляет наименьшее из всех выбранных значений данного поля.
В SELECT-запросе агрегирующие функции используются аналогично именам полей, при этом последние (имена полей) используются в качестве аргументов этих функций.
Функция AVG предназначена для подсчета среднего значения поля на множестве записей таблицы.
Например, для определения среднего значения поля MARK (оценки) по всем записям таблицы EXAM_MARKS можно использовать запрос с функцией AVGследующего вида:
SELECTAVG(MARK)
FROMEXAM_MARKS;
Для подсчета общего количества строк в таблице следует использовать функцию COUNTсо звездочкой.
SELECT COUNT(*)
FROMEXAM_MARKS;
Аргументы DISTINCTи ALLпозволяют, соответственно, исключать и включать дубликаты обрабатываемых функцией COUNTзначений, при этом необходимо учитывать, что при использовании опции ALLзначения NULLвсе равно не войдут в число подсчитываемых значений.
SELECT COUNT(DISTINCTSUBJID) FROMSUBJECT;
Предложение GROUP BYGROUP BY(ГРУППИРОВАТЬ ПО) позволяет группировать записи в подмножества, определяемые значениями какого-либо поля, и применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе.
Предположим, требуется найти максимальное значение оценки, полученной каждым студентом. Запрос будет выглядеть следующим образом:
SELECTSTUDENT_ID, MAX(MARK) FROMEXAM_MARKS GROUPBY STUDENT_ID;
Выбираемые из таблицы EXAM_MARKS записи группируются по значениям поля STUDENT_ID, указанного в предложении GROUPBY, и для каждой группы находится максимальное значение поля MARK. Предложение GROUPBY позволяет применять агрегирующие функции к каждой группе, определяемой общим значением поля (или полей), указанных в этом предложении. В приведенном запросе рассматриваются группы записей, сгруппированные по идентификаторам студентов.
В конструкции GROUPBY для группирования может быть использовано более одного столбца. Например:
SELECTSTUDENT_ID, SUB J_ID, MAX(MARK) FROMEXAM_MARKS GROUPBYSTUDENT_ID, SUBJ_ID;
В этом случае строки вначале группируются по значениям первого столбца, а внутри этих групп - в подгруппы по значениям второго столбца. Таким образом, GROUPBY не только устанавливает столбцы, по которым осуществляется группирование, но и указывает порядок разбиения столбцов на группы.
Следует иметь в виду, что в предложении GROUPBY должны быть указаны все выбираемые столбцы, приведенные после ключевого слова SELECT,кроме столбцов, указанных в качестве аргумента в агрегирующей функции.
При необходимости часть сформированных с помощью GROUPBY групп может быть исключена с помощью предложения HAVING.
Предложение HAVINGопределяет критерий, по которому группы следует включать в выходные данные, по аналогии с предложением WHERE,которое осуществляет это для отдельных строк.
SELECTSUB J_NAME, MAX(HOUR) FROMSUBJECT GROUPBY SUBJ_NAME HAVINGMAX(HOUR)>=72;
В условии, задаваемом предложением HAVING,указывают только поля или выражения, которые на выходе имеют единственное значение для каждой выводимой группы.
Влияние NULL-значений в функции count
Если аргумент функции COUNT является константой или столбцом без пустых значений, то функция возвращает количество строк, к которым применимо определенное условие или группирование.
Если аргументом функции является столбец, содержащий пустое значение, то COUNTвернет число строк, не содержащих пустые значения, и к которым применимо определенное условие или группирование.
Если бы механизм NULLне был доступен, то неприменимые и отсутствующие значения пришлось бы исключать с помощью конструкции WHERE.
Поведение функции COUNT(*)не зависит от пустых значений. Она возвратит общее количество строк в таблице.
Влияние NULL-значений в функции avg
Среднее значение множества чисел равно сумме чисел, деленной на число элементов множества. Однако, если некоторые элементы пусты, то есть их значения неизвестны или не существуют, то деление на количество всех элементов множества приведет к неправильному результату.
Функция AVGвьиисляет среднее значение всех известных значений множества элементов, то есть эта функция подсчитывает сумму известных значений и делит ее на количество этих значений, а не на общее количество значений, среди которых могут быть NULL-значения. Если столбец состоит только из пустых значений, то функция AVGтакже возвратит NULL.
Как уже отмечалось, записи в таблицах реляционной базы данных неупорядочены. Однако, данные, выводимые в результате выполнения запроса, могут быть упорядочены. Для этого используется оператор ORDER BY,который позволяет упорядочивать выводимые записи в соответствии со значениями одного или нескольких выбранных столбцов. При этом можно задать возрастающую (ASC)или убывающую (DESC)последовательность сортировки для каждого из столбцов. По умолчанию принята возрастающая последовательность сортировки.
Запрос, позволяющий выбрать все данные из таблицы предметов обучения SUBJECT, с упорядочиванием по наименованиям предметов, выглядит следующим образом:
SELECT *
FROMSUBJECT
ORDER BYSUBJ_NAME;
Тот же список, но упорядоченный в обратном порядке, можно получить запросом:
SELECT *
FROMSUBJECT
ORDER BYSUBJ_NAME DESC;
Можно упорядочить выводимый список предметов обучения по значениям семестров, а внутри семестров - по наименованиям предметов.
SELECT *
FROM SUBJECT
ORDER BY SEMESTR, SUBJ_NAME;
Предложение ORDER BY может использоваться с GROUP BY для упорядочивания групп записей. При этом оператор ORDER BY в запросе всегда должен быть последним.
SELECT SUBJ_NAME, SEMESTR, MAX(HOUR) FROM SUBJECT
GROUP BY SEMESTR, SUBJ_NAME ORDER BY SEMESTR;
При упорядочивании вместо наименований столбцов можно указывать их номера, имея, однако, в виду, что в данном случае это - номера столбцов, указанные при определении выходных данных в запросе, а не номера столбцов в таблице. Полем с номером 1 является первое поле, указанное в предложении ORDER BY - независимо от его расположения в таблице.
SELECT SUBJ_ID, SEMESTR FROM SUBJECT ORDER BY2DESC;
В этом запросе выводимые записи будут упорядочены по полю
SEMESTR.
Если в поле, которое используется для упорядочивания, существуют NULL-значения, то все они размещаются в конце или предшествуют всем остальным значениям этого поля.
База данных, состоит из следующих таблиц.
Таблица 1.1.STUDENT (Студент)
| STUDENT_ID | SURNAME | NAME | STIPEND | KURS | CITY | BIRTHDAY | UNIV_ID |
| Иванов | Иван | Орел | 3/12/1982 | ||||
| Петров | Петр | Курск | 1/12/1980 | ||||
| Сидоров | Вадим | Москва | 7/06/1979 | ||||
| Кузнецов | Борис | Брянск | 8/12/1981 | ||||
| Зайцева | Ольга | Липецк | 1/05/1981 | ||||
| Павлов | Андрей | Воронеж | 5/11/1979 | ||||
| Котов | Павел | Белгород | NULL | ||||
| Лукин | Артем | Воронеж | 1/12/1981 | ||||
| Петров | Антон | NULL | 5/08/1981 | ||||
| Белкин | Вадим | Воронеж | 7/01/1980 |
STUDENT_ID - числовой код, идентифицирующий студента, SURNAME - фамилия студента, NAME- имя студента,
STIPEND- стипендия, которую получает студент, KURS- курс, на котором учится студент, CITY- город, в котором живет студент, BIRTHDAY- дата рождения студента,
UNIV_ID - числовой код, идентифицирующий университет, в котором учится студент.
Таблица1.2. LECTURER (Преподаватель)
| LECTURER ID | SURNAME | NAME | CITY | UNIV ID |
| Колесников | Борис | Воронеж | ||
| Никонов | Иван | Воронеж | ||
| Лагутин | Павел | Москва | ||
| Струков | Николай | Москва | ||
| Николаев | Виктор | Воронеж | ||
| Сорокин | Андрей | Орел |
LECTURER_ID - числовой код, идентифицирующий
преподавателя,
SURNAME - фамилия преподавателя, NAME - имя преподавателя,
CITY- город, в котором живет преподаватель, UNIV_ID - идентификатор университета, в котором работает преподаватель.
| Таблица 1.3 | .SUBJECT(Предмет о | бучения) |
| SUBJ_ID | SUBJ_NAME HOUR | SEMESTER |
| Информатика 56 | ||
| Физика 34 | ||
| Математика 56 | ||
| История 34 | ||
| Английский 56 | ||
| Физкультура 34 |
SUBJ_ID- идентификатор предмета обучения,
SUBJ_NAME- наименование предмета обучения,
HOUR- количество часов, отводимых на изучение предмета,
SEMESTER- семестр, в котором изучается данный предмет.
| Таблица 1/ | UNIVERSITY | (Универс | итеты) |
| UNIV_ID | UNIV_NAME | RATING | CITY |
| МГУ | Москва | ||
| ВГУ | Воронеж | ||
| ИГУ | Новосибирск | ||
| РГУ | Ростов | ||
| БГУ | Белгород | ||
| ТГУ | Томск | ||
| ВГМА | Воронеж |
UNIV_ID- идентификатор университета,
UNIV_NAME- название университета,
RATING- рейтинг университета,
CITY- город, в котором расположен университет,
Таблица 1.5.EXAM_MARKS (Экзаменационные оценки)
| EXAM_ID | STUDENT_ID | SUBJ_ID | MARK | EXAM_DATE |
| 12/01/2000 | ||||
| 23/01/2000 | ||||
| 05/01/2000 | ||||
| 17/06/1999 | ||||
| NULL | 22/06/1999 | |||
| 18/01/2000 |
EXAM_ID - идентификатор экзамена, STUDENT_ID- идентификатор студента, SUBJ_ID- идентификатор предмета обучения, MARK - экзаменационная оценка, EXAM DATE - дата экзамена.
Таблица 1.6.SUB J_LECT (Учебные дисциплины преподавателей)
| LECTURER_ID | SUBJ_ID |
LECTURER_ID - идентификатор преподавателя, SUBJ_ID - идентификатор предмета обучения.
Задание на работу
1. Напишите запрос для подсчета количества студентов, сдававших экзамен по предмету обучения с идентификатором, равным 20.
2. Напишите запрос, который позволяет подсчитать в таблице EXAM_MARKS количество различных предметов обучения.
3. Напишите запрос, который выполняет выборку для каждого студента значения его идентификатора и минимальной из полученных им оценок.
4. Напишите запрос, выполняющий вывод фамилии первого в алфавитном порядке (по фамилии) студента, фамилия которого начинается на букву "И".
5. Напишите запрос, который выполняет вывод для каждого предмета обучения наименование предмета и максимальное значение номера семестра, в котором этот предмет преподается.
6. Напишите запрос, который выполняет вывод данных для каждого конкретного дня сдачи экзамена о количестве студентов, сдававших экзамен в этот день.
7. Напишите запрос для получения среднего балла для каждого курса по каждому предмету.
8. Напишите запрос для получения среднего балла для каждого студента, включив в результат запроса только тех студентов, средний балл которых не меньше 4.
9. Напишите запрос для получения среднего балла для каждого экзамена, включив только те экзамены, которые сдали более 5 студентов.
10. Напишите запрос для определения количества студентов, сдававших каждый экзамен, исключив предметы с идентификатором 1.
11. Напишите запрос для определения количества изучаемых предметов на каждом курсе, исключив из результата математику, а также курсы, в течение которых изучалось менее 10 предметов.
12. Предположим, что стипендия всем студентам увеличена на 20%. Напишите запрос к таблице STUDENT, выполняющий вывод номера студента, фамилию студента и величину увеличенной стипендии. Выходные данные упорядочить: а) по значению последнего столбца (величине стипендии); б) в алфавитном порядке фамилий студентов.
13. Напишите запрос, выполняющий вывод списка предметов обучения в порядке убывания семестров и возрастания отводимых на предмет часов. Поле семестра в выходных данных должно быть первым, за ним должны следовать имя предмета обучения и идентификатор предмета.
14. Напишите запрос, который выполняет вывод суммы баллов всех студентов для каждой даты сдачи экзаменов и представляет результаты в порядке убывания этих сумм.
15. Напишите запрос, который выполняет вывод а) среднего, б) минимального, в) максимального баллов всех студентов для каждой даты сдачи экзаменов в период прошлого учебного года, включив дни, в течение которых студентами не было получено неудовлетворительных оценок, и представляет результаты в порядке убывания этих значений.