Шаг 34 - SELECT - Агрегаты и группировка данных

Агрегатные функции, которые мы рассматривали ранее, по своей сути формируют конечный итог для запрашиваемой таблицы. То есть, получаем только одну итоговую строку. Например: Какова средняя стоимость заказа?

SELECT AVG(AMOUNT)
FROM ORDERS
/

Поучаем итоговый результат:

SQL> SELECT AVG(AMOUNT)
  2  FROM ORDERS
  3  /

AVG(AMOUNT)
-----------
170,0368333

Такое положение дел не всегда устраивает при выполнении такого рода запросов. Например, необходимо найти промежуточный результат. В этом случае нам поможет запрос с "группировкой". А именно выражение GROUP BY оператора SELECT. Для начала дадим вот такой запрос с применением выражения GROUP BY: Какова средняя стоимость заказа для каждого служащего?

SELECT REP, AVG(AMOUNT)
FROM ORDERS
GROUP BY REP
/

Поучаем сгруппированный запрос:

SQL> SELECT REP, AVG(AMOUNT)
  2  FROM ORDERS
  3  GROUP BY REP
  4  /

   REP AVG(AMOUNT)
------ -----------
   101      58,826
   102       5,694
   103      301,22
   105     148,125
   106      16,479
   107 228,5933333
   108 311,2064285
   109      3,5515
   110      327,25

9 rows selected

А вот, как это можно представить графически:

34_1.gif (14561 b)

Поле REP в данном случае является полем группировки, то есть все значения поля REP собираются в группы и для каждой отдельно взятой группы просчитывается выражение AVG(AMOUNT)! Просто и ясно. Если говорить сухим языком формулировок, то это будет примерно следующее:

  1. Заказы делятся на группы по одной группе для каждого служащего. В каждой группе все заказы имеют одно и тоже значение поля REP.
  2. Для каждой группы вычисляется среднее значение столбца AMOUNT по всем строкам входящим в группу и генерируется одна итоговая строка результатов. Эта строка содержит значение столбца REP для группы и среднюю стоимость заказа для данной группы.

Надеюсь, теперь стало немного понятнее. Следовательно, запрос с применением выражения GROUP BY, называется "ЗАПРОС С ГРУППИРОВКОЙ"! А имя столбца, следующего за этим выражением, называется "столбцом группировки". Давайте для закрепления рассмотрим еще несколько запросов с группировкой.

Каков диапазон плановых объемов продаж для каждого офиса?

SELECT REP_OFFICE, MIN(QUOTA), MAX(QUOTA)
FROM SALESREPS
GROUP BY REP_OFFICE
/

Получаем искомый результат:

SQL> SELECT REP_OFFICE, MIN(QUOTA), MAX(QUOTA)
  2  FROM SALESREPS
  3  GROUP BY REP_OFFICE
  4  /

  REP_OFFICE MIN(QUOTA) MAX(QUOTA)
------------ ---------- ----------
          11        275        300
          12        200        400
          13        350        350
          21        350        350
          22        300        300

6 rows selected

Еще один запрос: Сколько служащих работают в каждом офисе?

SELECT REP_OFFICE, COUNT(*)
FROM SALESREPS
GROUP BY REP_OFFICE
/

Получаем результат:

SQL> SELECT REP_OFFICE, COUNT(*)
  2  FROM SALESREPS
  3  GROUP BY REP_OFFICE
  4  /

  REP_OFFICE   COUNT(*)
------------ ----------
          11          2
          12          4
          13          1
          21          2
          22          1
                1

6 rows selected

И снова обратите внимание на последнюю строку, там уютно устроился наш старый добрый NULL, его я прописал намеренно, так как SQLPlus вернет пустоту, а вот COUNT(*) его видит!!! :)

Вот еще интересный запрос с группировкой: Сколько клиентов обслуживает каждый служащий?

SELECT COUNT(DISTINCT CUST_NUM), 'CUSTOMERS FOR SALESREPS', CUST_REP
FROM CUSTOMERS
GROUP BY CUST_REP
/

Получаем:

SQL> SELECT COUNT(DISTINCT CUST_NUM), 'CUSTOMERS FOR SALESREPS', CUST_REP
  2  FROM CUSTOMERS
  3  GROUP BY CUST_REP
  4  /

COUNT(DISTINCTCUST_NUM) 'CUSTOMERSFORSALESREPS'   CUST_REP
----------------------- ----------------------- ----------
                      3 CUSTOMERS FOR SALESREPS        101
                      4 CUSTOMERS FOR SALESREPS        102
                      3 CUSTOMERS FOR SALESREPS        103
                      1 CUSTOMERS FOR SALESREPS        104
                      2 CUSTOMERS FOR SALESREPS        105
                      2 CUSTOMERS FOR SALESREPS        106
                      1 CUSTOMERS FOR SALESREPS        107
                      2 CUSTOMERS FOR SALESREPS        108
                      2 CUSTOMERS FOR SALESREPS        109
                      1 CUSTOMERS FOR SALESREPS        110
                      1 CUSTOMERS FOR SALESREPS        120

11 rows selected

Здесь обратите внимание, на использование псевдополя 'CUSTOMERSFORSALESREPS'. Просто, чтобы было нагляднее. Так же группировать результаты запроса можно и по нескольким столбцам. Например, вот так:

Подсчитать общее количество заказов по каждому клиенту для каждого служащего.

SELECT REP, CUST, SUM(AMOUNT)
FROM ORDERS
GROUP BY REP, CUST
/

Получаем следующее:

SQL> SELECT REP, CUST, SUM(AMOUNT)
  2  FROM ORDERS
  3  GROUP BY REP, CUST
  4  /

  REP   CUST SUM(AMOUNT)
----- ------ -----------
  101   2102       3,978
  101   2108         150
  101   2113        22,5
  102   2106       4,026
  102   2114          15
  102   2120        3,75
  103   2111      602,44
  105   2103      736,88
  105   2111       3,745
  106   2101       1,458
  106   2117        31,5
  107   2109       31,35
  107   2124      654,43
  108   2112      47,925
  108   2114         7,1
  108   2118     2123,42
  109   2108       7,103
  110   2107       654,5

18 rows selected

Тем не менее, при группировке по двум столбцам нельзя создать группы и подгруппы с двумя уровнями итоговых результатов. Но, тем не менее, возможно применить сортировку, чтобы результаты запроса шли в нужном порядке. Хотя обычно результаты запроса при использовании GROUP BY, сортируются автоматически. Рассмотрим такой запрос:

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

SELECT REP, CUST, SUM(AMOUNT)
FROM ORDERS
GROUP BY REP, CUST
ORDER BY REP, CUST
/

Получаем то, что уже видели, но немного в другом порядке:

  REP   CUST SUM(AMOUNT)
----- ------ -----------
  101   2102       3,978
  101   2108         150
  101   2113        22,5
  102   2106       4,026
  102   2114          15
  102   2120        3,75
  103   2111      602,44
  105   2103      736,88
  105   2111       3,745
  106   2101       1,458
  106   2117        31,5
  107   2109       31,35
  107   2124      654,43
  108   2112      47,925
  108   2114         7,1
  108   2118     2123,42
  109   2108       7,103
  110   2107       654,5

18 rows selected

Вот таким образом выражение GROUP BY заставляет SELECT обрабатывать группы. Пробуйте сами и закрепляйте свои познания! :)


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.