ROLLUP

만약 나는 '부서-직업' 묶음으로 급여합계도 궁금하고 

부서별 급여합계도 궁금하고

그냥 전직원 급여합계도 궁금한데 이걸 한번에 보고싶다고 하면 왠지 이 문장들을 UNION ALL로 합치면 될 것 같다 

SELECT DEPTNO, JOB, SUM(SAL) 
  FROM EMP 
 GROUP BY DEPTNO, JOB ;

SELECT DEPTNO, SUM(SAL) 
  FROM EMP 
 GROUP BY DEPTNO ;

SELECT SUM(SAL) 
  FROM EMP  ;

 

그러나 이렇게 하면 에러가 난다. 얘네 각각 실행하면 나오는 표 모양이 다 다른데 합치려니 자릿수가 달라서 결과를 못 내준다. 

그럼 제일 큰 놈에 맞춰서 다른애들은 필요없는데에다가 null을 넣으면 된당.

오호라 이렇게 하니까 왠지 엑셀에서 만들어 둔 표 같다. 중간합, 총합이 들어가있는.

 

하지만 이쯤되면 이런걸 지원하는 함수가 왠지 있을 것 같지... 맞다 그것이 바로 rollup 

rollup은 group by 절을 확장하는 역할을 한다. 

deptno와 job 값이 같은것끼리 그룹을 만들고  - - 부서/job별 급여합계

그다음에 우측 끝의 값을 빼고 deptno만 가지고 해보고 - -부서별 급여합계

그다음에 전체합을 해본다 - - 전체 급여합계

 

그럼 우리는 job이 같은것끼리의 합계는 알 수가 없다.

그래서 컬럼순서가 중요하다. 오른쪽부터 빠져나가니까  안만들어지는 조합이 생길 수밖에 없다. 

만약 모든 경우의 수를 고려한 조합의 결과를 보고싶으면, CUBE라는 함수를 쓰면 된다.

다만 CUBE는 결과가 너무 많이 나와서 부하가 심해서 실무에서는 잘 사용하지 않는다. DB에 따라 이 기능을 지원하지 않기도 한다.

 

SELECT DEPTNO, JOB, MGR, SUM(SAL) 
  FROM EMP 
 GROUP BY CUBE(DEPTNO, JOB, MGR) ;

 

위 문장을 실행하면 

(DEPTNO, JOB, MGR)

(DEPTNO, JOB) (DEPTNO, MGR) (JOB, MGR)

(DEPTNO) (JOB) (MGR)

()

이 모든 그룹에 대한 결과를 다 보여준다. 

 

근데 나는  (DEPTNO, JOB),(DEPTNO,MGR) 조합만 필요하다 그러면 또 방법이 있음 GROUPING SET.

그룹바이그룹핑셋츠,,, 

 

 

두번째 방법은 (솔직히 이해가 잘 안되지만)  GROUPING 을 이용하는 것이다

GROUPING은 인수가 하나밖에 없고, 그 인수가 그룹생성에 참여했다면 0, 안했다면 1 을 리턴한다.

GROUPING_ID는 위에서 가져온 이진수를 십진수로 바꿔준다. 그걸로 둘이상의 컬럼조합이 컬럼생성에 참여했는지 안했는지를 확인할 수 있다. 

SELECT DEPTNO, JOB, MGR, SUM(SAL)
       ,GROUPING(DEPTNO), GROUPING(JOB), GROUPING(MGR)
       ,GROUPING_ID(DEPTNO,JOB,MGR)  
  FROM EMP 
 GROUP BY CUBE(DEPTNO, JOB, MGR) 
HAVING (GROUPING(DEPTNO) = 0 
   AND GROUPING(JOB)    = 0 
   AND GROUPING(MGR)    = 1 )
    OR (GROUPING(DEPTNO) = 0 
   AND GROUPING(JOB)    = 1 
   AND GROUPING(MGR)    = 0 ) ;

 

GROUP BY절까지만 실행하면 이런 결과가 나오는데, 

우리가 원하는 DEPTNO + MGR 조합이 나오려면 

GROUPING(DEPTNO) 0 [컬럼생성에 참여함] , GROUPING(JOB) 1 [컬럼생성에 참여안함], GROUPING(MGR) 0 [컬럼생성에 참여함] 조합이어야 한다  즉 이진수로 010

이걸 그대로 HAVING 조건에 넣은 것이 위의 식이고 ↑ 

 

010을 십진수로 바꾸면 2가 되고

우리가 원하던 또 다른 조합 (DEPTNO, JOB)은 001이라 십진수로 1이 되니 

GROUPING_ID가 1이거나 2인 결과를 내놓으라고 아래와 같이 식을 쓸 수도 있다. ↓ 이렇게.

근데 여기 MGR에 NULL은 뭐란말인가? 얘는 조합하다가 null이 나온게 아니라 애초에 null이다. 사장님이라 자기 위에 매니저가 없는 경우여서 값 자체가 null인 것.

 

이게 머선 null인지 궁금하다면 아까 위에서 '그룹 생성에 참여했으면 0, 참여안했으면 1을 리턴하는' grouping 함수를 확인해보자. 0이다. 참여했다는 것이다(그룹하면서 NULL된게 아니라 그냥 원래 값이 null). 다른 MGR 숫자들어간애들처럼 0으로 나온다. 

 

참고로 다시 ROLLUP으로 돌아가면, 죄다 ROLLUP 안에 넣어야 하는건 아니다. 이렇게 하나는 밖으로 뺄수도 있다. 근데 그렇게 하면 전체합계 값은 안나온다.