Глава. 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 Orders
                 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
                WHERE odate = 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.)