1. 그룹함수
- 집계함수를 제외하고 ROLLUP, CUBE, GROUPING SETS
2. ROLLUP
- 소그룹간의 소계 계산. GROUP BY의 확장된 형태
- GROUP BY 의 칼럼에 대하여 subTotal 만들어줌
- ROLLUP에 지정된 그룹핑 칼럼의 리스트는 소계를 생성하기 위해 사용
- 그룹 잡을 칼럼의 수가 N일 때 SubTotal은 N+1 LEVEL로 생성됨
- ROLLUP 인수는 계층구조! -> 인수의 순서 바뀌면 결과도 바뀌게 됨(먼저나오는 인수가 상위 계층)
SELECT DNAME, JOB COUNT(*) SUM(SAL) FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
LEVEL 1 : 표준 집계. DNAME(첫번째 인자)으로 나눔
LEVEL 2 : DNAME(첫 인자) 별 JOB(두번째 인자) SubTotal 구함
LEVEL 3 : 총 합계
|
LEVEL 2 DNAME별 JOB : 차장 3명 9000만원 |
|
|
LEVEL 2 DNAME별 JOB : 대리 4명 9000만원 |
|
LEVEL1 DNAME : 영업 7명 18000만원 |
|
|
|
LEVEL 2 DNAME별 JOB : 차장 5명 20000만원 |
|
|
LEVEL 2 DNAME별 JOB : 대리 4명 12000만원 |
|
LEVEL1 DNAME : 생산 9명 32000만원 | | |
| | | LEVEL 3 전부 합계 16명 50000만원 |
- 계층 내 정렬을 하려면 ORDER BY로 정렬
3. CUBE
- GROUP BY 항목들 간 다차원적인 소계 계산 (결합가능한 모든 값에 대하 집계 생성) 그러나 부하가 많음
- 표시된 인수들 간에는 평등한 관계라 인수의 순서는 중요하지 않음
- 결과에 대한 정렬이 필요할 시 ORDER BY 절에서 칼럼 명시해야 함
- 그룹 잡을 칼럼의 수가 N일 때 SubTotal은 2^N LEVEL로 생성됨
SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB);
2^2레벨 -> 레벨 4까지의 SUBTOTAL 나온다는 것
LEVEL 1 전체 합계 11000만원 |
|
|
|
LEVEL 2-2 JOB별 합계 차장 6000만원 |
|
| | LEVEL 2-2 JOB별 합계 대리 5000만원 | |
|
LEVEL 2-1 DEPTNO별 합계 영업부 11000만원 |
|
|
|
LEVEL 4 DEPTNO별 JOB별 합계 영업_차장 3000만원 |
|
|
LEVEL 4 DEPTNO별 JOB별 합계 영업_대리 2500만원 |
|
|
LEVEL 4 DEPTNO별 JOB별 합계 생산_차장 3000만원 |
|
|
LEVEL 4 DEPTNO별 JOB별 합계 생산_대리 2500만원 |
4. GROUPING SETS
- 특정항목에 대한 소계 계산-> 원하는 부분의 소계만 추출 가능
- GROUP BY에 나오는 칼럼의 순서와 상관없이 다양한 소계 생성
- 인자간 평등한 관계이며, 소계를 모두 개별적으로 처리
- UNION ALL을 사용한 일반 그룹함수 사용 SQL과 같은 결과가 나옴
SELECT DEPNO, JOB, SUM(SAL) FROM EMP GROUP BY GROUPING SETS(DEPTNO, JOB);
LEVEL 1 JOB별 합계 차장 6000만원 |
LEVEL 1 JOB별 합계 대리 5000만원 |
LEVEL 1 DEPTNO별 합계 영업 6000만원 |
LEVEL 1 DEPTNO별 합계 생산 5000만원 |
5. GROUPING
- ROLLUP CUBE GROUPING SET에서 생성되는 합계 값을 구분하기 위해 만들어진 함수
- ROLLUP 이나 CUBE 등으로 소계가 계산된 결과에는 GROUPING(칼럼명) = 1이 반환, 그 외의 결과는 0 반환
- GROUPING의 반환 값을 DECODE나 CASE 문으로 식별해서 SELECT문으로 필드에 원하는 문자열 지정할 수 있음
SELECT
CASE GROUPING(DNAME) WHEN 1 THEN 'DEPARTMENT TATAL' ELSE DNAEM END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'JOB TOTAL' ELSE JOB END AS JOB, COUNT(*), SUM(SAL)
FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);
SELECT
DECODE(GROUPING(DNAME), 1, 'DEPARTMENT TOTAL', DNAME) AS DNAME,
DECODE(GROUPIN(JOB), 1, 'JOB TOTAL', JOB) AS JOB, COUNT(*), SUM(SAL)
FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY GROUPING SETS (DNAME, JOB);