| {code:SQL | title= 예제 | borderStyle=solid} select e.department_id, e.job_id, sum(e.salary) avg_sal from employees e group by grouping sets((e.department_id, e.job_id), (e.department_id)); |
============
Plan Table
============
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | 1 | 32 | 00:00:00.01 | 7 | ||||
| 1 | SORT GROUP BY ROLLUP | 1 | 32 | 00:00:00.01 | 7 | 2048 | 2048 | 2048 (0) | |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 00:00:00.01 | 7 |
* Plan상에서 Rollup이 발생하였다.|
| * SQL을 풀어서 확인하면 다음과 같다.
{code:SQL|title= SQL 비교 |borderStyle=solid}
-- Grouping Set을 expand_gset_to_union방식으로 변환
select e.department_id, e.job_id job_id, avg(e.salary) avg_sal
from employees e
group by e.department_id, e.job_id
union all
select e.department_id, Null job_id, avg(e.salary) avg_sal
from employees e
group by e.department_id;
-- Rollup으로 변환
select e.department_id, e.job_id, avg(e.salary) avg_sal
from employees e
group by e.department_id, rollup(job_id);
rollup응용 설명
SELECT A, B, C, SUM(D)
FROM T
GROUP BY C, ROLLUP(A, B)
-->
GROUP BY C, A, B
UNION ALL
GROUP BY C, A
UNION ALL
GROUP BY C, NULL