GROUPING과 GROUPING_ID
GROUPING (컬럼)
- - GROUPING 함수는 ROLLUP, CUBE에 모두 사용할 수 있다.
- - GROUPING 함수는 해당 컬럼의 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 된다.
- - 따라서 해당 Row가 결과집합에 의해 산출된 Data 인지, ROLLUP이나 CUBE에 의해서 산출된 Data 인지를 알 수 있도록 지원하는 함수이다.
GROUPING_ID(컬럼a, 컬럼b[, …])
- - GROUPING(컬럼a)||GROUPING(컬럼b)의 값을 2진수에서 10진수로 변환한 값이다.
아래는 GROUPING과 GROUPING_ID의 예제이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | SELECT deptno , empno , SUM(sal) s_sal , GROUPING(deptno) grp_dept , GROUPING(empno) grp_emp , GROUPING_ID(deptno, empno) gid FROM emp GROUP BY ROLLUP(deptno, empno);-- GRP_DEPT : deptno로 GROUP BY 되면 0, deptno의 값이 없으면 1-- GRP_EMP : empno로 GROUP BY 되면 0, empno의 값이 없으면 1-- GID : GROUPING(deptno) || GROUPING(empno)의 값을 2진수에서 10진수로 변환한 값DEPTNO EMPNO S_SAL GRP_DEPT GRP_EMP GID------ ---------- ---------- ---------- ---------- ---------- 10 7782 2450 0 0 0 10 7839 5000 0 0 0 10 7934 1300 0 0 0 10 8750 0 1 1 20 7369 800 0 0 0 20 7566 2975 0 0 0 20 7788 3000 0 0 0 20 7876 1100 0 0 0 20 7902 3000 0 0 0 20 10875 0 1 1 30 7900 950 0 0 0 30 7499 1600 0 0 0 30 7521 1250 0 0 0 30 7654 1250 0 0 0 30 7698 2850 0 0 0 30 7844 1500 0 0 0 30 9400 0 1 1 29025 1 1 3 |
분석:순번대로 보기 바람
SELECT deptno , empno , SUM(sal) s_sal , GROUPING(deptno) grp_dept //2번 deptno로 group by되면 0,deptno의 값이 없으면 1 , GROUPING(empno) grp_emp //3번 empno로 group by되면 0,empno의 값이 없으면 1 , GROUPING_ID(deptno, empno) gid //4번 GROUPING(deptno) || GROUPING(empno)의 값을 2진수에서 10진수로 변환한 값 이라는데 1 1 이니까 11이되나봄 11의 10진수 값은 3 음.. FROM emp GROUP BY ROLLUP(deptno, empno) //1번 2개의 조건으로 그룹을 엮음;소계만 표시, 총계 제거
아래는 부서별 소계만 조회하고, 전체 총계는 조회하지 않는 예제이다. 위 GROUPING과 GROUPING_ID 예제 실행결과 [GROUPING(deptno)=0] 조건으로 소계만 조회가 가능하다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SELECT deptno , DECODE(GROUPING(empno),1,'소계',empno) empno , SUM(sal) s_sal FROM emp GROUP BY ROLLUP(deptno, empno)HAVING GROUPING(deptno) = 0; DEPTNO EMPN S_SAL------- ---- ---------- 10 7782 2450 10 7839 5000 10 7934 1300 10 소계 8750 20 7369 800 20 7566 2975 20 7788 3000 20 7876 1100 20 7902 3000 20 소계 10875 30 7900 950 30 7499 1600 30 7521 1250 30 7654 1250 30 7698 2850 30 7844 1500 30 소계 9400 |
분석: 순번대로 보기 바람
SELECT deptno , DECODE(GROUPING(empno),1,'소계',empno) empno //3번 empno의 GROUPING값이 1일때만 소계 아니면 empno값을 그대로 추력해주는 문 , SUM(sal) s_sal FROM emp GROUP BY ROLLUP(deptno, empno) //1번 deptno와 empno로 그룹HAVING GROUPING(deptno) = 0 // 2번 그룹핑값이 deptno가 0일때만 ;아래와 같이 GROUP BY 절에서 empno만 ROLLUP 연산[GROUP BY deptno, ROLLUP(empno)]을 하여도 동일한 결과를 얻을 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SELECT deptno , DECODE(GROUPING(empno),1,'소계',empno) empno , SUM(sal) s_sal FROM emp GROUP BY deptno, ROLLUP(empno); DEPTNO EMPN S_SAL------- ---- ---------- 10 7782 2450 10 7839 5000 10 7934 1300 10 소계 8750 20 7369 800 20 7566 2975 20 7788 3000 20 7876 1100 20 7902 3000 20 소계 10875 30 7900 950 30 7499 1600 30 7521 1250 30 7654 1250 30 7698 2850 30 7844 1500 30 소계 9400 |
분석:순번대로 보기 바람.
SELECT deptno , DECODE(GROUPING(empno),1,'소계',empno) empno //2번 empno의 GROUPING값이 1일때만 소계 아니면 empno값을 그대로 추력해주는 문 , SUM(sal) s_sal FROM emp GROUP BY deptno, ROLLUP(empno) //1번 그룹을 이렇게 엮으면 deptno가 중심이 되지만 deptno에대한 총합은 구해지지 않는다 empno만 그룹으로 묶어 합을 구하게된다.총계만 표시, 소계 제거
아래는 전체 총계만 조회하고, 소계는 조회하지 않는 예제이다. 위 GROUPING과 GROUPING_ID 예제 실행결과 [GROUPING_ID(deptno, empno) != 1] 조건으로 소계만 조회가 가능하다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | SELECT deptno , DECODE(GROUPING(deptno),1,'합계',empno) empno , SUM(sal) s_sal FROM emp GROUP BY ROLLUP(deptno, empno)HAVING GROUPING_ID(deptno, empno) != 1; DEPTNO EMPN S_SAL------- ---- ---------- 10 7782 2450 10 7839 5000 10 7934 1300 20 7369 800 20 7566 2975 20 7788 3000 20 7876 1100 20 7902 3000 30 7900 950 30 7499 1600 30 7521 1250 30 7654 1250 30 7698 2850 30 7844 1500 합계 29025 |
분석:위에분석이 되어서 그런가 쉽게 되는듯 하다 순번대로 보시기 바란다.SELECT deptno , DECODE(GROUPING(deptno),1,'합계',empno) empno ////3번 deptno의 GROUPING값이 1일때만 '합계' 아니면 empno값을 그대로 추력해주는 문 , SUM(sal) s_sal FROM emp GROUP BY ROLLUP(deptno, empno) //1번 둘다 묶었다. 전체 월급합과 부서별 월급합이 나옴을 알수 있다.HAVING GROUPING_ID(deptno, empno) != 1 //2번 그런데 두 조건의 || 연산으로 나온 값의 십진수값이 1이 아닐때만 출력하란다. 그럼 둘다 0 0 일때와 1 1 일때만 출력될것이다. || 연산이 |연산이 아니라 문자로 합치기이다 그래서 1,0이면 10 ㅎ 0,1이면 1 이다. 10이 이진수이므로 십진수로 교체하면 2가된다. 그런데 1,0의 경우의수는 없는듯하다.;아래와 같이 ROLLUP 연산을 괄호로 묶어[GROUP BY ROLLUP((deptno, empno))]서 처리가 가능하다. 괄호로 묶은 항목은 하나의 항목처럼 묶여 처리 된다. 이는 코드와 명칭을 함께 그룹바이 롤업할때 주로 사용된다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | SELECT deptno , DECODE(GROUPING(deptno),1,'합계',empno) empno , SUM(sal) s_sal FROM emp GROUP BY ROLLUP((deptno, empno)); DEPTNO EMPN S_SAL------- ---- ---------- 10 7782 2450 10 7839 5000 10 7934 1300 20 7369 800 20 7566 2975 20 7788 3000 20 7876 1100 20 7902 3000 30 7900 950 30 7499 1600 30 7521 1250 30 7654 1250 30 7698 2850 30 7844 1500 합계 29025 |
분석:순번대로 보기 바란다.
SELECT deptno , DECODE(GROUPING(deptno),1,'합계',empno) empno //3번 deptno의 GROUPING값이 1일때만 '합계' 아니면 empno값을 그대로 추력해주는 문 , SUM(sal) s_sal FROM emp GROUP BY ROLLUP((deptno, empno)) //1번 그룹으로 묶는데 가로를 하나 더 쳤다.. 앞서봤던예제와 결과가 같은데 이런방식도 있음을 알아두자 그런데 이건 좀 아닌듯.;태그
문서에 대하여
- - 강좌 URL : http://www.gurubee.net/lecture/2679
- - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
'Oracle' 카테고리의 다른 글
| GROUPING SETS (0) | 2014.12.03 |
|---|---|
| CUBE GROUP BY CUBE (0) | 2014.12.03 |
| Rollup (0) | 2014.12.03 |
| KEEP (0) | 2014.12.03 |
| LAG, LEAD (다른 행의 값을 참조) (0) | 2014.12.03 |
은호아빠
여행, 맛집, 일상, 프로그래밍, 개발자, 윈도우, 웹, jsp, spring, db, mysql, oracle, c#