OLAP.ru   Rambler's Top100
Вы находитесь на страницах старой версии сайта. Перейдите на новую версию OLAP.ru
  
Поиск по сайту
Новости
Основы OLAP
Продукты
Business Objects/ Crystal Decisions
Каталог
OLAP в жизни
Тенденции
Download
Яndex
 
 
 
TopList
 
 Business Objects/Crystal Decisions  Oracle  Microsoft  IBM 
 Hyperion  Sybase  Informix  Actuate  SAS Institute  Brio 

Функции ROLLUP и CUBE в предложении SELECT


Стивен Фернстайн, Russian Oracle Internet Magazine

Рекомендации RevealNet (группа PL/SQL) за январь 2001

Новые возможности 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
(DEPTNO)

GROUPING
(JOB)

--------

--------

--------

--------

--------

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
(DEPTNO)

GROUPING
(JOB)

--------

--------

--------

--------

--------

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. Дополнительно, она отображает число служащих по профессиям, независимо от номера отдела

 Обсудить на форуме   Написать вебмастеру 

© 2001 Interface Ltd