Полезные Скрипты Oracle

Применение отметок(опций) 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);

Использование CUBE и ROLLUP отметок в SQL (Oracle 11g)