[SQLD] 제2장 - 그룹함수(CUBE, ROLLUP, GROUPING SETS)_1

그룹 함수(GROUP FUNCTION)의 필요성

결산 개념의 업무를 가지는 원가나 판매 시스템의 경우는 소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만드는 것이 중요 업무 중의 하나입니다. 개발자들이 이런 보고서를 작성하기 위해서는 SQL이 포함된 3GL으로 배치 프로그램을 작성하거나, 레벨별 집계를 위한 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러 번 읽어 다시 재정렬하는 복잡한 단계를 거쳐야만 합니다.

하지만, 그룹 함수(GROUP FUNCTION)을 사용한다면, 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성할 수 있습니다. 또한, GROUPING 함수와 CASE 함수를 이용하면 쉽게 원하는 포맷의 보고서 작성도 가능합니다.

그룹 함수(GROUP FUNCTION)의 종류 - ROLLUP, CUBE, GROUPING SETS

그렇다면 그룹 함수(GROUP FUNCTION)엔 어떤것들이 있을까요??

ROLLUP 함수 : 소그룹 간의 소계를 계산함

CUBE 함수 : GROUP BY 항목들간 다차원적인 소계를 계산할 수 있음 (모든 그룹케이스를 계산함)

GROUPING SETS 함수 : 특정 항목에 대한 소계를 계산할 수 있음 (GROUPING SETS 은 특정항목을 그룹한 다수의 SQL들을 UNION한 것과 같음)

자, 이제 하나씩 알아 보겠습니다.

1. ROLLUP 함수

ROLLUP에 의해서 생성되는 Subtotal은 Grouping Columns의 수를 N개 라고 했을 때, N+1 Level의 Subtotal이 생성됩니다. 

잊지 말아야 할 것은, ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의해야 합니다.

ROLLUP을 알아보기 전, 일반적인 GROUP BY 절을 사용해 보겠습니다.

STEP 1. 일반적인 GROUP BY 절 사용

부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행한다.

SETP 2. ROLLUP으로 변환하기

- 2개 컬럼을 그룹핑 했으므로, 3개의 소계 행이 생길 것입니다. 

( Subtotal은 Grouping Columns의 수를 N개 라고 했을 때, N+1 Level의 Subtotal이 생성됩니다. )

-> 따로 정렬을 하지 않아도 자동 정렬되서 나옵니다.

-> ROLLUP의 경우 계층 간 집게에 대해서는 LEVEL 별 순서(L1->L2->L3)를 정렬하지만, 계층 내 GROUP BY 수행시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않습니다.

L1 - GROUP BY 수행시 생성되는 표준 집계 (9건)

L2 - DNAME 별 모든 JOB의 SUBTOTAL (3건)

L3 - GRAND TOTAL (마지막 행, 1건)

STEP 3. GROUPING 함수 사용

  ROLLUP, COBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가되었습니다.

- ROLLUP 이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고,

- 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시됩니다.

GROUPING 함수와 CASE/DECODE 를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있어, 보고서 작성시 유용하게 사용될 수 있습니다.

[예제1] ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가된 SQL 문장을 작성하시오.

-> ROLLUP에 의해 소계된 컬럼은 GROUPING 함수에 의해 1로 표시됩니다. 아닌 경우는 0

-> 이제 1과 0의 값에 따라 DECODE 조건을 걸어 소계가 걸리면 빈 공란으로 두지 않고 명칭을 부여합니다.

[예제2] ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가된 SQL 문장을 작성하시오.

            + JOB만 ROLLUP, DNAME은 GROUP BY 기존 방식

-> JOB에 대해서만 소계가 출력되고, DNAME은 따로 소계가 없다. 즉 L3 - GRAND TOTAL (마지막 행, 1건) 가 없습니다.

ROLLUP 함수에 대해 알아봤습니다.

여기서 끊고, 다음 시간에는 CUBE 와 GROUPING SETS에 대해 알아보겠습니다.

수고하셨습니다(?) ㅎㅎ