|
В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS" представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции. |
Опубликован: 28.12.2011 | Уровень: для всех | Доступ: свободно
Лекция 7:
Выборка данных. Фразы ORDER BY, GROUP BY и CONNECT BY предложения SELECT. Множественные операции
Фразы 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.
Ярослав Прозоров
Володимир Миколайчук
|
Помогите разобраться поетапно с логикой запроса
-------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')); И сколько строк он все таки вернет |
Жанбек Сарсенов
| Россия, Москва, Московский Государственный Университет имени Ломоносова М.В., 2002 |