Могут производить обобщенную групповую обработку значений полей. Это осуществляется с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. В SQL предусмотрены следующие агрегатные функции:
- COUNT – производит подсчет количества строк таблицы с не-NULL значениями указанного в качестве аргумента поля.
- SUM – вычисляет арифметическую сумму всех выбранных значений данного поля.
- AVG – производит усреднение всех выбранных значений данного поля.
- MAX – выводит наибольшее значение из всех выбранных значений данного поля.
- MIN
– выводит наименьшее значение из всех выбранных значений данного поля.
Использование агрегатных функций
Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением: они берут имена полей в качестве аргумента. Только числовые поля могут использоваться с SUM и AVG . С COUNT , MAX , и MIN могут использоваться и числовые, и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII. Это означает, что MIN будет выбирать первое, а MAX последнее значение в алфавитном порядке.
Чтобы найти общую сумму продаж в таблице продаж, мы должны написать следующий запрос:
SELECT SUM(SSum) FROM Sells
В результате получим:
Этот запрос подсчитал количество непустых значений в поле SNum таблицы Sells. Если же мы перепишем запрос в следующем виде:
SELECT COUNT(SDate) FROM Sells
То в результате получим:
COUNT OF SDate 4 Различные результаты запросов при вычислении, казалось бы, одного и того же, получены, потому что одно из значений поля SDate имеет пустое значение (NULL ). Будьте внимательны при использовании таких запросов.
- Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.
Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю «Дисциплина» и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.
SELECT R1. Дисциплина, COUNT(*)
GROUP BY R1.Дисциплина;
Результат:
SELECT R1.Дисциплина, COUNT (*)
WHERE R1. Оценка IS NOT NULL
GROUP BY R1.Дисциплина;
Результат:
не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины «Теория информации» будет на 1 меньше.
Аналогичный результат можно получить, если записать запрос следующим способом:
SELECT R1. Дисциплина, COUNT(R1. Оценка)
GROUP BY R1. Дисциплина;
Функция COUNT (ИМЯ АТРИБУТА) считает количество определенных значений в группе, в отличие от функции COUNT(*), которая считает количество строк в группе. Действительно, в группе с дисциплиной «Теория информации» будет 4 строки, но только 3 определенных значения атрибута «Оценка».
Правила обработки значений NULL в агрегатных функциях
Если какие-либо значения в столбце равны NULL при вычислении результата функции они исключаются.
Если все значения в столбце равны NULL , то Max Min Sum Avg = NULL , count = 0 (ноль).
Если таблица пуста, count(*) = 0 .
Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу.
Правила интерпретации агрегатных функций
Агрегатные функции могут быть включены в список вывода и тогда они применяются ко всей таблице.
SELECT MAX (Оценка) from R1 даст максимальную оценку на сессии;
SELECT SUM (Оценка) from R1 даст сумму всех оценок на сеcси;
SELECT AVG(Оценка) from R1 даст среднюю оценку по всей сессии.
2; Результат: " width="640"
Обратившись снова к базе данных «Сессия» (таблицы R1), найдем количество успешно сданных экзаменов:
SELECT COUNT(*) As Сдано _ экзаменов
WHERE Оценка 2;
Результат:
Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Для того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца. Вычислим количество различных оценок, полученных по каждой дисциплине:
SELECT R1.Дисциплина, COUNT (DISTINCT R1.Оценка)
WHERE R1. Оценка IS NOT NULL
GROUP BY R1.Дисциплина;
Результат:
Тот же самый результат получается, если исключить явное условие в части WHERE, в этом случае запрос будет им следующий вид:
SELECT R1. Дисциплина, COUNT(DISTINCT R1. Оценка)
GROUP BY R1. Дисциплина;
Функция COUNT (DISTINCT R1.Оценка) считает только определенные различные значения.
Для того чтобы и в этом случае был получен нужный результат, необходимо сделать предварительное преобразование типа данных столбца «Оценка», приведя его к действительному типу, тогда и результат вычисления среднего не будет целым числом. В этом случае запрос будет выглядеть следующим образом:
2 Group by R2. Группа, R1. Дисциплина; Здесь функция CAST() выполняет преобразование столбца «Оценка» к действительному типу данных. " width="640"
Select R2.Группа, R1.Дисциплина,Count(*) as Всего, AVG(cast(Оценка as decimal(3,1))) as Средний_балл
From R1,R2
where R1. ФИО = R2. ФИО and R1. оценка is not null
and R1. Оценка 2
Group by R2. Группа, R1. Дисциплина;
Здесь функция CAST() выполняет преобразование столбца «Оценка» к действительному типу данных.
Нельзя использовать агрегатные функции в предложении WHERE, потому что условия в этом разделе оцениваются в терминах одиночной строки, а агрегатные функции - в терминах групп строк.
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.
Построим запрос, который выводит группы, в которых по одной дисциплине на экзаменах получено больше одной двойки:
1; Результат: " width="640"
SELECT R2. Группа
FROM R1,R2
WHERE R1. ФИО = R2. ФИО AND
R1.Оценка = 2
GROUP BY R2.Группа, R1.Дисциплина
HAVING count(*) 1;
Результат:
Имеем БД «Банк», состоящую из одной таблицы F, в которой хранится отношение F, содержащее информацию о счетах в филиалах некоторого банка:
Найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM (Остаток) из таблицы для каждого филиала, но операция группировки GROUP BY, позволит поместить их все в одну команду:
SELECT Филиал , SUM( Остаток )
GROUP BY Филиал;
GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, т. е. суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY , имеет по определению только одно значение на группу вывода, как и результат работы агрегатной функции.
5 000; Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT , где используется GROUP BY . Они должны иметь одно значение на группу вывода. " width="640"
Предположим, что выбрать только те филиалы, суммарные значения остатков на счетах которых превышают $5 000, а также суммарные остатки для выбранных филиалов. Чтобы вывести в результат филиалы, суммарные остатки в которых свыше $5 000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же, как предложение WHERE делает это для индивидуальных строк.
Правильной командой будет следующая:
SELECT Филиал, SUM(Остаток)
GROUP BY Филиал
HAVING SUM ( Остаток ) 5 000;
Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT , где используется GROUP BY . Они должны иметь одно значение на группу вывода.
Следующая команда будет запрещена:
SELECT Филиал,SUM(Остаток)
GROUP BY Филиал
HAVING ДатаОткрытия = 27/12/2004 ;
Поле ДатаОткрытия не может быть использовано в предложении HAVING , потому что оно может иметь больше, чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY . Имеется правильный способ сделать вышеупомянутый запрос:
SELECT Филиал,SUM(Остаток)
WHERE ДатаОткрытия = ’27/12/2004’
GROUP BY Филиал;
Смысл данного запроса следующий: найти сумму остатков по каждому филиалу счетов, открытых 27 декабря 2004 года.
Как говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически ссылки на агрегатные функции - наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:
SELECT Филиал, SUM(Остаток)
FROM F,Q
WHERE F. Филиал = Q. Филиал
GROUP BY Филиал
HAVING Филиал IN (‘Санкт-Петербург’, ‘Псков’, ‘Урюпинск’);
100 000; Если суммарный остаток более чем $100 000, то мы его увидим в результирующем отношении, в противном случае мы получим пустое отношение. " width="640"
Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия отбора в части HAVING есть TRUE. В частности, если раздел HAVING присутствует в запросе, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования. Рассмотрим пример. Допустим, мы хотим вывести общую сумму остатков по всем филиалам, но только в том случае, если она более $100 000. В этом случае наш запрос не будет содержать операции группировки, но будет содержать раздел HAVING и будет выглядеть следующим образом:
SELECT SUM( Остаток )
HAVING SUM( Остаток ) 100 000;
Если суммарный остаток более чем $100 000, то мы его увидим в результирующем отношении, в противном случае мы получим пустое отношение.
Здравствуйте! Сегодня мы познакомимся с агрегатными функциями в SQL, подробно разберем их работу с данными из таблиц, которые создавали в прошлых уроках.
Общее понятие
В прошлом уроке по мы познакомились с тем, как строятся запросы к данным. Агрегатные функции же существуют для того, чтобы была возможность каким либо образом обобщить полученные данные, то есть манипулировать ими так, как нам это захочется.
Эти функции выполняются с помощью ключевых слов, которые включаются в запрос SELECT, и о том, как они прописываются будет рассказано далее. Чтобы было понятно, вот некоторые возможности агрегатных функций в SQL:
- Суммировать выбранные значения
- Находить среднее арифметическое значений
- Находить минимальное и максимальное из значений
Примеры агрегатных функций SQL
Мы разберем самые часто используемые функции и приведем несколько примеров.
Функция SUM
Эта функция позволяет просуммировать значения какого либо поля при запросе SELECT. Достаточно полезная функция, синтаксис которой довольно прост, как и всех других агрегатных функций в SQL. Для понимания сразу начнем с примера:
Получить сумму всех заказов из таблицы Orders, которые были совершены в 2016 году.
Можно было бы просто вывести сумму заказов, но мне кажется, что это совсем просто. Напомним структуру нашей таблицы:
| onum | amt | odate | cnum | snum |
|---|---|---|---|---|
| 1001 | 128 | 2016-01-01 | 9 | 4 |
| 1002 | 1800 | 2016-04-10 | 10 | 7 |
| 1003 | 348 | 2017-04-08 | 2 | 1 |
| 1004 | 500 | 2016-06-07 | 3 | 3 |
| 1005 | 499 | 2017-12-04 | 5 | 4 |
| 1006 | 320 | 2016-03-03 | 5 | 4 |
| 1007 | 80 | 2017-09-02 | 7 | 1 |
| 1008 | 780 | 2016-03-07 | 1 | 3 |
| 1009 | 560 | 2017-10-07 | 3 | 7 |
| 1010 | 900 | 2016-01-08 | 6 | 8 |
Следующий код осуществит нужную выборку:
SELECT SUM (amt) FROM Orders WHERE odate BETWEEN "2016-01-01" and "2016-12-31" ;В результате получим:
| SUM(amt) |
|---|
| 4428 |
В данном запросе мы использовали функцию SUM , после которой в скобках нужно указать поле для суммирования. Затем мы указали условие в WHERE, которое отобрало строчки только с 2016 годом. На самом деле это условие можно записать по другому, но сейчас важнее агрегатная функция суммирования в SQL.
Функция AVG
Следующая функция осуществляет подсчет среднего арифметического поля данных, которое мы укажем в качестве параметра. Синтаксис такой функции идентичен функции суммирования. Поэтому сразу перейдем к простейшей задаче:
Вывести среднюю стоимость заказа из таблицы Orders.
И сразу запрос:
SELECT AVG (amt) FROM Orders;В результате получим:
Также стоит сказать, что в отличие от предыдущих функций, эти 2 могут работать с символьными параметрами, то есть можно написать запрос типа MIN(odate) (в данном случае дата у нас символьная), и тогда нам вернется 2016-01-01.
Дело в том, что в этих функциях есть механизм преобразования символов в ASCII код, который потом они и сравнивают.
Еще одним важным моментом является то, что мы можем производить некоторые простые математические операции в запросе SELECT, например, такой запрос:
SELECT (MAX (amt) - MIN (amt)) AS "Разница" FROM Orders;Вернет такой ответ:
Очевидно, что количество заказов — 10, но если вдруг у вас имеется большая таблица, то такая функция будет очень удобной. Что касается уникальных продавцов, то здесь необходимо использовать DISTINCT, потому что один продавец может обслужить несколько заказов.
Оператор GROUP BY
Теперь рассмотрим 2 важных оператора, которые помогают расширить функционал наших запросов в SQL. Первым из них является оператор GROUP BY, который осуществляет группировку по какому либо полю, что иногда является необходимым. И уже для этой группы производит заданное действие. Например:
Вывести сумму всех заказов для каждого продавца по отдельности.
То есть теперь нам нужно для каждого продавца в таблице Orders выделить поля с ценой заказа и просуммировать. Все это сделает оператор GROUP BY в SQL достаточно легко:
SELECT snum, SUM (amt) AS "Сумма всех заказов" FROM Orders GROUP BY snum;И в итоге получим:
| snum | Сумма всех заказов |
|---|---|
| 1 | 428 |
| 3 | 1280 |
| 4 | 947 |
| 7 | 2360 |
| 8 | 900 |
Как видно, SQL выделил группу для каждого продавца и посчитал сумму всех их заказов.
Оператор HAVING
Этот оператор используется как дополнение к предыдущему. Он необходим для того, чтобы ставить условия для выборки данных при группировке. Если условие выполняется то выделяется группа, если нет — то ничего не произойдет. Рассмотрим следующий код:
SELECT snum, SUM (amt) AS "Сумма всех заказов" FROM Orders GROUP BY snum HAVING MAX (amt) > 1000;Который создаст группу для продавца и посчитает сумму заказов этой группы, только в том случае, если максимальная сумма заказа больше 1000. Очевидно, что такой продавец только один, для него выделится группа и посчитается сумма всех заказов:
| snum | Сумма всех заказов |
|---|---|
| 7 | 2360 |
Казалось бы, почему тут не использовать условие WHERE, но SQL так построен, что в таком случае выдаст ошибку, и именно поэтому в SQL есть оператор HAVING.
Примеры на агрегатные функции в SQL
1. Напишите запрос, который сосчитал бы все суммы заказов, выполненных 1 января 2016 года.
SELECT SUM (amt) FROM Orders WHERE odate = "2016-01-01" ;2. Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице заказчиков.
SELECT COUNT (DISTINCT city) FROM customers;3. Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.
SELECT cnum, MIN (amt) FROM orders GROUP BY cnum;4. Напишите запрос, который бы выбирал заказчиков чьи имена начинаются с буквы Г.
SELECT cname FROM customers WHERE cname LIKE " Г % " ;5. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.
SELECT city, MAX (rating) FROM customers GROUP BY city;Заключение
На этом мы будем заканчивать. В этой статье мы познакомились с агрегатными функциями в SQL. Разобрали основные понятия и базовые примеры, которые могут пригодиться далее.
Если у вас остались вопросы, то задавайте их в комментариях.
Для подведения итогов по информации, содержащейся в БД, в 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. Эти два указания противоречат друг другу, что приводит к ошибке.
Сказанное не относится к случаям обработки подзапросов и запросов с группировкой.
Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:
Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:
Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):
Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).
Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT . Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,
Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY , синтаксически следующего после предложения WHERE .
Предложение GROUP BY
Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM) . Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции , и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции , то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
| SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model; |
В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:
| model | Qty_model | Avg_price |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).
Существует несколько определенных правил выполнения агрегатных функций :
- Если в результате выполнения запроса не получено ни одной строки (или не одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций - NULL.
- Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). Т.е. в одном запросе нельзя, скажем, получить максимум средних значений.
- Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
- Если при выполнении функции SUM был получен результат, превышающий максимальное значение используемого типа данных, возникает ошибка .
Итак, если запрос не содержит предложения GROUP BY , то агрегатные функции , включенные в предложение SELECT , исполняются над всеми результирующими строками запроса. Если запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляет группу, и агрегатные функции выполняются для каждой группы отдельно.
Предложение HAVING
Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций . Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM , а из групп таких строк . Поэтому такая проверка не может содержаться в предложении WHERE .
