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

Указание ROLLUP, CUBE и GROUPING SETS во фразе GROUP BY

В версии 8.1 Oracle в запросах с GROUP BY в соответствии с предложениями SQL:1999 (но без строгого им следования) была введена возможность заключать признаки группировки в конструкции ROLLUP и CUBE, позволяющие сымитировать в СУБД на основе SQL функциональность OLAP. В версии 9 к ним добавилась конструкция GROUPING SETS.

Всякий запрос с ROLLUP, CUBE или GROUPING SETS может быть переформулирован в запрос с группировками, но без этих конструкций, и тем не менее их использование (а) дает более лаконичную и универсальную формулировку, и (б) специальным образом в Oracle оптимизировано.

Пример группировки с ROLLUP:

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

Результат складывается из группировки по паре значений (MGR, JOB) плюс группировки по значению MGR плюс данных по "собственной группе" из всего множества исходных строк:

    MGR JOB        SUM(SAL)  COUNT(*)
------- --------- --------- ---------
        PRESIDENT      5000         1  ← SUM и COUNT для mgr=NULL и job=PRESIDENT
                       5000         1  ← SUM и COUNT для mgr=NULL
   7566 ANALYST        6000         2  ← SUM и COUNT для mgr=7566 и job=ANALYST
   7566                6000         2  ← SUM и COUNT для mgr=7566
   7698 CLERK           950         1  ← SUM и COUNT для mgr=7698 и job=CLERK
   7698 SALESMAN       5600         4  ← SUM и COUNT для mgr=7698 и job=SALESMAN
   7698                6550         5  ← SUM и COUNT для mgr=7698
   7782 CLERK          1300         1  ← ...
   7782                1300         1
   7788 CLERK          1100         1
   7788                1100         1
   7839 MANAGER        8275         3
   7839                8275         3
   7902 CLERK           800         1
   7902                 800         1
                      29025        14  ← SUM и COUNT для всех начальников и должностей

Такое построение результата порождает в ответе искусственно отсутствующие значения из-за группировок по сокращенным перечням признаков. Обратите внимание, они имеют смысл "значение неприменимо" (unapplicable). Это противоречит упоминавшейся выше рекомендации стандарта SQL использовать NULL в смысле "значение неизвестно" (unknown). Такую специфику отсутствующих значений в результатах с GROUP BY ROLLUP следует учитывать в последующей обработке. Но ведь пропуски значений могли иметься и в исходных данных (например, у сотрудника могла быть не обозначена должность), где они могут иметь иной смысл и требовать иного характера обработки. Программа сама по себе не в состоянии определить смысл NULL.

Для различения отсутствующих значений в строках с обобщенными итогами от значений, отсутствующих в исходных данных, создана специальная индикаторная функция GROUPING, возвращающая 1 на "благоприобретенных" NULL-значениях и 0 на всех остальных. Следующий запрос получен из предыдущего добавлением выдачи функций GROUPING и поясняет, как это работает:

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

С помощью функции GROUPING и оператора CASE отсутствующие значения в строках промежуточных сумм могут быть заполнены желаемым образом:

COLUMN "Начальник" FORMAT A15
SELECT
  CASE GROUPING ( mgr ) WHEN 1 THEN 'все начальники' ELSE TO_CHAR ( mgr ) END 
  AS "Начальник"
, CASE GROUPING ( job ) WHEN 1 THEN 'все должности' ELSE job END 
  AS "Должность"
, SUM ( sal ) AS "Зарплата в группе"
, COUNT ( * ) AS "Сотрудников в группе"
FROM
  emp 
GROUP BY
  ROLLUP ( mgr, job )
ORDER BY mgr, job
;

Упражнение. Выполните приведенный запрос и посмотрите результат. Замените ROLLUP на CUBE, выполните и посмотрите результат.

В версии 9 вместо функции GROUPING стало возможным использовать функцию GROUPING_ID, возвращающую тот же результат, что и GROUPING, но в виде поразрядной маски. Так, один из предшествующих "пояснительных" запросов можно переписать короче:

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

Иногда использование GROUPING_ID позволяет проще, нежели GROUPING, сформулировать в запросе фильтр.

Еще одна функция, GROUP_ID, также введена в версии 9 и позволяет в запросе с группировкой отметить повторяющиеся строки с агрегатами (которые могут иногда возникать), указывая "степень повторения" > 0, например:

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

В версии 9 появилась возможность выдавать в запросе только промежуточные агрегаты без усложнений текста, необходимых в таких ситуациях при использовании ROLLUP и CUBE. Для этого введено специальное указание GROUPING SETS:

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

Сравните с результатом запроса:

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

Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS

Использование в группировках ROLLUP, CUBE и GROUPING SETS дает гибкость формулировки и эффективность исполнения, но не логическую новизну. Так, следующие запросы "по определению" равносильны по результату:

SELECT a, b
FROM Z
GROUP BY ROLLUP ( a, b )

SELECT    a,   b  FROM Z GROUP BY a, b UNION ALL
SELECT    a, NULL FROM Z GROUP BY a    UNION ALL
SELECT NULL, NULL FROM Z

SELECT a, b
FROM Z
GROUP BY CUBE ( a, b )

SELECT    a,    b FROM Z GROUP BY a, b UNION ALL
SELECT    a, NULL FROM Z GROUP BY a    UNION ALL
SELECT NULL,    b FROM Z GROUP BY b    UNION ALL
SELECT NULL, NULL FROM Z

SELECT a, b
FROM Z
GROUP BY 
   GROUPING SETS ( a, b )

SELECT    a, NULL FROM Z GROUP BY a    UNION ALL
SELECT NULL,    b FROM Z GROUP BY b

В списке выражений для ROLLUP, CUBE и GROUPING SETS также возможно группирование. Например, возможен запрос типа (AGG — условное обозначение произвольной агрегирующей функции):

SELECT a, b, c, AGG ( d )
FROM Z
GROUP BY ROLLUP ( a, ( b, c ) )

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

SELECT    a,    b,    c, AGG ( d ) FROM Z GROUP BY a, b, c UNION ALL
SELECT    a, NULL, NULL, AGG ( d ) FROM Z GROUP BY a       UNION ALL
SELECT NULL, NULL, NULL, AGG ( d ) FROM Z

То есть при подсчете обобщений пары (тройки, четверки, …) выражений будут приниматься как целое ("составной столбец"). Группирование может быть произвольным: ( ( a, b ), c ), ( a, ( b, c ), d ), ( ( a, b ), ( c, d, e ) ) и так далее. Равным образом сказанное распространяется на CUBE и GROUPING SETS. Возможно также и комбинирование типа следующего:

SELECT a, b, c, AGG ( d )
FROM   Z
GROUP BY GROUPING SETS ( a, ROLLUP ( b, c ) )
Это равносильно формулировке
SELECT a, NULL, NULL, AGG ( d ) FROM Z GROUP BY a
UNION ALL
SELECT NULL, b,    c, AGG ( d ) FROM Z GROUP BY ROLLUP ( b, c )