Хотите получать новые статьи блога
прямо себе на почту?
Укажите свой e-mail:


WorldSkills Russia
Яндекс.Метрика Интернет-издание Профобразование

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

0

План:

  1. Агрегирование и групповые функции
  2. Пустые значения (NULL) в агрегируюших функциях
    • Влияние NULL-значений на функции COUNT
    • Влияние NULL-значений на функции AVG
Агрегирование и групповые функции Скачать материал лекции в формате PDF

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

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

  • COUNT определяет количество строк или значений поля, выбранных посредством запроса и не являющихся NULL-значениями;
  • SUM вычисляет арифметическую сумму всех выбранных значений данного поля;
  • AVG вычисляет среднее значение для всех выбранных значений данного поля;
  • MAX вычисляет наибольшее из всех выбранных значений данного поля;
  • MIN вычисляет наименьшее из всех выбранных значений данного поля.

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

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

Таблица 1 «Экзаменационная ведомость»

№ п/п Фамилия Имя Номер билета Оценка
1 Иванов Иван 5 4
2 Петров Александр 9 3
3 Сидоров Степан 14 5
4 Попов Илья 8 3
5 Фролов Дмитрий NULL NULL
6 Кострюков Максим 17 2
7 Яблоков Сергей 2 4

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

1
SELECT AVG(Оценка) FROM Экзаменационная_ведомость;

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

1
SELECT COUNT(*) FROM Экзаменационная_ведомость;

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

1
SELECT ALL COUNT(Оценка) FROM Экзаменационная_ведомость;

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

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

1
2
SELECT * FROM Экзаменационная_ведомость
GROUP BY Номер_билета, Оценки;

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

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

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

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

1
2
SELECT SUM(Фамилия) FROM Экзаменационная_ведомость
GROUP BY Оценки HAVING Оценка=3;

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

2. Пустые значения (NULL) в агрегируюших функциях

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

а) Влияние NULL-значений на функции COUNT

Если аргумент функции COUNT является константой или столбцом без пустых значений, то функция возвращает количество строк, к которым применено определённое условие или группирование.

Если аргументом функции является столбец, содержащий пустое значение, то COUNT вернёт число строк, которые не содержат пустые значения и к которым применимо определенное в COUNT условие или группирование.

Если бы механизм NULL не был доступен, то неприменимые и отсутствующие значения пришлось бы исключать с помощью конструкции WHERE.

Поведение функции COUNT(*) не зависит от пустых значений. Она возвратит общее количество строк в таблице.

б) Влияние NULL-значений на функции AVG

Среднее значение множества чисел равно сумме чисел, делённой на число элементов множества. Однако, если некоторые элементы пусты (то есть их значения неизвестны или не существуют), деление на количество всех элементов множества приведёт к неправильному результату.

Функция AVG вычисляет среднее значение всех известных значений множества элементов, то есть эта функция подсчитывает сумму известных значений и делит её на количество этих значений, а не на общее количество значений, среди которых могут быть NULL-значения. Если столбец состоит только из пустых значений, то функция AVG также возвращает NULL.


Возникли вопросы?
Тогда смело пишите комментарий — рада буду ответить!
Агрегирование и групповые функции