Глава 6
ОБОБЩЕНИЕ ДАННЫХ С ПОМОЩЬЮ
АГРЕГАТНЫХ ФУНКЦИЙ

В ЭТОЙ ГЛАВЕ, ВЫ ПЕРЕЙДЕТЕ ОТ ПРОСТОГО

использования запросов к извлечению значений из базы данных и опре-

делению, как вы можете использовать эти значения чтобы получить

из них информацию. Это делается с помощью агрегатных или общих

функций которые берут группы значений из поля и сводят их до

одиночного значения. Вы узнаете как использовать эти функции, как

определить группы значений к которым они будут применяться, и как

определить какие группы выбираются для вывода. Вы будете также

видеть при каких условиях вы сможете объединить значения поля с

этой полученной информацией в одиночном запросе.

==== ЧТО ТАКОЕ АГРЕГАТНЫЕ ФУНКЦИИ ? =====

Запросы могут производить обобщенное групповое значение полей

точно также как и значение одного поля. Это делает с помощью

агрегатых функций. Агрегатные функции производят одиночное

значение для всей группы таблицы. Имеется список этих функций:

* COUNT

производит номера строк или не-NULL значения полей которые выбрал

запрос.

* SUM

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

поля.

* AVG

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

* MAX

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

* MIN

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

поля.

КАК ИСПОЛЬЗОВАТЬ АГРЕГАТНЫЕ ФУНКЦИИ ?

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

аргументы. Только числовые поля могут использоваться с SUM и AVG.

С COUNT, MAX, и MIN, могут использоваться и числовые или

символьные поля. Когда они используются с символьными полями,

MAX и MIN будут транслировать их в эквивалент ASCII, который

должен сообщать, что MIN будет означать первое, а MAX последнее

значение в алфавитном порядке( выдача алфавитного упорядочения

обсуждается более подробно в Главе 4 ).

Чтобы найти SUM всех наших покупок в таблицы Порядков, мы можем

ввести следующий запрос, с его выводом в Рисунке 6.1:

SELECT SUM ((amt))

FROM Orders;

=============== SQL Execution Log ============

| |

| SELECT SUM (amt) |

| FROM Orders; |

| ==============================================|

| |

| ------- |

| 26658.4 |

| |

| |

===============================================

Рисунок 6.1: Выбор суммы

Это конечно, отличается от выбора поля при котором возвращается

одиночное значение, независимо от того сколько строк находится в

таблице. Из-за этого, агрегатные функции и поля не могут выбираться

одновременно, пока предложение GROUP BY (описанное далее)

не будет использовано.

Нахождение усредненой суммы - это похожая операция ( вывод

следующего запроса показывается в Рисунке 6.2 ):

SELECT AVG (amt)

FROM Orders;

=============== SQL Execution Log ============

| |

| SELECT AVG (amt) |

| FROM Orders; |

| ==============================================|

| |

| ------- |

| 2665.84 |

| |

| |

===============================================

Рисунок 6.2: Выбор среднего

СПЕЦИАЛЬНЫЕ АТРИБУТЫ COUNT

Функция COUNT несколько отличается от всех. Она считает число

значений в данном столбце, или число строк в таблице. Когда она

считает значения столбца, она используется с DISTINCT чтобы

производить счет чисел различных значений в данном поле. Мы могли

бы использовать ее, например, чтобы сосчитать номера продавцов в

настоящее время описаных в таблице Порядков ( вывод показывается

в Рисунке 6.3 ):

SELECT COUNT ( DISTINCT snum )

FROM Orders;

ИСПОЛЬЗОВАНИЕ DISTINCT

Обратите внимание в вышеупомянутом примере, что DISTINCT,

сопровождаемый именем поля с которым он применяется, помещен

в круглые скобки, но не сразу после SELECT, как раньше.

Этого использования DISTINCT с COUNT применяемого к

индивидуальным столбцам, требует стандарт ANSI, но большое

количество программ не предъявляют к ним такого требования.

=============== SQL Execution Log ============

| |

| SELECT COUNT (DISTINCT snum) |

| FROM Orders; |

| ==============================================|

| |

| ------- |

| 5 |

| |

| |

===============================================

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

Вы можете выбирать многочисленые счета( COUNT ) из полей с помощью

DISTINCT в одиночном запросе который, как мы видели в Главе 3, не

выполнялся когда вы выбирали строки с помощью DISTINCT.

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

но наиболее часто он используется с COUNT. С MAX и MIN, это просто

