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

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

COUNT

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

SUM

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

AVG

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

MAX

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

MIN

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

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

СПЕЦИАЛЬНЫЕ АТРИБУТЫ 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: Подсчет значений пол 

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

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

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

             SELECT MAX ( blnc + (amt) ) 
                FROM Orders; 
 

Для каждой строки таблицы, этот запрос будет складывать blnc и amt для этого заказчика и выбирать самое большое значение которое он найдет. Конечно, пока заказчики могут иметь многочисленные порядки, их неуплаченный баланс оценивается отдельно для каждого порядка. Возможно, порядок с более поздней датой будет иметь самый большой неуплаченный баланс. Иначе, старый баланс должен быть выбран как в запросе выше.

Предложение 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: Нахождение наибольшей суммы приобретений на каждый день 
 

Предположим, что в предыдущем примере, вы хотели бы увидеть только максимальную сумму приобретений значение которой выше $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 Октября 
 

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

              SELECT odate, MAX ( SUM (amt) ) 
                 FROM Orders 
                 GROUP BY odate; 
 

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

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

Объединенные вместе, эти особенности делают возможным, производить агрегаты основанные на сильно определенных подмножествах значений в поле. Затем вы можете определять другое условие для исключения определенных результатов групп с предложением HAVING.

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

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

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

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

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

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