Запрос с использованием агрегатных функций sql. Агрегатные функции SQL - SUM, MIN, MAX, AVG, COUNT

Использование агрегатных функций

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

Общий формат унарной агрегатной функции следующий:

имя_функции([АLL | DISTINCT] выражение)

где DISTINCT указывает, что функция должна рассматривать только различные значения аргумента, а ALL - все значения, включая повторяющиеся (этот вариант используется по умолчанию). Например, функция AVG с ключевым словом DISTINCT для строк столбца со значениями 1, 1, 1 и 3 вернет 2, а при наличии ключевого слова ALL вернет 1,5.

Агрегатные функции применяются во фразах SELECT и HAVING. Здесь мы рассмотрим их использование во фразе SELECT. В этом случае выражение в аргументе функции применяется ко всем строкам входной таблицы фразы SELECT. Кроме того, во фразе SELECT нельзя использовать и агрегатные функции, и столбцы таблицы (или выражения с ними) при отсутствии фразы GROUP BY, которую мы рассмотрим в следующем разделе.

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

• COUNT(*)
• COUNT( выражение)

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

Запрос: Количество видов продукции, информация о которых имеется в базе данных.

SELECT COUNT(*) AS "Количество видов продукции"

FROM Product

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

Запрос: Количество различных имен, содержащихся в таблице Customer.

SELECT COUNT (DISTINCT FNAME)

FROM Customer

Использование остальных унарных агрегатных функции аналогично COUNT за тем исключением, что для функций MIN и MAX использование ключевых слов DISTINCT и ALL не имеет смысла. С функциями COUNT, MAX и MIN кроме числовых могут использоваться и символьные поля. Если аргумент агрегатной функции не содержит значений, функция COUNT возвращает 0, а все остальные - значение NULL.

SELECT MAX (OrdDate)

FROM

WHERE OrdDate"1.09.2010"

Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных:

• Суммарная стоимость всех заказов;
• Количество различных городов, содержащихся в таблице Customer.

На уроке будет рассмотрена тема sql переименование столбца (полей) при помощи служебного слова AS; также рассмотрена тема агрегатные функции в sql. Будут разобраны конкретные примеры запросов

Имена столбцов в запросах можно переименовывать. Это придает результатам более читабельный вид.

В языке SQL переименование полей связано с использованием ключевого слова AS , которое и используется для переименования имен полей в результирующих наборах

Синтаксис:

SELECT <имя поля> AS <псевдоним> FROM …

Рассмотрим пример переименования в SQL:

Пример БД «Институт»: Вывести фамилии учителей и их зарплаты, для тех преподавателей, у которых зарплата ниже 15000, переименовать поле zarplata на «низкая_зарплата»

✍ Решение:

Переименование столбцов в SQL часто необходимо при вычислении значений, связанных с несколькими полями таблицы. Рассмотрим пример:

Пример БД «Институт»: Из таблицы teachers вывести поле name и вычислить сумму зарплаты и премии, назвав поле «зарплата_премия»

✍ Решение:

1 2	SELECT name, (zarplata+ premia) AS zarplata_premia FROM teachers;

SELECT name, (zarplata+premia) AS zarplata_premia FROM teachers;

Результат:

Агрегатные функции в SQL

Для получения итоговых значений и вычисления выражений используются агрегатные функции в sql:

Все агрегатные функции возвращают единственное значение.

Функции COUNT , MIN и MAX применимы к любым типам данных.

Функции SUM и AVG используются только для числовых полей.
Между функциями COUNT(*) и COUNT() есть разница: вторая при подсчете не учитывает NULL -значения.

Важно: при работе с агрегатными функциями в SQL используется служебное слово AS

Пример БД «Институт»: Получить значение самой большой зарплаты среди учителей, вывести итог как «макс_зп»

✍ Решение:

SELECT MAX (zarplata) AS макс_зп FROM teachers;

SELECT MAX(zarplata) AS макс_зп FROM teachers;

Результаты:

Рассмотрим более сложный пример использования агрегатных функций в sql.

✍ Решение:

Предложение GROUP BY в SQL

Оператор group by в sql обычно используется совместно с агрегатными функциями.

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

Рассмотрим пример с таблицей lessons:

Пример:

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

При этом стоит учесть, что при группировке по полю, содержащему NULL -значения, все такие записи попадут в одну группу.

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

Оператор Having SQL

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

Пример: БД Компьютерный магазин . Посчитать среднюю цену компьютеров с одинаковой скоростью процессора. Выполнить подсчет только для тех групп, средняя цена которых меньше 30000.

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

Каждый столбец в списке выборки должен присутствовать в предложении GROUP BY, исключение составляют только константы и столбцы — операнды агрегатных функций.

Таблицу можно сгруппировать по любой комбинации ее столбцов.

Агрегатные функции используются для получения из группы строк одного единственного суммарного значения. Все агрегатные функции выполняют вычисления над одним аргументом, который может быть или столбцом, или выражением. Результатом вычислений любой агрегатной функции является константное значение, отображаемое в отдельном столбце результата.

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

Если запрос содержит предложение WHERE, то агрегатные функции вычисляют значение для результатов выборки.

Агрегатные функции MIN и MAX вычисляют наименьшее и наибольшее значение столбца соответственно. Аргументами могут быть числа, строки и даты. Все значения NULL удаляются перед вычислением (т.е. в расчет не берутся).

Агрегатная функция SUM вычисляет общую сумму значений столбца. Аргументами могут быть только числа. Использование параметра DISTINCT устраняет все повторяющиеся значения в столбце перед применением функции SUM. Аналогично удаляются все значения NULL перед применением этой агрегатной функции.

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

