Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6784 / 673 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 7:

Выборка данных. Фразы ORDER BY, GROUP BY и CONNECT BY предложения SELECT. Множественные операции

< Лекция 6 || Лекция 7: 123456 || Лекция 8 >

Фразы GROUP BY и HAVING предложения SELECT

Назначение фразы GROUP BY — сгруппировать строки по указанному общему признаку и выдать сведения, общие для каждой группы. Подчиненная ей фраза HAVING употребляется для отсева по мере необходимости некоторых групп, подобно тому как фраза WHERE отсеивает строки, полученные из источников данных запроса, по сформулированному условному выражению.

Пример отработки фразы GROUP BY … HAVING

Рассмотрим запрос:

SELECT   deptno
FROM     emp
WHERE    sal > 1000
GROUP BY deptno
HAVING   COUNT ( * ) <= 4
ORDER BY deptno DESC
;

"Выбрать в убывающем порядке номера отделов, в которых число сотрудников с окладом > 1000 менее пяти"

Запрос намеренно упрощен тем, что не учитывает наличия данных о связи сотрудников и отделов вне таблицы EMP. В действительности, в схеме SCOTT это не так: высказывание "имеются отделы, где нет сотрудников" требует для определения истинности обращения сразу к к двум таблицам: EMP и DEPT. Точнее приведенной формулировке на SQL соответствует следующая на естественном языке:

"Выбрать в убывающем порядке номера отделов, в которых есть сотрудники, но оклад > 1000 имеют менее пяти из них"

Ниже отработка фраз GROUP BY и HAVING поясняется серией шагов в соответствии с общей логикой выполнения, упоминавшейся ранее.

Промежуточный результат после FROM ... WHERE ...

Из-за наличия в запросе всего одного источника данных фраза FROM всего только выбирает строки из таблицы EMP, настоящую работу выполняет фраза WHERE. Вот ее результат:

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
     7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300        30
     7521 WARD       SALESMAN       7698 22-FEB-81      1250       500        30
     7566 JONES      MANAGER        7839 02-APR-81      2975                  20
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     7698 BLAKE      MANAGER        7839 01-MAY-81      2850                  30
     7782 CLARK      MANAGER        7839 09-JUN-81      2450                  10
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  20
     7839 KING       PRESIDENT           17-NOV-81      5000                  10
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  20
     7902 FORD       ANALYST        7566 03-DEC-81      3000                  20
     7934 MILLER     CLERK          7782 23-JAN-82      1300                  10
Промежуточный результат после GROUP BY

Группировка по признаку "значение в DEPTNO" даст следующее объединение строк:

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
[    7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300 
     7521 WARD       SALESMAN       7698 22-FEB-81      1250       500    
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        
     7698 BLAKE      MANAGER        7839 01-MAY-81      2850                  
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0]       30
[    7782 CLARK      MANAGER        7839 09-JUN-81      2450
     7839 KING       PRESIDENT           17-NOV-81      5000                  
     7934 MILLER     CLERK          7782 23-JAN-82      1300          ]       10
[    7566 JONES      MANAGER        7839 02-APR-81      2975
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  
     7902 FORD       ANALYST        7566 03-DEC-81      3000          ]       20

После группировки в окончательный ответ смогут поступить только значения признака группирования (данные из столбца DEPTNO, конкретно — значения 30, 10 и 20 для каждой группы) и агрегаты для групп, то есть то, что для каждой группы строк является общим. Это касается следующей фразы HAVING и всех далее идущих.

Промежуточный результат после HAVING

Присутствующая здесь фраза HAVING отсеет группы, количество сотрудников в которых больше четырех:

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
[    7782 CLARK      MANAGER        7839 09-JUN-81      2450
     7839 KING       PRESIDENT           17-NOV-81      5000                  
     7934 MILLER     CLERK          7782 23-JAN-82      1300          ]       10
[    7566 JONES      MANAGER        7839 02-APR-81      2975
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  
     7902 FORD       ANALYST        7566 03-DEC-81      3000          ]       20

Агрегаты (функции обобщения, в нашем случае это COUNT ( * )) в запросах с GROUP BY автоматически приобретают необычный смысл: они распространяются только на группы, а не на все множество строк.

Промежуточный результат ORDER BY

Множество групп впервые упорядочивается — по убыванию значения DEPTNO:

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
[    7876 ADAMS      CLERK          7788 23-MAY-87      1100
     7566 JONES      MANAGER        7839 02-APR-81      2975                  
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  
     7902 FORD       ANALYST        7566 03-DEC-81      3000         ]        20
[    7934 MILLER     CLERK          7782 23-JAN-82      1300
     7782 CLARK      MANAGER        7839 09-JUN-81      2450                  
     7839 KING       PRESIDENT           17-NOV-81      5000         ]        10
Конечный отбор SELECT

В окончательный ответ попадают только данные о номерах отделов:

   DEPTNO
---------
       20
       10

В другом случае в ответ могли бы поступить еще и агрегаты.

Отсутствие значения в выражении для группировки

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

SQL> SELECT comm, COUNT ( * ) FROM emp GROUP BY comm;
      COMM   COUNT(*)
---------- ----------
         0          1
       300          1
       500          1
      1400          1
                   10

Причина та же: иначе запросы с группировкой были бы малопрактичны. Можно напомнить, что это исключение для правила сравнения с NULL касается внутренней технической операции сравнения, но не явно сформулированной в запросе.

Другие примеры

В следующих примерах для группирования строк указывается пара значений, а не одно.

Сравните выдачу

SELECT   mgr, job, SUM ( sal ), COUNT ( * ) 
FROM     emp 
GROUP BY mgr, job
;

и

SELECT   mgr, job, SUM ( sal ), COUNT ( * ) 
FROM     emp 
GROUP BY job, mgr
;

Очевидно, порядок перечисления выражений для признаков группировки в GROUP BY не сказывается на конечном результате.

Еще пример:

SELECT   mgr, job, COUNT ( * ) 
FROM     emp 
GROUP BY mgr, job
HAVING   AVG ( sal ) > 1000
;

Дополнительные примеры приводятся в заключительной части настоящего материала.

Обратите внимание, что формально запрос с группировкой может и не содержать обращений к функциям обобщения (агрегатам), однако в этом случае проще отказаться от GROUP BY и использовать DISTINCT.

< Лекция 6 || Лекция 7: 123456 || Лекция 8 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Ладисьяно Писеев
Ладисьяно Писеев
Россия