Функции ROLLUP и CUBE в предложении SELECT
Стивен Фернстайн
Источник: Рекомендации RevealNet (группа PL/SQL) за январь 2001, (http://128.121.241.221/Pipelines/PLSQL/tips.htm)
ROLLUP and CUBE Features of SELECT, by Steven
Feuerstein
Благодарю
Fawwad-uz-Zafar Siddiqi (fuzs_98@yahoo.com).
Новые возможности Oracle 8i - ROLLUP и CUBE существенно упрощают программирование на SQL тем, что устраняют необходимость проектировать UNION-предложения. Приводимые ниже примеры демонстрируют это:
Выведем строки таблицы EMP из всем в Oracle известной схемы SCOTT:
select deptno, empno, ename, job, sal
from emp order by deptno, empno;
|
DEPTNO |
EMPNO |
ENAME |
JOB |
SAL |
|
-------- |
-------- |
-------- |
-------- |
----- |
|
10 |
7782 |
CLARK |
MANAGER |
2450 |
|
10 |
7839 |
KING |
PRESIDENT |
5000 |
|
10 |
7934 |
MILLER |
CLERK |
1300 |
|
20 |
7369 |
SMITH |
CLERK |
800 |
|
20 |
7566 |
JONES |
MANAGER |
2975 |
|
20 |
7788 |
SCOTT |
ANALYST |
3000 |
|
20 |
7876 |
ADAMS |
CLERK |
1100 |
|
20 |
7902 |
FORD |
ANALYST |
3000 |
|
30 |
7499 |
ALLEN |
SALESMAN |
1600 |
|
30 |
7521 |
WARD |
SALESMAN |
1250 |
|
30 |
7654 |
MARTIN |
SALESMAN |
1250 |
|
30 |
7698 |
BLAKE |
MANAGER |
2850 |
|
30 |
7844 |
TURNER |
SALESMAN |
1500 |
|
30 |
7900 |
JAMES |
CLERK |
950 |
14 rows selected.
Если требуется найти число служащих по департаментам, надо ввести запрос:
select deptno, count(*)
from emp
group by deptno;
|
DEPTNO |
COUNT(*) |
|
-------- |
-------- |
|
10 |
3 |
|
20 |
5 |
|
30 |
6 |
Если при этом нужно также
вывести на выход число служащих, требуется ввести фразу UNION, как показано
ниже:
select deptno, count(*)
from emp
group by deptno
union
select to_number(null), count(*)
from emp;
|
DEPTNO |
COUNT(*) |
|
-------- |
-------- |
|
10 |
3 |
|
20 |
5 |
|
30 |
6 |
|
|
14 |
В Oracle8i можно
использовать опцию ROLLUP, чтобы достигнуть того же самого результата:
select deptno, count(*)
from emp
group by rollup(deptno);
|
DEPTNO |
COUNT(*) |
|
-------- |
-------- |
|
10 |
3 |
|
20 |
5 |
|
30 |
6 |
|
|
14 |
Просто включая функцию
ROLLUP во фразе GROUP BY, мы указываем Oracle, чтобы он просуммировал данные по
уровнях указанных выше столбцов и подвел общий итог. Обратите внимание, что
когда Oracle сообщает общий итог, то остается незаполненной строка в столбце,
по которому строилась фраза GROUP BY. Если столбец GROUP BY также содержит
пустые значения, то может быть трудно отличить его значения от итога по строке.
К счастью, мы можем использовать специальную функцию, называемую GROUPING,
которая сообщает о статусе (summarization) текущего уровеня. Функция возвращает
два значения: "0" указывает, что текущая строка является группой,
специфицированной уровнем GROUP BY, а "1" указывает, что строка
сгруппирована на более высоком уровне.
select deptno, count(*), grouping(deptno)
from emp
group by rollup(deptno);
|
DEPTNO |
COUNT(*) |
GROUPING(DEPTNO) |
|
-------- |
-------- |
-------- |
|
10 |
3 |
0 |
|
20 |
5 |
0 |
|
30 |
6 |
0 |
|
|
14 |
1 |
Теперь мы можем получить
преимущества от использования функции GROUPING при форматировании выходного
листинга:
select decode(grouping(deptno),0,to_char(deptno),'Total') deptno,
count(*)
from emp
group by rollup(deptno);
|
DEPTNO |
COUNT(*) |
|
-------- |
-------- |
|
10 |
3 |
|
20 |
5 |
|
30 |
6 |
|
Total |
14 |
Функция ROLLUP, действительно,
может работать на нескольких столбцах. Результат многоуровнего действия rollup
демонстрируется ниже:
select deptno, job, count(*), grouping(deptno), grouping(job)
from emp
group by rollup(deptno, job);
|
DEPTNO |
JOB |
COUNT(*) |
GROUPING |
GROUPING |
|
-------- |
-------- |
-------- |
-------- |
-------- |
|
10 |
CLERK |
1 |
0 |
0 |
|
10 |
MANAGER |
1 |
0 |
0 |
|
10 |
PRESIDENT |
1 |
0 |
0 |
|
10 |
|
3 |
0 |
1 |
|
20 |
ANALYST |
2 |
0 |
0 |
|
20 |
CLERK |
2 |
0 |
0 |
|
20 |
MANAGER |
1 |
0 |
0 |
|
20 |
|
5 |
0 |
1 |
|
30 |
CLERK |
1 |
0 |
0 |
|
30 |
MANAGER |
1 |
0 |
0 |
|
30 |
SALESMAN |
4 |
0 |
0 |
|
30 |
|
6 |
0 |
1 |
|
|
|
14 |
1 |
1 |
Как показано на этом примере,
мы можем подсчитать служащих по 1) отделам и профессиям; 2) по отделам; 3)
общим итогом.
Просто ROLLUP является и
CUBE-функцией. CUBE группирует данные по нескольким измерениям. В частности,
она суммирует данные, которые явно наличествуют в столбцах, специфицированных в
фразе GROUP BY. Посмотрим, как она работает:
select deptno, job, count(*), grouping(deptno), grouping(job)
from emp
group by cube(deptno, job);
|
DEPTNO |
JOB |
COUNT(*) |
GROUPING |
GROUPING |
|
-------- |
-------- |
-------- |
-------- |
-------- |
|
10 |
CLERK |
1 |
0 |
0 |
|
10 |
MANAGER |
1 |
0 |
0 |
|
10 |
PRESIDENT |
1 |
0 |
0 |
|
10 |
|
3 |
0 |
1 |
|
20 |
ANALYST |
2 |
0 |
0 |
|
20 |
CLERK |
2 |
0 |
0 |
|
20 |
MANAGER |
1 |
0 |
0 |
|
20 |
|
5 |
0 |
1 |
|
30 |
CLERK |
1 |
0 |
0 |
|
30 |
MANAGER |
1 |
0 |
0 |
|
30 |
SALESMAN |
4 |
0 |
0 |
|
30 |
|
6 |
0 |
1 |
|
|
ANALYST |
2 |
1 |
0 |
|
|
CLERK |
4 |
1 |
0 |
|
|
MANAGER |
3 |
1 |
0 |
|
|
PRESIDENT |
1 |
1 |
0 |
|
|
SALESMAN |
4 |
1 |
0 |
|
|
|
14 |
1 |
1 |
Вывод функции CUBE включает выходной листинг, генерируемый ROLLUP. Дополнительно, она отображает число
служащих по профессиям, независимо от номера отдела