Агрегатные функции
Для подведения итогов по информации, содержащейся в БД, в SQL предусмотрены агрегатные функции. Агрегатная функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец.
Например, агрегатная функция AVG() принимает в качестве аргумента столбец чисел и вычисляет их среднее значение.
Чтобы вычислить среднедушевой доход жителя Зеленограда, нужен такой запрос:
SELECT ‘СРЕДНЕДУШЕВОЙ ДОХОД=’, AVG(SUMD)
FROM PERSON
В SQL имеется шесть агрегатных функций, которые позволяют получать различные виды итоговой информации (рис. 1):
– SUM( ) вычисляет сумму всех значений, содержащихся в столбце;
– AVG( ) вычисляет среднее среди значений, содержащихся в столбце;
– MIN( ) находит наименьшее среди всех значений, содержащихся в столбце;
– MAX( ) находит наибольшее среди всех значений, содержащихся в столбце;
– COUNT( ) подсчитывает количество значений, содержащихся в столбце;
– COUNT(*) подсчитывает количество строк в таблице результатов запроса.
Аргументом агрегатной функции может быть простое имя столбца, как в предыдущем примере, или выражение, как в следующем запросе, задающем вычисление среднедушевого налога:
SELECT AVG(SUMD*0.13)
FROM PERSON
При выполнении этого запроса создается временный столбец, содержащий значения (SUMD*0.13) для каждой строки таблицы PERSON, а затем вычисляется среднее значение временного столбца.
Сумму доходов у всех жителей Зеленограда можно вычислить с помощью агрегатной функции SUM:
SELECT SUM(SUMD) FROM PERSON
Агрегатная функция может быть использована и для вычисления итогов по таблице результатов, полученной соединением нескольких исходных таблиц. Например, можно вычислить общую сумму дохода, которая получена жителями от источника с названием «Стипендия»:
SELECT SUM(MONEY)
FROM PROFIT, HAVE_D
WHERE PROFIT.ID=HAVE_D.ID
AND PROFIT.SOURCE=’Стипендия’
Агрегатные функции MIN( ) и MAX( ) позволяют найти соответственно наименьшее и наибольшее значения в таблице. При этом столбец может содержать числовые или строковые значения либо значения даты или времени.
Например, можно определить:
(а) наименьший общий доход, полученный жителями, и наибольший налог, подлежащий уплате:
SELECT MIN(SUMD), MAX(SUMD*0.13)
FROM PERSON
(б) даты рождения самого старого и самого молодого жителя:
SELECT MIN(RDATE), MAX(RDATE)
FROM PERSON
(в) фамилии, имена и отчества самого первого и самого последнего жителей в списке, упорядоченном по алфавиту:
SELECT MIN(FIO), MAX(FIO)
FROM PERSON
Применяя эти агрегатные функции, нужно помнить, что числовые данные сравниваются по арифметическим правилам, сравнение дат происходит последовательно (более ранние значения дат считаются меньшими, чем более поздние), сравнение интервалов времени выполняется на основании их продолжительности.
При использовании функции 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.ID=PROFIT.ID
Без агрегатных функций запрос выглядел бы так:
SELECT SUMD, SUMD, MONEY/SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D.ID=PROFIT.ID
и возвращал бы одну строку результатов для каждого жителя и конкретного источника дохода. Агрегатные функции используют столбцы таблицы результатов этого запроса для получения однострочной таблицы с итоговыми результатами.
В строке возвращаемых столбцов вместо имени любого столбца можно указать агрегатную функцию. Например, она может входить в выражение, в котором суммируются или вычитаются значения двух агрегатных функций:
SELECT MAX(SUMD)-MIN(SUMD) FROM PERSON
Однако агрегатная функция не может быть аргументом для другой агрегатной функции, т.е. запрещены вложенные агрегатные функции.
Кроме того, в списке возвращаемых столбцов нельзя одновременно использовать агрегатные функции и обычные имена столбцов, поскольку в этом нет смысла, например:
SELECT FIO, SUM(SUMD) FROM PERSON
Здесь первый элемент списка указывает, чтобы СУБД создала таблицу, которая будет состоять из нескольких строк и содержать по одной строке для каждого жителя. Второй элемент списка просит СУБД получить одно результирующее значение, являющееся суммой значений столбца SUMD. Эти два указания противоречат друг другу, что приводит к ошибке.
По этой причине либо все ссылки на столбцы в списке возвращаемых столбцов должны являться аргументами агрегатных функций, либо в списке не должно быть ни одной агрегатной функции.
Сказанное не относится к случаям обработки подзапросов и запросов с группировкой.