[Oracle] 그룹함수 - GROUP BY의 확장

# 그룹함수 - GROUP BY의 확장

GROUP BY ROLLUP절 

GROUP BY ROLLUP의 동작 방식 

• 뒤 인자부터 순차적으로 소거하며 그룹핑 
  • GROUP BY ROLLUP(a, b, c): GROUP BY a, b, c / GROUP BY a, b / GROUP BY a / GROUP BY 전체 수행  
  • GROUP BY ROLLUP(a, (b, c)): GROUP BY a, (b, c) / GROUP BY a / GROUP BY 전체 수행  */ 

SELECT deptno, job, COUNT(*) 
FROM   emp 
WHERE  deptno IN (10, 20, 30) 
GROUP BY ROLLUP(deptno, job); 

-- ROLLUP 안에서 세분화된 그룹핑 
SELECT deptno, job, COUNT(*) 
FROM   emp 
WHERE  deptno IN (10, 20, 30) 
GROUP BY ROLLUP((deptno, job));

  

GROUP BY CUBE절 

GROUP BY CUBE의 동작 방식  

• 모든 인자들의 가능한 조합을 그룹핑 
  • GROUP BY CUBE(a, b, c): GROUP BY a, b, c / GROUP BY a, b / GROUP BY a, c / GROUP BY b, c / GROUP BY a / GROUP BY b / GROUP BY c / GROUP BY 전체 수행  

SELECT deptno, job, COUNT(*) 
FROM   emp 
WHERE  deptno IN (10, 20, 30) 
GROUP BY CUBE(deptno, job);

  

GROUP BY GROUPING SETS절 

• 표시된 인수들에 대한 개별 집계 구하는 것이 가능 

-- 총계없이 부서별 소계만 구하고 싶은 경우 
SELECT deptno, job, COUNT(*) 
FROM   emp 
WHERE  deptno IN (10, 20, 30) 
GROUP BY GROUPING SETS((deptno, job), (deptno)); 

-- 총계만 구하고 싶은 경우 
SELECT deptno, job, COUNT(*) 
FROM   emp 
WHERE  deptno IN (10, 20, 30) 
GROUP BY GROUPING SETS((deptno, job), ());