[ главная ]   [ рейтинг статей ]   [ справочник радиолюбителя ]   [ новости мира ИТ ]



Ответов: 0
25-02-12 07:01







   Web - программирование
PHP


ASP






XML



CSS

SSI





   Программирование под ОС











   Web - технологии








   Базы Данных









   Графика






Данные




Базы Данных / MySQL /

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

            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 в любом случае.  Следующая команда подсчитает(CO-
 UNT) число не-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 подразуме-
 вает что должна иметься одна строка вывода для каждой группы поля oda-
 te.
 
 
               ===============   РЕЗЮМЕ  ================
 
 
 
 
   Теперь вы используете запросы несколько по-другому.  Способность по-
 лучать,  а не просто размещать значения, очень мощна. Это означает что
 вы  не  обязательно должны следить за определенной информацией если вы
 можете сформулировать запрос так чтобы ее получить.  Запрос будет  да-
 вать  вам  по-минутные  результаты,  в то время как таблица общего или
 среднего значений будет хороша только некоторое время после ее модифи-
 кации.  Это не должно наводить на мысль,  что агрегатные функции могут
 полностью вытеснить потребность в отслеживании информации такой напри-
 мер как эта.
   Вы можете применять эти агрегаты  для  групп  значений  определенных
 предложением GROUP BY. Эти группы имеют значение поля в целом, и могут
 постоянно находиться внутри других групп которые имеют значение поля в
 целом.  В то же время, предикаты еще используются чтобы определять ка-
 кие строки агрегатной функции применяются.
   Объединенные вместе,  эти особенности делают возможным,  производить
 агрегаты основанные на сильно определенных  подмножествах  значений  в
 поле. Затем вы можете определять другое условие для исключения опреде-
 ленных результатов групп с предложением HAVING.
   Теперь , когда вы стали знатоком большого количества того как запрос
 производит значения, мы покажем вам, в Главе 7, некоторые вещи которые
 вы можете делать со значениями которые он производит.
 
 
               ************** РАБОТА С SQL **************
 
 
 1. Напишите запрос который сосчитал бы все суммы приобретений на 3 Ок-
    тября.
 2. Напишите  запрос который сосчитал бы число различных не-NULL значе-
    ний поля city в таблице Заказчиков.
 3. Напишите  запрос  который выбрал бы нименьшую сумму для каждого за-
    казчика.
 4. Напишите запрос который бы выбирал заказчиков в алфавитном порядке,
    чьи имена начинаются с буквы G.
 5. Напишите запрос который выбрал бы высшую оценку в каждом городе.
 6. Напишите запрос который сосчитал бы число заказчиков регистрирующих
    каждый день свои порядки.  (Если продавец имел более одного порядка
    в данный день, он должен учитываться только один раз.)
 
 
 ( См. Приложение A для ответов. )
 
 
 




Комментарии

 Ваш комментарий к данному материалу будет интересен нам и нашим читателям!



Последние статьи: Базы Данных / MySQL /

Близкие контакты третьего вида с Visual Foxpro (или как написать свой провайдер для FoxPro)
12-03-2010   

Многие наверное как и я в свое время задавались интересным вопросом – “А вот как бы задействовать всю силу применяемой в моем проекте СУБД? Не только стандартные SQL запросы, а и скрытые возможности.” Тогда ведь можно будет получать результат найэффективнешими методам... подробнее

Кол. просмотров: общее - 4506 сегодня - 0

Oracle: Изучаем метки доступа к строкам: задание свойств столбца доступа в таблице
07-03-2010   

Эта статья рассматривает некоторые особенности средства label security в oracle. Здесь показана возможность секретить служебный столбец с метками доступа к строкам, а также рассмотрены некоторые правила правки меток. В первую очередь статья затрагивает использование параметра table_options процедуры apply_table_policy из пакета sa_policy_admin... подробнее

Кол. просмотров: общее - 3280 сегодня - 4

Простой журнал аудита на триггерах MySQL
07-03-2010   

Небольшая заметка об использовании триггеров в СУБД MySQL. Несмотря на достаточно приличный возраст этой СУБД, поддержка триггеров появилась только в 5-й версии и достаточно мало описана на русском языке... подробнее

Кол. просмотров: общее - 3176 сегодня - 2

Настройки mysql-server сразу после установки
07-03-2010   

Мой любимый вопрос, задаваемый DBA, которые хотят увеличить производительность MySQL: “какие параметры надо настраивать в первую очередь, сразу после установки сервера?”... подробнее

Кол. просмотров: общее - 2976 сегодня - 0

10+ способов обрушить mysql-сервер
07-03-2010   

Иногда у меня спрашивают о ошибках MySQL, (например таких), которые могут привести к обрушиванию mysql-сервера пользователем с обычными привелегиями. Потом звучит вопрос: “Что же делать в таких случаях? Как защититься от подобных ситуаций?”... подробнее

Кол. просмотров: общее - 8335 сегодня - 1



  WWW.COMPROG.RU - 2009-2012 | Designed and Powered by Zaipov Renat | Projects