과목II SQL 기본 및 활용 2장 - 5절 GROUP FUNCTION

데이터 분석 함수 

(AGGREGATE FUNCTIONS) 

- GROUP FUNCTION의 한 부분이며, GROUP AGGREGATE FUNCTION이라고도 부를 수 있다. 

COUNT,SUM,AVG,MAX,MIN외 각종 집계 함수들이 포함되어 있다. 

 

(GROUP FUNCTIONS)

- 그룹함수로는 집계 함수 제외하면, ROLLUP함수, CUBE 함수, GROUPING SETS 함수가 있다. 

- ROLLUP은 GROUP BY의 확장된 형태로 병렬로 수행이 가능. (계층적 분류를 포함하고 있는 데이터 집계에 적합)

- CUBE 는 결합가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻는 장점이 있지만, 시스템에 부하를 많이 주는 단점이 있다. 

- GROUPING SETS 는 원하는 부분의 소계만 손쉽게 추출가능. 

- ROLLUP, CUBE, GROUPING SETS 사용시 정렬이 필요한 경우는 ORDER BY 절에 정렬 칼럼을 명시해야 한다 .

 

(WINDOW FUNCTIONS)

- 분석함수(ANALYTIC FUNCTION)나 순위함수(RANK FUCTION)로도 알려져 있음. 

 

그룹함수(GROUP FUNCTIONS )

1) ROLLUP 함수 

- GROUP BY 절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진 집합 결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행 한다.

- 인수가 계층구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에 주의해야함. 

예제1 ) SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
          FROM EMP, DEPT
          WHERE DEPT.DEPTNO = EMP.DEPTNO
          GROUP BY ROLLUP (DNAME, JOB)

DNAME과 JOB에 대한 소계가 계산됨 

예제2 ) GROUPING 함수 사용 

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

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

SELECT DNAME, GROUPING(DNAME), JOB, GROUPING(JOB), COUNT(*) "Total  Empl", SUM(SAL) "Total Sal"

FROM EMP,DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB); 

각 소계 계산 결과에 대해 1 표시 

+) 이외에도 CASE함수를 함께 사용하거나 GROUP BY절에서 ROLLUP함수를 특정 속성에만 사용하거나 여러 속성을 괄호로 묶어 하나의 집합으로 간주하는 등의 사용이 가능하다.

 

2) CUBE 함수  

- ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다. 

- CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한번의 Query를 추가 수행해야 한다. 

- ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 함. 

- ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다. 

 

예제1) CUBE 함수 이용 

SELECT

CASE GROUPING(DNAME)
  WHEN 1 THEN 'All Departmets'
  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 CUBE (DNAME, JOB);

 

결합가능한 모든 값에 대한 집계

3) GROUPING SETS 함수  

- 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다. 

- GROUP BY SQL 문장을 여러번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있게 됨. 

- GROUPING SETS(인수1, 인수2)는 (인수1 그룹핑 결과)  + (인수2 그룹핑 결과)를 합친(UNION ALL) 결과임.

 

예제 1) 

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 GROUPING SETS (DNAME, JOB);

GROUPING SETS 결과