1. ROLLUP()
ROLLUP 은 GROUP BY 결과물 테이터 항목별 합을 나타낸다,
아래와 같은 테이블에서 GROUP BY 만 한것과 ROLLUP 결과물 차이를 보면쉽게 알수 있다.
위> GROUP BY 로만 한결과문 : B_TYPE(형액형) 별로 PT(포인트)의 평균을 구해보았다.
SELECT B_TYPE, ROUND(AVG(PT))
FROM TBL_POINT
GROUP BY B_TYPE;
아래> ROLLUP 까지 적용 : 제일 마지막행은 혈액형별 전체 평균 이것은 (결국 모든이의 포인트 평균이 된다.)
낸것들의 합이 나타난다.
SELECT B_TYPE, ROUND(AVG(PT))
FROM TBL_POINT
GROUP BY ROLLUP(B_TYPE);
2. ROLLUP() 인자로 두 가지 column을 넣었을?
SELECT JOB,B_TYPE, ROUND(AVG(PT))
FROM TBL_POINT
GROUP BY ROLLUP(JOB,B_TYPE);
위 쿼리문에서는 ROLLUP 시킬 것으로 형액형외에 JOB(직업)이라는 요소도 추가하였다.
참고로 편의성을 위해 직업은 숫자로 표현되게하였다. (10 은 학생을 의미, 20은 회사원을 의미 이런식으로)
위에서 보면 직업별로 형액혈별 모두를 적용시켜 종합시킨 포인트의 평균이 나온다.
위에서 직업번호10번인 사람들중 형액형별(여기서는 O,AB)로 다시 구분해서
포인트의 평균값이 나왔다.
참고로 NULL값들은 해당항목의 총계를 나타낸다.지금은 구분이 어렵지만 테크닉으로
NULL값대신 적당한 값 '10번부서의 포인트 평균" , '총평균' 이런식으로 이름을 지어줄수 있다.
(후반부에 설명함)
3. CUBE
큐브명령은 ROLLUP과 거의 같다.
그러나 차이점은 바로 위에 2번에서 설명한 ROLLUP결과물과 아래것을 비교해보라
SELECT B_TYPE, ROUND(AVG(PT))
FROM TBL_POINT
GROUP BY CUBE(JOB ,B_TYPE);
ROLLUP대신 CUBE를 쓴것외에 차이가 없는 더 많은 항목이 나온것을 알수 있다.
ROLLUP 결과물이 먼저 직업별로 group을 하고-> 각가 직업에 대한 혈액형별로 구분을 둘다 적용한 결과 보여주는데 반해
CUBE는 모든 조합결과물을 보여준다. 여기서는 형액혈별로 평균값을 보여주고 -> 직업별 group을 한후 -> 그 각각 직업에 형액형별로
구분을 적용한결과를 보여준다.
3. CUBE 와 ROLLUP 비교
3개이상의 column을 ROLLUP 이나 CUBE해줄수도 있으므로 이를 일반화해보면
ROLLUP(a,b,c) : a,b,c 집계 -> a,b 집계 -> a 집계 -> 전체합계표시 순으로 하는데 반해
CUBE(a,b,c) : a,b,c 집계 -> (a, b) 집계 -> (b,c) 집계 ->
(a,c)집계 -> (a)집계 -> (b)집계 -> (c) 집계 -> 전체합계표시순으로 모든 조합 결과를 보여줍니다.
4. GROUPING SETS
CUBE 명령시에 내가 원치 않는 모든 조합의 결과물을 보여주므로 내가 원하는 않는 부분도 보게 되므로
원하는 부분만 세팅해서 볼수 이씨게 하는것이 GROUPING SETS 입니다.
예를들어서 형액형,직업별을 같이 조합한 결과물과 형액형별,부서별의 조합결과만 보고 싶다면
SELECT B_TYPE, JOB, DEPTNO
FROM 테이블이름
GROUPING SETS((B_TYPE, JOB),(B_TYPE,DEPTNO))
이런식으로 쿼리문을 작성하면됩니다.
5. GROUPING()
- 그것이 총계를 나타내는 결과물이면 1, 아니면 0을 반환 합니다.
SELECT B_TYPE, ROUND(AVG(PT)),GROUPING(B_TYPE)
FROM TBL_POINT
GROUP BY ROLLUP(B_TYPE);
위에서 NULL 부분은 총합계를 나타나므로 GROUPING 값이 1을 반환했습니다.
어찌보면 그리 의미가 없는 결과물같습니다만.
이를 잘 이용하면 아까 앞에서 총계를 나타내는 부분이 NULL로 나와버리는 문제를 해결할수 있습니다.
<참고> ROLLUP, CUBE 결과물로 나오는 NULL 값을 원래?이로 변경해보는 테크닉
CASE 문 또는 DECODE문을 쓰면 해결된다.
( DECODE문의 경우는 다음 사이트를 참고해보라 http://www.statwith.pe.kr/ORACLE/functions054.htm)
위의 표에서 밑의 쿼리문을 실행해보면
SELECT JOB,B_TYPE,ROUND(AVG(PT)), GROUPING(JOB),GROUPING(B_TYPE)
FROM TBL_POINT
GROUP BY ROLLUP(JOB,B_TYPE);
위에처럼 총합이라는 표현이 NULL값이 나와버린다. 이것을 적당한 표현이 되도록 CASE문의 경우로 해결해보자.
예를들면 job 10 의 null값은 10번 부서 전체의 '포인트평균값'을 의미하므로 바꾸어주면 해석이 편할것이다.
GROUPING 값을 표시한 이유는 이 값을 이용해서 1일때는 총합을 의미하기 ?문에
그 값을 가진 필드값만 변환해주는 테크닉을 쓸것이기 때문이다.
SELECT
CASE GROUPING(JOB) WHEN 1 THEN '총평균' ELSE TO_CHAR(JOB) END AS "직업번호",
CASE GROUPING(B_TYPE) WHEN 1 THEN '혈액형별평균' ELSE B_TYPE END AS "혈액형",
ROUND(AVG(PT)) AS "평균포인트값"
FROM TBL_POINT
GROUP BY ROLLUP(JOB,B_TYPE);