не будет иметь никакого эффекта, а SUM и AVG, вы обычно применяете

для включения повторяемых значений, так как они законно эффективнее

общих и средних значений всех столбцов.

ИСПОЛЬЗОВАНИЕ COUNT СО

СТРОКАМИ, А НЕ ЗНАЧЕНИЯМИ

Чтобы подсчитать общее число строк в таблице, используйте функцию

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

примере, вывод из которого показан на Рисунке 6.4:

SELECT COUNT (*)

FROM Customers

COUNT со звездочкой включает и NULL и дубликаты, по этой причине

DISTINCT не может быть использован. DISTINCT может производить

более высокие номера чем COUNT особого поля, который удаляет все

=============== SQL Execution Log ============

| |

| SELECT COUNT (*) |

| FROM Customers; |

| ==============================================|

| |

| ------- |

| 7 |

| |

| |

===============================================

Рисунок 6. 4: Подсчет строк вместо значений

строки, имеющие избыточные или NULL данные в этом поле.

DISTINCT не применим c COUNT (*), потому, что он не имеет никакого

действия в хорошо разработаной и поддерживаемой базе данных.

В такой базе данных, не должно быть ни таких строк, которые бы являлись

полностью пустыми, ни дубликатов ( первые не содержат никаких

данных, а последние полностью избыточны ). Если, с другой стороны,

все таки имеются полностью пустые или избыточные строки, вы

вероятно не захотите чтобы COUNT скрыл от вас эту информацию.

ВКЛЮЧЕНИЕ ДУБЛИКАТОВ В АГРЕГАТНЫЕ ФУНКЦИИ

Агрегатные функции могут также ( в большинстве реализаций )

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

подобно DISTINCT, но означает противоположное: - включать дубликаты.

ANSI технически не позволяет этого для COUNT, но многие реализации

ослабляют это ограничение.

Различия между ALL и * когда они используются с COUNT -

* ALL использует имя_поля как аргумент.

* ALL не может подсчитать значения NULL.

Пока * является единственым аргументом который включает NULL

значения, и он используется только с COUNT; функции отличные от

COUNT игнорируют значения NULL в любом случае. Следующая

команда подсчитает(COUNT) число не-NULL значений в поле rating

в таблице Заказчиков ( включая повторения ):

SELECT COUNT ( ALL rating )

FROM Customers;

АГРЕГАТЫ ПОСТРОЕННЫЕ НА

СКАЛЯРНОМ ВЫРАЖЕНИИ

До этого, вы использовали агрегатные функции с одиночными полями как

аргументами. Вы можете также использовать агрегатные функции с

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

или более полей. ( Если вы это делаете, DISTINCT не разрешается. )

Предположим, что таблица Порядков имеет еще один столбец который

хранит предыдущий неуплаченый баланс (поле blnc) для каждого заказчика.

Вы должны найти этот текущий баланс, добавлением суммы приобретений

к предыдущему балансу.

Вы можете найти наибольший неуплаченый баланс следующим образом:

SELECT MAX ( blnc + (amt) )

FROM Orders;

Для каждой строки таблицы, этот запрос будет складывать blnc и amt

для этого заказчика и выбирать самое большое значение которое он

найдет. Конечно, пока заказчики могут иметь многочисленые порядки,

их неуплаченый баланс оценивается отдельно для каждого порядка.

Возможно, порядок с более поздней датой будет иметь самый большой

неуплаченый баланс. Иначе, старый баланс должен быть выбран как в

запросе выше.

Фактически, имеются большое количество ситуаций в SQL где вы

можете использовать скалярные выражения с полями или вместо полей,

как вы увидете это в Главе 7.

ПРЕДЛОЖЕНИЕ GROUP BY

Предложение GROUP BY позволяет вам определять подмножество

значений в особом поле в терминах другого поля, и применять функцию

агрегата к подмножеству. Это дает вам возможность объединять поля и

агрегатные функции в едином предложении SELECT.

Например, предположим что вы хотите найти наибольшую сумму приобре-

тений полученную каждым продавцом. Вы можете сделать раздельный

запрос для каждого из них, выбрав MAX (amt) из таблицы Порядков для

каждого значения поля snum.

GROUP BY, однако, позволит Вам поместить их все в одну команду:

SELECT snum, MAX (amt)

FROM Orders

GROUP BY snum;

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

=============== SQL Execution Log ==============

| |

| SELECT snum, MAX (amt) |

| FROM Orders |

| GROUP BY snum; |

| =============================================== |

| snum |

| ------ -------- |