Агрегатная функция COUNT имеет две разные формы:

• COUNT( col_name) — подсчитывает количество значений в столбце col_name, значения NULL не учитываются
• COUNT(*) — подсчитывает количество строк в таблице, значения NULL также учитываются

Если в запросе используется ключевое слово DISTINCT, перед применением функции COUNT удаляются все повторяющиеся значения столбца.

Функция COUNT_BIG аналогична функции COUNT. Единственное различие между ними заключается в типе возвращаемого ими результата: функция COUNT_BIG всегда возвращает значения типа BIGINT, тогда как функция COUNT возвращает значения данных типа INTEGER.

В предложении HAVING определяется условие, которое применяется к группе строк. Оно имеет такой же смысл для групп строк, что и предложение WHERE для содержимого соответствующей таблицы (WHERE применяется до группировки, HAVING после).

Для подведения итогов по информации, содержащейся в БД, в SQL предусмотрены агрегатные функции. Агрегатная функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец.

Например, агрегатная функция AVG() принимает в качестве аргумента столбец чисел и вычисляет их среднее значение.

Чтобы вычислить среднедушевой доход жителя Зеленограда, нужен такой запрос:

SELECT ‘СРЕДНЕДУШЕВОЙ ДОХОД=’, AVG(SUMD)

В SQL имеется шесть агрегатных функций, которые позволяют получать различные виды итоговой информации (рис. 1):

– SUM() вычисляет сумму всех значений, содержащихся в столбце;

– AVG() вычисляет среднее среди значений, содержащихся в столбце;

– MIN() находит наименьшее среди всех значений, содержащихся в столбце;

– MAX() находит наибольшее среди всех значений, содержащихся в столбце;

– COUNT() подсчитывает количество значений, содержащихся в столбце;

– COUNT(*) подсчитывает количество строк в таблице результатов запроса.

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

SELECT AVG(SUMD*0.13)

При выполнении этого запроса создается временный столбец, содержащий значения (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)

(б) даты рождения самого старого и самого молодого жителя:

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.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. Эти два указания противоречат друг другу, что приводит к ошибке.

Сказанное не относится к случаям обработки подзапросов и запросов с группировкой.

по значению столбца Дисциплина . Мы получим 4 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка . Таблица 5.7. Агрегатные функции

Функция	Результат
COUNT	Количество строк или непустых значений полей, которые выбрал запрос
SUM	Сумма всех выбранных значений данного поля
AVG	Среднеарифметическое значение всех выбранных значений данного поля
MIN	Наименьшее из всех выбранных значений данного поля
MAX	Наибольшее из всех выбранных значений данного поля

R1
	ФИО	Дисциплина	Оценка
Группа 1	Петров Ф. И.	Базы данных	5
	Сидоров К. А.	Базы данных	4
	Миронов А. В.	Базы данных	2
	Степанова К. Е.	Базы данных	2
	Крылова Т. С.	Базы данных	5
	Владимиров В. А.	Базы данных	5
Группа 2	Сидоров К. А.	Теория информации	4
	Степанова К. Е.	Теория информации	2
	Крылова Т. С.	Теория информации	5
	Миронов А. В.	Теория информации	Null
Группа 3	Трофимов П. А.	Сети и телекоммуникации	4
	Иванова Е. А.	Сети и телекоммуникации	5
	Уткина Н. В.	Сети и телекоммуникации	5
Группа 4	Владимиров В. А.	Английский язык	4
	Трофимов П. А.	Английский язык	5
	Иванова Е. А.	Английский язык	3
	Петров Ф. И.	Английский язык	5

Агрегатные функции используются подобно именам полей в операторе SELECT , но с одним исключением: они берут имя поля как аргумент . С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT , MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.

Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю "Дисциплина" и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.

SELECT R1.Дисциплина, COUNT(*) FROM R1 GROUP BY R1.Дисциплина

Результат:

Если же мы хотим сосчитать количество сдавших экзамен по какой-либо дисциплине, то нам необходимо исключить неопределенные значения из исходного отношения перед группировкой. В этом случае запрос будет выглядеть следующим образом:

Получим результат:

В этом случае строка со студентом

Миронов А. В.	Теория информации	Null

не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины " Теория информации " будет на 1 меньше.

Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу.

Обратившись снова к базе данных "Сессия" (таблицы R1, R2, R3 ), найдем количество успешно сданных экзаменов:

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

Результат:

В результат можно включить значение поля группировки и несколько агрегатных функций , а в условиях группировки можно использовать несколько полей. При этом группы образуются по набору заданных полей группировки. Операции с агрегатными функциями могут быть применены к объединению множества исходных таблиц. Например, поставим вопрос: определить для каждой группы и каждой дисциплины количество успешно сдавших экзамен и средний балл по дисциплине.

Результат:

Мы не можем использовать агрегатные функции в предложении WHERE , потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции - в терминах групп строк.

Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT . Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT , так и в выражении условия обработки сформированных групп HAVING . В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций , могут быть использованы для вывода соответствующих результатов или для условия отбора групп.

Построим запрос , который выводит группы, в которых по одной дисциплине на экзаменах получено больше одной двойки:

В дальнейшем в качестве примера будем работать не с БД "Сессия", а с БД "Банк", состоящей из одной таблицы F , в которой хранится отношение F, содержащее информацию о счетах в филиалах некоторого банка:

F = (N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток); Q = (Филиал, Город);

поскольку на этой базе можно ярче проиллюстрировать работу с агрегатными функциями и группировкой.

Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY , однако, позволит поместить их все в одну команду:

SELECT Филиал, SUM(Остаток) FROM F GROUP BY Филиал;

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