제 4-5절 그룹함수

 

1. 데이터 분석 개요

 

가. AGGREGATE FUNCTION

COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들 포함

 

나. GROUP FUNTION

레벨별 집계를 위한 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러 번 읽어 다시 재정렬하는 단계를

그룹함수를 이용하면 한번에 해결 가능, CASE 함수를 이용하면 쉽게 원하는 포맷 보고서 작성 가능

• ROLLUP 함수 : 소그룹 간의 소계를 계산, 확장된 형태, 병렬로 수행가능하기에 효과적임, 계층적 분류를 포함하고 있는 데이터 집계에 적합
• CUBE 함수 : GROUP BY 항목들 간 다차원적인 소계를 계산, 모든값에 대한 다차원적인 집계 생성, 시스템 부하를 줄수 있음
• GROUPING SET 함수 : 특정 항목에 대한 소계를 계산, 원하는 부분만 추출가능
• Rollup 이나 Cube에 의한 소계가 계산된 결과에는 Grouping = 1 이 표시되고 그 외의 결과에는 Grouping = 0 으로 표시

 

다. WINDOW FUNTION

분석함수, 순위함수로 불림, 데이터웨어하우스에서 발전한 기능

 

2. ROLLUP 함수

지정된 그룹 칼럼의 리스트는 SUBTOTAL을 생성하기 위해 사용되어짐

생성되는 SUBTOTAL : 칼럼수 + 1

인수는 계층구조라 인수 순서가 바뀌면 수행결과 바뀌니 인수의 순서에도 주의해야함

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

STEP 2. ROLLUP 함수 사용 + ORDER BY절 사용

→ 그룹 함수 수행시 생성되는 표준집계 + 기준칼럼에 대한 소계 + 전체의 마지막 소계

STEP 3. GROUPING 함수 사용 + CASE 함수 사용(ORACLE경우는 DECODE함수 사용)

GROUPING(EXPR) = 1, 그외의 결과는 GROUPINT(EXPR) = 0 → CASE함수로 소계에 나타내는 필드에 문자열 지정 가능 보고서 작성시 유용함

예제) SELECT    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,

CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,

COUNT(*) "Total Empl",

SUM(SAL) "Total Sal"

FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB);

Oracle의 경우는 DECODE 함수를 사용해서 좀더 짧게 표현할 수 있다.

SELECT     DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,

COUNT(*) "Total Empl", SUM(SAL) "Total Sal"

FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB);

 

STEP 4-1. 일부 칼럼에 ROLLUP 함수 사용

예제) GROUP BY DNAME, ROLLUP(JOB) 최종 소계 결과는 나오지 않음

STEP 4-2. 칼럼을 결합하여 ROLLUP 함수 사용

예제) GROUP BY ROLLUP (DNAME, (JOB, MGR)) 하나의 집합으로 간주하여 칼럼별 집계를 구하지 않음

 

3. CUBE 함수

결합 가능한 모든 값에 대하여 다차원 집계를 생성

그룹핑 칼럼의 순서를 바꿔서 한번 더 쿼리를 추가 수행하고 양쪽 쿼리에서 총계를 모두 생성함으로 연산 대상이 많아 시스템 부하의 단점이 있음

계층구조가 아니라 평등 관계임으로 인수 순서, 정렬순서는 바뀔 수있어도 데이터 결과는 같음

STEP 1. CUBE 함수 이용

칼럼이 가질 수 있는 모든 경우의 수예 대하여 SUBTATAL이 생성됨으로 2의 컬럼수에 대한 레벨의 SUBTATAL이 발생

ROLLUP함수에 비해 업무별 집계를 표시한 추가적인 데이터가 생성

STEP 2. UNION ALL 사용

CUBE함수를 사용하면 반복 엑세스 하는 부분을 한번으로 줄일 수 있음, 수행속도 및 자원 사용율을 개선하고 가독성을 높힘

 

4. GROUPING SET 함수

다양한 소계 집합을 만들 수 있음, 여러 번 반복하지 않아도 원하는 결과 얻을 수 있음

인수별 개별 집계 가능하고 평등관계라 인수 순서 중요하지 않음

각 컬럼별의 총계들을 구함

 

STEP 1. 3개의 인수를 이용

괄호로 묶은 집합 별로 괄호 내는 계층 구조가 아닌 하나의 데이터로 간주하여 집계를 구할 수 있음

 

- Rollup(a,b,c) -

{a,b,c} 그룹의 합계

{a.b} 그룹의 합계

{a} 그룹의 합계

{} 전체 테이블에 대한 합계

 - Cube(a,b.c) -

Cube(a,b,c)

{a,b,c} 그룹의 합계 

{a,b}, {a,c}, {b,c} 그룹의 합계

{a}, {b}, {c} 그룹의 합계

{} 전체 테이블에 대한 합계