| 1001 767.19 |

| 1002 1713.23 |

| 1003 75.75 |

| 1014 1309.95 |

| 1007 1098.16 |

| |

================================================

Рисунок 6.5: Нахождение максимальной суммы продажи у каждого продавца

GROUP BY применяет агрегатные функции независимо от серий групп

которые определяются с помощью значения поля в целом. В этом случае,

каждая группа состоит из всех строк с тем же самым значением поля snum,

и MAX функция применяется отдельно для каждой такой группы. Это значение

поля, к которому применяется GROUP BY, имеет, по определению, только одно

значение на группу вывода, также как это делает агрегатная функция.

Результатом является совместимость которая позволяет агрегатам и полям

объединяться таким образом.

Вы можете также использовать GROUP BY с многочислеными полями.

Совершенствуя вышеупомянутый пример далее, предположим что вы

хотите увидеть наибольшую сумму приобретений получаемую каждым

продавцом каждый день. Чтобы сделать это, вы должны сгруппировать

таблицу Порядков по датам продавцов, и применить функцию MAX к

каждой такой группе, подобно этому:

SELECT snum, odate, MAX ((amt))

FROM Orders

GROUP BY snum, odate;

Вывод для этого запроса показывается в Рисунке 6.6.

=============== SQL Execution Log ==============

| |

| SELECT snum, odate, MAX (amt) |

| FROM Orders |

| GROUP BY snum, odate; |

| =============================================== |

| snum odate |

| ------ ---------- -------- |

| 1001 10/03/1990 767.19 |

| 1001 10/05/1990 4723.00 |

| 1001 10/06/1990 9891.88 |

| 1002 10/03/1990 5160.45 |

| 1002 10/04/1990 75.75 |

| 1002 10/06/1990 1309.95 |

| 1003 10/04/1990 1713.23 |

| 1014 10/03/1990 1900.10 |

| 1007 10/03/1990 1098.16 |

| |

================================================

Рисунок 6.6: Нахождение наибольшей суммы приобретений на каждый день

Конечно же, пустые группы, в дни когда текущий продавец не имел порядков,

не будут показаны в выводе.

ПРЕДЛОЖЕНИЕ HAVING

Предположим, что в предыдущем примере, вы хотели бы увидеть только

максимальную сумму приобретений значение которой выше $3000.00. Вы

не сможете использовать агрегатную функцию в предложении WHERE

( если вы не используете подзапрос, описанный позже ), потому что

предикаты оцениваются в терминах одиночной строки, а агрегатные

функции оцениваются в терминах групп строк. Это означает что вы не

сможете сделать что-нибудь подобно следующему:

SELECT snum, odate, MAX (amt)

FROM Oreders

WHERE MAX ((amt)) > 3000.00

GROUP BY snum, odate;

Это будет отклонением от строгой интерпретации ANSI. Чтобы увидеть мак-

симальную стоимость приобретений свыше $3000.00, вы можете использо-

вать предложение HAVING.

Предложение HAVING определяет критерии используемые чтобы удалять

определенные группы из вывода, точно также как предложение WHERE

делает это для индивидуальных строк.

Правильной командой будет следующяя:

SELECT snum, odate, MAX ((amt))

FROM Orders

GROUP BY snum, odate

HAVING MAX ((amt)) > 3000.00;

Вывод для этого запроса показывается в Рисунке 6. 7.

=============== SQL Execution Log ==============

| |

| SELECT snum, odate, MAX (amt) |

| FROM Orders |

| GROUP BY snum, odate |

| HAVING MAX (amt) > 3000.00; |

| =============================================== |

| snum odate |

| ------ ---------- -------- |

| 1001 10/05/1990 4723.00 |

| 1001 10/06/1990 9891.88 |

| 1002 10/03/1990 5160.45 |

| |

================================================

Рисунок 6. 7: Удаление групп агрегатных значений

Аргументы в предложении HAVING следуют тем же самым правилам

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

GROUP BY. Они должны иметь одно значение на группу вывода.

Следующая команда будет запрещена:

SELECT snum, MAX (amt)

FROM Orders

GROUP BY snum

HAVING odate = 10/03/1988;

Поле оdate не может быть вызвано предложением HAVING, потому что

оно может иметь ( и действительно имеет ) больше чем одно значение

на группу вывода. Чтобы избегать такой ситуации, предложение HAVING

должно ссылаться только на агрегаты и поля выбранные GROUP BY.

Имеется правильный способ сделать вышеупомянутый запрос( вывод

показывается в Рисунке 6.8 ):

