Neo-Oracle Home

1. rollup을 grouping sets로 바꿔본 예

-rollup

select manager_id, job_id, sum(salary)
from employees
where manager_id is not null
and manager_id < 120
group by rollup(manager_id, job_id);

-rollup to grouping sets

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets (
                            (manager_id, job_id),
                            (manager_id),
                            ()
                          );

2. cube를 grouping sets로 바꿔본 예

-cube

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by cube(manager_id, job_id);

-cube to grouping sets

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets(
                          (),
                          (job_id),
                          (manager_id),
                          (manager_id, job_id)
                          );

그런데 정렬이 동일하지 않다. 그래서 정렬도 추가한다. 단 null값은 기본적으로 오름차순시 가장 큰 값 위치로 정렬되기에 이를 바꾼다.

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets(
                          (),
                          (job_id),
                          (manager_id),
                          (manager_id, job_id)
                          )
order by 1 nulls first, 2 asc nulls first;

한번 grouping sets을 터득해보니 cube와 rollup보다 자유로워진 느낌이다.