Вторник, 23.07.2024, 10:41

  • Полезные Скрипты Oracle
Главная Книги Быстрый Поиск О Сайте

Oracle
PL/SQL
Решения для Oracle
ПОИСК
Быстрый поиск
 CUBE и ROLLUP
Использование CUBE и  ROLLUP отметок в SQL (Oracle 11g)

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








Copyright sql.ucoz.com © 2024
Оцените сайт

Всего ответов: 51