Применение отметок(опций) CUBE и ROLLUP во фразе GROUP BY запроса позволяет производить функционально более полные операции суммирования. CUBE и ROLLUP предоставляют информацию, которую в ином случае можно можно было получить только посредством дополнительных запросов или кодирования.
Следующий запрос показывает "нормальную" функциональность GROUP BY. Оператор SQL сортирует детальные данные, используя столбцы, указанные в GROUP BY, затем вычисляет результаты - одна строка для каждой группы :Select e.department_id,e.job_id, SUM(e.salary) sum_s
From hr.employees e
Group by e.department_id,e.job_id;
DEPARTMENT_ID |
JOB_ID |
SUM_S |
110 |
AC_ACCOUNT |
8300 |
90 |
AD_VP |
34000 |
50 |
ST_CLERK |
55700 |
80 |
SA_REP |
243500 |
50 |
ST_MAN |
36400 |
80 |
SA_MAN |
61000 |
110 |
AC_MGR |
12008 |
90 |
AD_PRES |
24000 |
60 |
IT_PROG |
28800 |
100 |
FI_MGR |
12008 |
30 |
PU_CLERK |
13900 |
50 |
SH_CLERK |
64300 |
20 |
MK_MAN |
13000 |
100 |
FI_ACCOUNT |
39600 |
|
SA_REP |
7000 |
70 |
PR_REP |
10000 |
30 |
PU_MAN |
11000 |
10 |
AD_ASST |
4400 |
20 |
MK_REP |
6000 |
40 |
HR_REP |
6500 |
ROLLUP строит агрегаты-подитоги (subtotal aggregates) на каждом запрошенном уровне, включая окончательный итог (grand total): Select e.department_id,e.job_id, SUM(e.salary) sum_s
From hr.employees e
Group by ROLLUP(e.department_id,e.job_id);
DEPARTMENT_ID |
JOB_ID |
SUM_S |
|
SA_REP |
7000 |
|
|
7000 |
10 |
AD_ASST |
4400 |
10 |
|
4400 |
20 |
MK_MAN |
13000 |
20 |
MK_REP |
6000 |
20 |
|
19000 |
30 |
PU_MAN |
11000 |
30 |
PU_CLERK |
13900 |
30 |
|
24900 |
40 |
HR_REP |
6500 |
40 |
|
6500 |
50 |
ST_MAN |
36400 |
50 |
SH_CLERK |
64300 |
50 |
ST_CLERK |
55700 |
50 |
|
156400 |
60 |
IT_PROG |
28800 |
60 |
|
28800 |
70 |
PR_REP |
10000 |
70 |
|
10000 |
80 |
SA_MAN |
61000 |
80 |
SA_REP |
243500 |
80 |
|
304500 |
90 |
AD_VP |
34000 |
90 |
AD_PRES |
24000 |
90 |
|
58000 |
100 |
FI_MGR |
12008 |
100 |
FI_ACCOUNT |
39600 |
100 |
|
51608 |
110 |
AC_MGR |
12008 |
110 |
AC_ACCOUNT |
8300 |
110 |
|
20308 |
|
|
691416 |
-сумма каждой группы e.department_id,e.job_id. -подитоги (subtotals). Сумма каждой группы e.department_id.
-окончательный итог(grand total).Общая сумма.
CUBE автоматически вычисляет все возможные комбинации возможных подитогов, включая окончательный итог(grand total): Select e.department_id,e.job_id, SUM(e.salary) sum_s
From hr.employees e
Group by CUBE(e.department_id,e.job_id);
DEPARTMENT_ID |
JOB_ID |
SUM_S |
|
|
7000 |
|
|
691416 |
|
AD_VP |
34000 |
|
AC_MGR |
12008 |
|
FI_MGR |
12008 |
|
HR_REP |
6500 |
|
MK_MAN |
13000 |
|
MK_REP |
6000 |
|
PR_REP |
10000 |
|
PU_MAN |
11000 |
|
SA_MAN |
61000 |
|
SA_REP |
7000 |
|
SA_REP |
250500 |
|
ST_MAN |
36400 |
|
AD_ASST |
4400 |
|
AD_PRES |
24000 |
|
IT_PROG |
28800 |
|
PU_CLERK |
13900 |
|
SH_CLERK |
64300 |
|
ST_CLERK |
55700 |
|
AC_ACCOUNT |
8300 |
|
FI_ACCOUNT |
39600 |
10 |
|
4400 |
10 |
AD_ASST |
4400 |
20 |
|
19000 |
20 |
MK_MAN |
13000 |
20 |
MK_REP |
6000 |
30 |
|
24900 |
30 |
PU_MAN |
11000 |
30 |
PU_CLERK |
13900 |
40 |
|
6500 |
40 |
HR_REP |
6500 |
50 |
|
156400 |
50 |
ST_MAN |
36400 |
50 |
SH_CLERK |
64300 |
50 |
ST_CLERK |
55700 |
60 |
|
28800 |
60 |
IT_PROG |
28800 |
70 |
|
10000 |
70 |
PR_REP |
10000 |
80 |
|
304500 |
80 |
SA_MAN |
61000 |
80 |
SA_REP |
243500 |
90 |
|
58000 |
90 |
AD_VP |
34000 |
90 |
AD_PRES |
24000 |
100 |
|
51608 |
100 |
FI_MGR |
12008 |
100 |
FI_ACCOUNT |
39600 |
110 |
|
20308 |
110 |
AC_MGR |
12008 |
110 |
AC_ACCOUNT |
8300 |
-сумма каждой группы e.department_id,e.job_id. -подитоги (subtotals). Сумма каждой группы e.department_id.
-подитоги (subtotals). Сумма каждой группы
e.job_id.
-итоги (grand total).Общая сумма.
Для того, чтобы улучшить обработку NULL-значений в строках, созданных ROLLUP и CUBE , используют функцию GROUPING, которая возвращает значение 1, если строка - это подитог, созданная ROLLUP или CUBE, и 0 в противном случае:
Select e.department_id,e.job_id, SUM(e.salary) sum_s,GROUPING(e.job_id)
From hr.employees e
Group by CUBE(e.department_id,e.job_id);