GROUPING( ) возвратит для них обоих 0. Результат для d iv is io n _ id комбинируется
с результатом для jo b _ id , в результате чего получается
битовый вектор 00, десятичным эквивалентом которого будет просто
0. Поэтому, если оба столбца содержат непустые значения,
GR0UPING_ID() возвращает 0.
■ Если division_id является не пустым (бит GROUPING равен 0), a job_id
содержит пустое значение (бит GROUPING равен 1), результирующий
вектор равен 01 и GROUPING_ID() возвращает 1.
■ Если division_id является пустым (бит GROUPING равен 1), a job_id содержит
непустое значение (бит GROUPING равен 0), результирующий
вектор равен 10 и GR0UPING_ID() возвращает 2.
■ Если оба столбца division_id и job_id содержат пустые значения,
GR0UPING() возвратит для них обоих по 1, битовый вектор будет равен
11 и GR0UPING_ID() возвратит 3.
В приведенной ниже таблице эти результаты показаны более наглядно:
d iv is io n _ id jo b _ id Битовый вектор Возвращенное значение
GR0UPING_ID()
не пустой не пустой 00 0
не пустой пустой 01 1
пустой не пустой 10 2
пустой пустой 1 1 3
Пример запроса, иллюстрирующий применение функции
G R O U P IN G _ ID ()
В следующем примере в функцию GR0UPING_ID() передаются значения столбцов
d iv is io n _ id и jo b _ id . Выходные данные функции GR0UPING_ID() согласуются
с ожидаемыми возвращаемыми значениями, документированными
в предыдущем разделе:
□ SELECT
division_id, job_id,
GR0UPING(division_id) AS DIV_GRP,
GROUPINGSob_id) AS J0B_GRP,
GR0UPING_ID(division_id, job_id) AS grp_id,
SUM(SALARY)
FROM employees2
GROUP BY CUBE(division_id, job_id)
ORDER BY division_id, job_id;
220 Глава 7
DIV JOB DIV_GRP J0B_GRP GRP_ID SUM(SALARY)
BUS MGR 0 0 0 530000
BUS PRE 0 0 0 800000
BUS WOR 0 0 0 280000
BUS 0 1 1 1610000
OPE ENG 0 0 0 245000
OPE MGR 0 0 0 805000
OPE WOR 0 0 0 270000
OPE 0 1 1 1320000
SAL MGR 0 0 0 4446000
SAL WOR 0 0 0 490000
SAL 0 1 1 4936000
SUP MGR 0 0 0 465000
SUP TEC 0 0 0 115000
SUP WOR 0 0 0 435000
SUP 0 1 1 1015000
ENG 1 0 2 245000
MGR 1 0 2 6246000
PRE -1 . 0 2 800000
TEC 1 0 2 115000
WOR 1 0 2 1475000
1 1 3 8881000
Полезное применение функции G R O U P IN G _ ID ()
Одно из полезных применений функции GROUPING_ID() - это фильтрация
строк при помощи фразы HAVING. Фраза HAVING может исключать строки,
не содержащие промежуточных итогов или итогов, просто проверяя, возвращает
ли функция GROUPING_ID() значение больше 0:
□ SELECT
division_id, job_id,
GROUPING_ID(division_id, job_id) AS grp_id,
SUM(salary)
FROM employees2
GROUP BY CUBE(division_id, job_id)
HAVING GROUPING_ID(division_id, job_id) > 0
ORDER BY division_id, job_id;
DIV JOB GRP_ID SUM(SALARY)
BUS 1 1610000
OPE 1 1320000
SAL 1 4936000
SUP 1 1015000
ENG 2 245000
MGR 6246000
PRE 2 800000
TEC 2 115000
WOR 2 1475000
3 8881000
Расширенные запросы 221
Многократное использование столбца во фразе GROUP BY
Во фразе GROUP BY имя столбца можно использовать несколько раз, что
позволяет реорганизовать данные или отчет для различной группировки
данных. В следующем запросе встречается фраза GROUP BY, дважды использующая
столбец division_id: один раз для группировки по столбцу
division_id, а второй - во фразе ROLLUP:
□ SELECT division_id, job_id, SUM(salary)
FROM employees2
GROUP BY division_id, ROLLUP(division_id, job_id);
DIV JOB SUM(SALARY)
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
SAL MGR 4446000 -
SAL WOR 490000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
Однако, обратите внимание - последние четыре строки выходных данных
дублируют предыдущие четыре строки. Это дублирование можно устранить,
используя функцию GR0UP_ID.
Использование функции GROUPJD()
Функцию GR0UP_ID() можно использовать для устранения дублирования
строк, возвращаемых фразой GROUP BY. Функция GR0UP_ID() не принимает
никаких параметров. Если в какой-либо конкретной группировке есть п
дубликатов, GR0UP_ID возвратит числа в диапазоне от 0 до п-1.
В приведенном ниже примере переписывается запрос, показанный в
предыдущем разделе, с включением в него выходных данных функции
GR0UP_ID(). Для всех строк, кроме последних четырех, функция GR0UP_ID()
возвращает 0, но для последних четырех строк, которые дублируют предыдущие
четыре, GR0UP_ID() возвращает 1:
□ SELECT division_id, job_id, GR0UP_ID(), SUM(salary)
FROM employees2
GROUP BY division_id, ROLLUP(division_id, job_id);
222 Глава 7
DIV JOB GR0UP_ID() SUM(SAL
BUS MGR 0 530000
BUS PRE 0 800000
BUS WOR 0 280000
OPE ENG 0 245000
OPE MGR 0 805000
OPE WOR 0 270000
SAL MGR 0 4446000
SAL WOR 0 490000
SUP MGR 0 465000
SUP TEC 0 115000
SUP WOR 0 435000
BUS 0 1610000
OPE 0 1320000
SAL 0 4936000
SUP 0 1015000
BUS 1 1610000
OPE 1 , - 1320000
SAL 1 4936000
SUP 1 1015000
Теперь можно удалить дублирующиеся строки, включив в запрос фразу