SELECT snum, MAX (amt)

FROM Orders

WHEREodate = 10/03/1990

GROUP BY snum;

=============== SQL Execution Log ==============

| |

| SELECT snum, odate, MAX (amt) |

| FROM Orders |

| GROUP BY snum, odate; |

| =============================================== |

| snum |

| ------ -------- |

| 1001 767.19 |

| 1002 5160.45 |

| 1014 1900.10 |

| 1007 1098.16 |

| |

================================================

Рисунок 6.8: Максимальное значение суммы приобретений у каждого

продавца на 3 Октября

Поскольку поля odate нет, не может быть и выбраных полей, значение

этих данных меньше чем в некоторых других примерах.

Вывод должен вероятно включать что-нибудь такое что говорит -

" это - самые большие порядки на 3 Октября." В Главе 7, мы

покажем как вставлять текст в ваш вывод.

Как и говорилось ранее, HAVING может использовать только аргументы

которые имеют одно значение на группу вывода. Практически, ссылки

на агрегатные функции - наиболее общие, но и поля выбранные с помощью

GROUP BY также допустимы. Например, мы хотим увидеть наибольшие

порядки для Serres и Rifkin:

SELECT snum, MAX (amt)

FROM Orders

GROUP BY snum

HAVING snum B (1002,1007);

Вывод для этого запроса показывается в Рисунке 6.9.

=============== SQL Execution Log ==============

| |

| SELECT snum, MAX (amt) |

| FROM Orders |

| GROUP BY snum |

| HAVING snum IN ( 1002, 1007 ); |

| =============================================== |

| snum |

| ------ -------- |

| 1002 5160.45 |

| 1007 1098.16 |

| |

================================================

Рисунок 6. 9: Использование HAVING с GROUP BY полями

НЕ ДЕЛАЙТЕ ВЛОЖЕННЫХ АГРЕГАТОВ

В строгой интерпретации ANSI SQL, вы не можете использовать агрегат

агрегата. Предположим что вы хотите выяснять, в какой день имелась

наибольшая сумма приобретений. Если вы попробуете сделать это,

SELECT odate, MAX ( SUM (amt) )

FROM Orders

GROUP BY odate;

то ваша команда будет вероятно отклонена. ( Некоторые реализации не

предписывают этого ограничения, которое является выгодным, потому

что вложенные агрегаты могут быть очень полезны, даже если они и

несколько проблематичны.) В вышеупомянутой команде, например,

SUM должен применяться к каждой группе поля odate, а MAX ко

всем группам, производящим одиночное значение для всех групп.

Однако предложение GROUP BY подразумевает что должна иметься

одна строка вывода для каждой группы поля odate.

=============== РЕЗЮМЕ ================

Теперь вы используете запросы несколько по-другому. Способность

получать, а не просто размещать значения, очень мощна.

Это означает что вы не обязательно должны следить за определенной

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

получить. Запрос будет давать вам по-минутные результаты, в то время

как таблица общего или среднего значений будет хороша только некоторое

время после ее модификации. Это не должно наводить на мысль, что

агрегатные функции могут полностью вытеснить потребность в

отслеживании информации такой например как эта.

Вы можете применять эти агрегаты для групп значений определенных

предложением GROUP BY. Эти группы имеют значение поля в целом, и

могут постоянно находиться внутри других групп которые имеют

значение поля в целом. В то же время, предикаты еще используются

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

Объединенные вместе, эти особенности делают возможным, производить

агрегаты основанные на сильно определенных подмножествах значений в

поле. Затем вы можете определять другое условие для исключения

определенных результатов групп с предложением HAVING.

Теперь , когда вы стали знатоком большого количества того как

запрос производит значения, мы покажем вам, в Главе 7, некоторые

вещи которые вы можете делать со значениями которые он производит.

************** РАБОТА С SQL **************

1. Напишите запрос который сосчитал бы все суммы приобретений на

3 Октября.

2. Напишите запрос который сосчитал бы число различных не-NULL

значений поля city в таблице Заказчиков.

3. Напишите запрос который выбрал бы нименьшую сумму для

каждого заказчика.

4. Напишите запрос который бы выбирал заказчиков в алфавитном

порядке, чьи имена начинаются с буквы G.

5. Напишите запрос который выбрал бы высшую оценку в каждом городе.

6. Напишите запрос который сосчитал бы число заказчиков регистрирующих

каждый день свои порядки. (Если продавец имел более одного

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

( См. Приложение A для ответов. )

---

---