Для подведения итогов по информации, содержащейся в БД, в SQL предусмотрены агрегатные функции. Агрегатная функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец.
Например, агрегатная функция AVG() принимает в качестве аргумента столбец чисел и вычисляет их среднее значение.
Чтобы вычислить среднедушевой доход жителя Зеленограда, нужен такой запрос:
SELECT ‘СРЕДНЕДУШЕВОЙ ДОХОД=’, AVG(SUMD)
В SQL имеется шесть агрегатных функций, которые позволяют получать различные виды итоговой информации (рис. 1):
– SUM( ) вычисляет сумму всех значений, содержащихся в столбце;
– AVG( ) вычисляет среднее среди значений, содержащихся в столбце;
– MIN( ) находит наименьшее среди всех значений, содержащихся в столбце;
– MAX( ) находит наибольшее среди всех значений, содержащихся в столбце;
– COUNT( ) подсчитывает количество значений, содержащихся в столбце;
– COUNT(*) подсчитывает количество строк в таблице результатов запроса.
Аргументом агрегатной функции может быть простое имя столбца, как в предыдущем примере, или выражение, как в следующем запросе, задающем вычисление среднедушевого налога:
При выполнении этого запроса создается временный столбец, содержащий значения (SUMD*0.13) для каждой строки таблицы PERSON, а затем вычисляется среднее значение временного столбца.
Сумму доходов у всех жителей Зеленограда можно вычислить с помощью агрегатной функции SUM:
SELECT SUM(SUMD) FROM PERSON
Агрегатная функция может быть использована и для вычисления итогов по таблице результатов, полученной соединением нескольких исходных таблиц. Например, можно вычислить общую сумму дохода, которая получена жителями от источника с названием «Стипендия»:
FROM PROFIT, HAVE_D
Агрегатные функции MIN( ) и MAX( ) позволяют найти соответственно наименьшее и наибольшее значения в таблице. При этом столбец может содержать числовые или строковые значения либо значения даты или времени.
Например, можно определить:
(а) наименьший общий доход, полученный жителями, и наибольший налог, подлежащий уплате:
SELECT MIN(SUMD), MAX(SUMD*0.13)
(б) даты рождения самого старого и самого молодого жителя:
SELECT MIN(RDATE), MAX(RDATE)
(в) фамилии, имена и отчества самого первого и самого последнего жителей в списке, упорядоченном по алфавиту:
SELECT MIN(FIO), MAX(FIO)
Применяя эти агрегатные функции, нужно помнить, что числовые данные сравниваются по арифметическим правилам, сравнение дат происходит последовательно (более ранние значения дат считаются меньшими, чем более поздние), сравнение интервалов времени выполняется на основании их продолжительности.
При использовании функции MIN( ) и MAX( ) со строковыми данными результат сравнения двух строк зависит от используемой таблицы кодировки символов.
Агрегатная функция COUNT( ) подсчитывает количество значений в столбце любого типа:
(а) сколько квартир в 1-м микрорайоне?
SELECT COUNT(ADR) FROM FLAT WHERE ADR LIKE ‘%, 1_ _-%’
(б) сколько жителей имеют источники дохода?
SELECT COUNT(DISTINCT NOM) FROM HAVE_D
(в) сколько источников дохода используются жителями?
SELECT COUNT(DISTINCT ID) FROM HAVE_D (ключевой слово DISTINCT указывает, что подсчитываются неповторяющиеся значения в столбце).
Специальная агрегатная функция COUNT(*) подсчитывает строки в таблице результатов, а не значения данных:
(а) сколько квартир во 2-м микрорайоне?
SELECT COUNT(*) FROM FLAT WHERE ADR LIKE ‘%, 2__-%’
(б) сколько источников дохода у Иванова Ивана Ивановича?
SELECT COUNT(*) FROM PERSON, HAVE_D WHERE FIO=’Иванов Иван Иванович’ AND PERSON.NOM=HAVE_D.NOM
(в) сколько жителей проживает в квартире по определенному адресу?
SELECT COUNT(*) FROM PERSON WHERE ADR=’Зеленоград, 1001-45′
Один из способов понять, как выполняются итоговые запросы с агрегатными функциями, это представить выполнение запроса разбитым на две части. Сначала определяется, как бы запрос работал без агрегатных функций, возвращая несколько строк результатов. Затем применяются агрегатные функции к результатам запроса, возвращая одну итоговую строку.
Например, рассмотрим следующий сложный запрос: найти среднедушевой общий доход, сумму общих доходов жителей, а также среднюю доходность источника в процентах от общего дохода жителя. Ответ дает оператор
SELECT AVG(SUMD), SUM(SUMD), (100*AVG(MONEY/SUMD)) FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D. >
Без агрегатных функций запрос выглядел бы так:
SELECT SUMD, SUMD, MONEY/SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D. >
и возвращал бы одну строку результатов для каждого жителя и конкретного источника дохода. Агрегатные функции используют столбцы таблицы результатов этого запроса для получения однострочной таблицы с итоговыми результатами.
В строке возвращаемых столбцов вместо имени любого столбца можно указать агрегатную функцию. Например, она может входить в выражение, в котором суммируются или вычитаются значения двух агрегатных функций:
SELECT MAX(SUMD)-MIN(SUMD) FROM PERSON
Однако агрегатная функция не может быть аргументом для другой агрегатной функции, т.е. запрещены вложенные агрегатные функции.
Кроме того, в списке возвращаемых столбцов нельзя одновременно использовать агрегатные функции и обычные имена столбцов, поскольку в этом нет смысла, например:
SELECT FIO, SUM(SUMD) FROM PERSON
Здесь первый элемент списка указывает, чтобы СУБД создала таблицу, которая будет состоять из нескольких строк и содержать по одной строке для каждого жителя. Второй элемент списка просит СУБД получить одно результирующее значение, являющееся суммой значений столбца SUMD. Эти два указания противоречат друг другу, что приводит к ошибке.
По этой причине либо все ссылки на столбцы в списке возвращаемых столбцов должны являться аргументами агрегатных функций, либо в списке не должно быть ни одной агрегатной функции.
Сказанное не относится к случаям обработки подзапросов и запросов с группировкой.
Оператор SELECT. Использование агрегатных функций
Запрос может вычислять обобщенное групповое значение полей точно также как и значение одного поля. Это делается с помощью агрегатных функций.
Агрегатные функции находят одиночное значение для всей таблицы. К этим функциям относятся:
- COUNT — число значений в столбце (или не-NULL значений поля)
- SUM — арифметическая сумма значений столбца
- AVG — среднее значение столбца
- MAX — наибольшее значение столбца
- MIN — наименьшее значение столбца.
Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.
С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля. Когда они используются с символьными полями, MIN будет означать первое, а MAX последнее значение в алфавитном порядке.
возвращает первый по алфавиту город – [San Cristobal de] la Laguna.
возвращает последний по алфавиту город – Zytomyr.
Функция COUNT считает число значений в данном поле (квадратные скобки используются в случае, если имя содержит пробелы)
SELECT COUNT(CityName) AS [Linnade arv]
FROM City;
Даст следующий результат:
При вычислении количества значений в заданном поле пустые значения (NULL) не учитываются. Так при применении функции COUNT к полю district, имеющему и пустые значения, результат будет отличаться от предыдущего (хотя в таблице содержиться 4079 городов, не пустых значений поля district всего 4057:
SELECT COUNT(district) AS [Linnade arv]
FROM City;
Чтобы подсчитать общее число строк в таблице, используют функцию COUNT со звездочкой вместо имени поля, как например в следующем примере. COUNT со звездочкой включает и NULL и дубликаты.
SELECT COUNT(*) AS [Linnade arv]
FROM City;
Внимание! Агрегат MAX/MIN нельзя использовать в предложении WHERE.
Задание. Попробуй создать SQL запросы для вывода следующей информации:
- Число городов континента Европа, представленных в базе данных
- Наибольшее и наименьшее число жителей городов Европы
- Среднее число жителей городов Европы
Запросы могут производить обобщенное групповое значение полей точно также как и значение одного пол. Это делает с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций:
* COUNT — производит номера строк или не-NULL значения полей которые выбрал запрос.
* SUM — производит арифметическую сумму всех выбранных значений данного пол.
* AVG — производит усреднение всех выбранных значений данного пол.
* MAX — производит наибольшее из всех выбранных значений данного пол.
* MIN — производит наименьшее из всех выбранных значений данного пол.
КАК ИСПОЛЬЗОВАТЬ АГРЕГАТНЫЕ ФУНКЦИИ ?
Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением, они берут имена пол как аргументы. Только числовые пол могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые или символьные пол. Когда они используются с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, который должен сообщать, что MIN будет означать первое, а MAX последнее значение в алфавитном порядке( выдача алфавитного упорядочения обсуждается более подробно в Главе 4 ).
Чтобы найти SUM всех наших покупок в таблицы Порядков, мы можем ввести следующий запрос, с его выводом в Рисунке 6.1: Рисунок 6.1: Выбор суммы
Это конечно, отличается от выбора пол при котором возвращается одиночное значение, независимо от того сколько строк находится в таблице. Из-за этого, агрегатные функции и пол не могут выбираться одновременно, пока предложение GROUP BY (описанное далее) не будет использовано. Нахождение усредненной суммы — это похожа операция ( вывод следующего запроса показывается в Рисунке 6.2 ): Рисунок 6.2: Выбор среднего
СПЕЦИАЛЬНЫЕ АТРИБУТЫ COUNT
ИСПОЛЬЗОВАНИЕ DISTINCT
Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем пол с которым он применяется, помещен в круглые скобки, но не сразу после SELECT, как раньше. Этого использования DISTINCT с COUNT применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют к ним такого требования. Рисунок 6.3: Подсчет значений пол
Вы можете выбирать многочисленные счета( COUNT ) из полей с помощью DISTINCT в одиночном запросе который, как мы видели в Главе 3, не выполнялись когда вы выбирали строки с помощью DISTINCT. DISTINCT может использоваться таким образом, с любой функцией агрегата, но наиболее часто он используется с COUNT. С MAX и MIN, это просто не будет иметь никакого эффекта, а SUM и AVG, вы обычно применяете для включения повторяемых значений, так как они законно эффективнее общих и средних значений всех столбцов.
ИСПОЛЬЗОВАНИЕ COUNT СО СТРОКАМИ, А НЕ ЗНАЧЕНИЯМИ
Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени пол, как например в следующем примере, вывод из которого показан на Рисунке 6.4: COUNT со звездочкой включает и NULL и дубликаты, по этой причине DISTINCT не может быть использован. DISTINCT может производить более высокие номера чем COUNT особого пол, который удаляет все Рисунок 6. 4: Подсчет строк вместо значений
строки, имеющие избыточные или NULL данные в этом поле. DISTINCT не применим c COUNT (*), потому, что он не имеет никакого действия в хорошо разработанной и поддерживаемой базе данных. В такой базе данных, не должно быть ни таких строк, которые бы являлись полностью пустыми, ни дубликатов ( первые не содержат никаких данных, а последние полностью избыточны ). Если, с другой стороны, все таки имеются полностью пустые или избыточные строки, вы вероятно не захотите чтобы COUNT скрыл от вас эту информацию.
ВКЛЮЧЕНИЕ ДУБЛИКАТОВ В АГРЕГАТНЫЕ ФУНКЦИИ
Агрегатные функции могут также ( в большинстве реализаций ) использовать аргумент ALL, который помещается перед именем пол, подобно DISTINCT, но означает противоположное: — включать дубликаты. ANSI технически не позволяет этого для COUNT, но многие реализации ослабляют это ограничение. Различи между ALL и * когда они используются с COUNT —
* ALL использует имя_поля как аргумент.
* ALL не может подсчитать значения NULL.
Пока * является единственным аргументом который включает NULL значения, и он используется только с COUNT; функции отличные от COUNT игнорируют значения NULL в любом случае. Следующая команда подсчитает(COUNT) число не-NULL значений в поле rating в таблице Заказчиков ( включая повторения ):
АГРЕГАТЫ ПОСТРОЕННЫЕ НА СКАЛЯРНОМ ВЫРАЖЕНИИ
ПРЕДЛОЖЕНИЕ GROUP BY
Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого пол, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять пол и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Порядков для каждого значения пол snum. GROUP BY, однако, позволит Вам поместить их все в одну команду: Вывод для этого запроса показывается в Рисунке 6.5. Рисунок 6.5: Нахождение максимальной суммы продажи у каждого продавца
GROUP BY применяет агрегатные функции независимо от серий групп которые определяются с помощью значения поля в целом. В этом случае, каждая группа состоит из всех строк с тем же самым значением пол snum, и MAX функция применяется отдельно для каждой такой группы. Это значение пол, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, также как это делает агрегатная функция. Результатом является совместимость которая позволяет агрегатам и полям объединяться таким образом. Вы можете также использовать GROUP BY с многочисленными полями. Совершенству вышеупомянутый пример далее, предположим что вы хотите увидеть наибольшую сумму приобретений получаемую каждым продавцом каждый день. Чтобы сделать это, вы должны сгруппировать таблицу Порядков по датам продавцов, и применить функцию MAX к каждой такой группе, подобно этому: Вывод для этого запроса показывается в Рисунке 6.6. Рисунок 6.6: Нахождение наибольшей суммы приобретений на каждый день
Конечно же, пустые группы, в дни когда текущий продавец не имел порядков, не будут показаны в выводе.
ПРЕДЛОЖЕНИЕ HAVING
Предположим, что в предыдущем примере, вы хотели бы увидеть только максимальную сумму приобретений значение которой выше $3000.00. Вы не сможете использовать агрегатную функцию в предложении WHERE ( если вы не используете подзапрос, описанный позже ), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах групп строк. Это означает что вы не сможете сделать что-нибудь подобно следующему: Это будет отклонением от строгой интерпретации ANSI. Чтобы увидеть максимальную стоимость приобретений свыше $3000.00, вы можете использовать предложение HAVING. Предложение HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк. Правильной командой будет следующая: Вывод для этого запроса показывается в Рисунке 6. 7. Рисунок 6. 7: Удаление групп агрегатных значений
Аргументы в предложении HAVING следуют тем же самым правилам что и в предложении SELECT, состоящей из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода. Следующая команда будет запрещена: Поле оdate не может быть вызвано предложением HAVING, потому что оно может иметь ( и действительно имеет ) больше чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос( вывод показывается в Рисунке 6.8 ): Рисунок 6.8: Максимальное значение суммы приобретений у каждого продавца на 3 Октября
Поскольку пол odate нет, не может быть и выбранных полей, значение этих данных меньше чем в некоторых других примерах. Вывод должен вероятно включать что-нибудь такое что говорит — " это — самые большие порядки на 3 Октября." В Главе 7, мы покажем как вставлять текст в ваш вывод. Как и говорилось ранее, HAVING может использовать только аргументы которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и пол выбранные с помощью GROUP BY также допустимы. Например, мы хотим увидеть наибольшие порядки для Serres и Rifkin: Вывод для этого запроса показывается в Рисунке 6.9. Рисунок 6. 9: Использование HAVING с GROUP BY полями
НЕ ДЕЛАЙТЕ ВЛОЖЕННЫХ АГРЕГАТОВ
РЕЗЮМЕ
РАБОТА С SQL
1. Напишите запрос который сосчитал бы все суммы приобретений на 3 Октября.
2. Напишите запрос который сосчитал бы число различных не-NULL значений пол city в таблице Заказчиков.
3. Напишите запрос который выбрал бы наименьшую сумму для каждого заказчика.
4. Напишите запрос который бы выбирал заказчиков в алфавитном порядке, чьи имена начинаются с буквы G.
5. Напишите запрос который выбрал бы высшую оценку в каждом городе.
6. Напишите запрос который сосчитал бы число заказчиков регистрирующих каждый день свои порядки. (Если продавец имел более одного порядка в данный день, он должен учитываться только один раз.)