Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Глава 6. Суммирование да11ных с помощью функций агрегирования

В этой главе осуществляется переход к более сложным запросам на извлечение

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

единственном запросе.

Что такое функции агрегирования?

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

• COUNT определяет количество строк или значений поля, выбранных посредст­ вом запроса и не являющихся NULL-значениями;

• SUM вычисляет арифметическую сумму всех выбранных значений данного

поля;

•АVG вычисляет среднее значение для всех выбранных значений данного поля;

•МАХ вычисляет наибольшее из всех выбранных значений данного поля;

•MIN вычисляет наименьшее из всех выбранных значений данного поля.

Как используются функции агрегирования?

Функции агрегирования используются как имена полей в предложении запроса SELECT с одним исключением: имена полей применяются как аргументы. Для SUM и AVG могут использоваться только цифровые поля. Для COUNT, МАХ и MIN - циф­ ровые и символьные поля. При употреблении с символьными полями МАХ и MIN применяются к АSСII-эквивалентам: MIN предполагает минимальное (первое), а МАХ - максимальное (последнее) значения в соответствии с алфавитным порядком (более детально алфавитное упорядочение рассмотрено в главе 4).

Чтобы найти сумму (SUM) всех заявок из таблицы Orders, можно ввести следую­ щий запрос, выходные данные для которого представлены на рис. 6.1 :

SELECT SUM(amt)

FROM Orders;

52

Что такое функции агрегирования?

Рис. 6.1. Выбор суммы

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

Похожей операцией является поиск среднего значения (выходные данные для сле­ дующего запроса представлены на рис. 6.2):

SELECT AVG(amt)

FROM Orders;

Рис. 6.2. Выбор среднего значения

53

Глава 6. Суммирование данных с помощью функций агрегирования

Специальные атрибуты в COUNT

Функция COUNT отличается от предыдущих тем, что подсчитывает количество значений в данном столбце или количество строк в таблице. Когда подсчитываются значения по столбцу, в команде используется DISTINCT для подсчета числа различ­ ных значений данного поля. Можно использовать его, например, для подсчета количе­ ства продавцов, имеющих в настоящее время заказы в таблице Orders (выходные данные представлены на рис. 6.3):

SELECT COUNT (DISTINCT snum)

FROM Orders;

Использование DISТINCT. В данном примере DISТINCT вместе со следующим за ним именем поля, к которому он применяется, заключен в круглые скобки и не следует непосредственно за SELECT, как это было в примере главы 3. Такая форма применения DISТINCT с COUNT к отдельным столбцам предписывается стандартом ANSI, но многие программы не придерживаются этого требования. Можно использо­ вать множество COUNT для DISTINCT полей в одном запросе; этот случай, рассмот­ ренный в главе 3, отличается от случая применения DISTINCT к строкам.

Указанным способом DISТINCT можно применять с любой функцией агрегирова­ ния, но чаще всего он используется с COUNT. Применение его с МАХ и MIN беспо­ лезно; а используя SUM и AVG, необходимо включение в выходные данные

повторяющихся значений, так как они влияют на сумму и среднее для значений всех

столбцов.

Использование COUNT со строками, а не со значениями. Для подсчета об­ щего количества строк в таблице следует использовать функцию COUNT со звездоч-

Рис. 6.3. Подсчет количества значений поля

54

Что такое функции агрегирования?

Рис. 6.4. Подсчет количества строк, а не значений поля

кой вместо имени поля так, как показано в следующем примере, выходные данные

для которого представлены на рис. 6.4:

SELECT COUNT (•)

FROM Customers;

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

получается число, превышающее COUNT для отдельного поля, который исключает

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

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

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

информации.

Использование дубликатов в агрегатных функциях. Агрегатные функции

могут также (во многих реализациях) иметь аргумент ALL, который размещается

перед именем поля, как и DISTINCT, но обозначает противоположное: включить дубликаты. Требования ANSI не допускают подобного для COUNT, но многие реа­ лизации игнорируют это ограничение. Различие между ALL и * при использовании COUNT заключается в следующем:

•ALL использует имя поля в качестве аргумента;

•ALL не подсчитывает NULL-значения.

55

Глава 6. Суммирование данных с помощью функций агрегирования

Поскольку * является единственным аргументом, который включает NULL- значения и используется только с COUNT, функции, отличные от COUNT, игнориру­ ют NULL-значения в любом случае. Следующая команда осуществляет подсчет коли­ чества значений поля rating, отличных от NULL-значений, в таблице Customers (включая повторения):

SELECT COUNT (ALL rating)

FROM Customers;

Агрегаты, построенные на скалярных выражениях

До сих пор были использованы агрегатные функции с одним полем в качестве ар­ гумента. Можно использовать агрегатные функции с аргументами, которые состоят из скалярных выражений, включающих одно поле или большее количество полей. (При этом не разрешается применять DISТINCT.) Предположим, таблица Orders содержит дополнительный столбец с величиной предыдущего баланса (Ыnс) для каждого поку­ пателя. Можно найти текущий баланс, добавив значение поля amount (amt) к значению поля Ь!nс. Можно найти наибольшее значение текущего баланса:

SELECT МАХ (binc + amt)

FROM Orders;

В процессе выполнения этого запроса для каждой строки таблицы выполняется сложение значений двух указанных полей записи и выбирается наибольшее из полу­ ченных значений. Конечно, поскольку покупатели могут иметь несколько заказов, их окончательный баланс в данном случае оценивается отдельно для каждого заказа. Предполагается, что последняя заявка имеет наибольшее значение баланса данного покупателя. В противном с.1учае в предыдущем примере мог быть выбран старый ба­ ланс. В SQL можно часто использовать скалярное выражение вместе с полями или

вместо них.

Предложение GROUP ВУ

Предложение GROUP ВУ позволяет определять подмножество значений отдельно­ го поля в терминах другого поля и применять функции агрегирования к полученному подмножеству. Это дает возможность комбинировать поля и агрегатные функции в од­ ном предложении SELECТ. Например, предположим, что нужно найти наибольший

заказ из тех, что получил каждый из продавцов. Можно сделать отдельный запрос на

каждого продавца, выбрав МАХ (amt) для таблицы Orders для каждого значения поля snum и используя GROUP ВУ, однако, возможно объединить все в одной команде:

SELECT snum, MAX(amt)

FROM Orders

GROUP ВУ snum;

Выходные данные для этого запроса представлены на рис. 6.5.

56