[Oracle] 오라클 - ROLLUP/CUBE/GROUPING + GROUP_ID

[Oracle] 
오라클 - ROLLUP/CUBE/GROUPING + GROUP_ID

---

나 정말 sqld 자격증 따서 다행이다...ㅎ 그때 열심히 공부한 내용에서 도움받는중..

 

✔️ ROLLUP

표현식이 2개라면, 3레벨까지 표현된다. (a별 b의 소계, a별 소계, 총계)

표현식이 1개라면, x별 a의 소계와 x의 소계 만 출력. 총계는 출력되지 않는다.

 

-- GROUP BY ROLLUP(a, b)

a+b    => a 별 b의 소계 : GROUP BY a, b 의 결과

a       => a 별 소계

전체   => 마지막에 한번

 

 

-- GROUP BY x, ROLLUP(a, b) : x는 디폴트로 마지막까지 나옴

x+a+b

x+a

x

 

-- GROUP BY x, ROLLUP(a) : 마지막 총계가 나오지 않는다. 

x+a

x

 

 

-- 부서별 인원수와 마지막 레코드는 전체 인원수 출력

    SELECT dept, COUNT(*)
    FROM emp
    GROUP BY ROLLUP(dept);

 

 

 

✔️ CUBE

결합 가능한 모~든 값에 대하여 다차원 집계를 생성한다. 큐브 절의 표현식은 2의 n승의 그룹화를 이룬다. 롤업에 비해 상대적으로 시스템의 연산 대상이 많다. 정렬이 필요하면 ORDER BY 로 정렬하도록 명시한다.

 

-- CUBE 절 예 

EXPR1 + ( A,B  )
EXPR1 + (A)
EXPR1 + (B)
EXPR1 

 

 

-- dept별 pos의 sal 소계, dept별 소계, pos별 소계, 마지막에 총계 출력

	SELECT dept, pos, SUM(sal)
	FROM emp
	GROUP BY CUBE(dept, pos)
	ORDER BY dept, pos;

 

 

✔️ GROUPING

그룹핑 함수는 롤업이나 큐브와 함께 사용하여, 롤업과 큐브에서 사용되었는지를 보여주는 함수다.

GROUPING : 결과값 0 또는 1

0 : 롤업이나 큐브에서 사용되었음을 나타냄.

1 : 롤업이나 큐브에서 사용되지 않았음을 나타냄

 

ex) ROLLUP 의 경우

     (0,0)
     (0,1)
     (1,1)

 

-- 부서별 평균 및 전체 평균

SELECT dept, pos, GROUPING(dept), GROUPING(pos), TRUNC(AVG(sal))
	FROM emp
	GROUP BY ROLLUP(dept, pos)
	HAVING GROUPING(pos) = 1;

SELECT dept, TRUNC(AVG(sal))
	FROM emp
	GROUP BY ROLLUP(dept, pos)
	HAVING GROUPING(pos) = 1;

 

 

✔️ GROUP_ID

그룹 아이디는 보통 필터링할 때 사용한다. 그룹 아이디를 가지고 값이 1인 경우(롤업, 큐브에서 사용안됨) 평균값을 계산하게 하도록 만드는 등 활용이 가능하다. 

 

 

⭐ 아래처럼 작성하면 똑같은 내용이 2번 나올수도 있다.
GROUP BY dept, ROLLUP(dept , (empNo, name))

= 부서별 합계가 2번 나온다. 한번은 롤업에 사용된 dept 으로 그룹 아이디 0/ 다른 한번은 밖에 dept 으로 나온 dept 합계

SELECT dept, empNo, GROUP_ID(),
		DECODE(GROUP_ID(), 0, NVL(name,'합계'), '평균')name,
		DECODE(GROUP_ID(), 0, SUM(sal), ROUND(AVG(sal))) sal
FROM emp
GROUP BY dept, ROLLUP(dept, (empNo, name));

 

 

🔒 문제

--부서명, 사번, 이름, 급여
--부서명 오름차순 정렬. 부서가 바뀌면 부서별 합계, 평균 출력 

SELECT dept, empNo, 
	DECODE(GROUP_ID(), 0, NVL(name, '합계'), '평균' ) name,
	DECODE(GROUP_ID(), 0, SUM(sal), ROUND(AVG(sal))) sal
FROM emp
GROUP BY dept, ROLLUP(dept , (empNo, name))
ORDER BY dept, empNo;