Агрегатные функции, которые мы рассматривали ранее, по своей сути формируют конечный итог для запрашиваемой таблицы. То есть, получаем только одну итоговую строку. Например: Какова средняя стоимость заказа?
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
А вот, как это можно представить графически:
Поле REP в данном случае является полем группировки, то есть все значения поля REP собираются в группы и для каждой отдельно взятой группы просчитывается выражение AVG(AMOUNT)! Просто и ясно. Если говорить сухим языком формулировок, то это будет примерно следующее:
- Заказы делятся на группы по одной группе для каждого служащего. В каждой группе все заказы имеют одно и тоже значение поля REP.
- Для каждой группы вычисляется среднее значение столбца 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 обрабатывать группы. Пробуйте сами и закрепляйте свои познания! :)