[SQL활용] 그룹 함수

1. 데이터 분석 개요

 

1) ANSI/ISO SQL 표준에서 정의하는 데이터 분석 함수

1-1)AGGREGATE FUNCTION

-GROUP AGGREGATE FUNCTION이라고도 부르며, GROUP FUNCTION의 한 부분으로 분류할 수 있음.

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

 

2) GROUP FUNCTION

2-1) 소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만들거나 통계 데이터를 뽑을 때 레베별 집계를 위한 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러번 다시 읽어 다시 재 정렬하는 복잡한 단계를 거쳤음.

2-2) 그룹 함수를 사용하면 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 데이터를 뽑을 수 있음.

2-3) 소계/합계를 표시하기 위해 GROUPING 함수와 CASE 함수를 이용하면 쉽게 원하는 포맷의 데이터를 뽑을 수 있음.

2-4) 그룹 함수로는 집계 함수를 제외하고

2-4-1) ROULLUP 함수 : 

- 소그룹 간의 소계를 계산

- GROUP BY의 확장된 형태로 사용하기가 쉬우며 병렬로 수행이 가능, 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합

2-4-2) CUBE 함수 : 

- .GROUP BY 항목들 간 다차원적인 소계를 계산

- 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 ROJLLUP에 비해 다양한 데이터를 얻는 장점

- 시스템에 부하를 많이 주는 단점

2-4-3) GROUPING SETS 함수 : 

- 특정 항목에 대한 소계를 계산

- 원하는 부분의 소계만 손쉽게 추출

 

3) WINDOW FUNCTION

분석 함수나 순위 함수로 알려져 있으며 데이터웨어하우스에서 발전한 기능

2. ROLLUP 함수

 

1) Grouping Coumns의 List 

- SubTotal을 생성하기 위해 사용

- Grouping Columns의 수를 n이라고 하면 n+1 Level의 SubTotal이 생성

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

 

STEP1. 일반적인 GROUP BY 절 사용

 

SELECT

DNAME

,JOB

,COUNT(*) AS TOTAL_EMPL

,SUM(SAL) AS TOTAL_SAL

FROM EMP E, DEPT DP

WHERE E.DEPTNO = DP.DEPTNO

GROUP BY DNAME, JOB

▲ Oracle을 포함한 일부 DBMS의 과거 버전에서는 GROUP BY 절 사용시 자동적으로 정렬을 수행 > 현재 대부분의 DBMS 버전은 집계 기능만 지원하고 있으며 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 컬럼이 표시되어야 함.

결과)

 

STEP1-1. GROUP BY 절 + ORDER BY 절 사용

 

SELECT

DNAME

,JOB

,COUNT(*) AS TOTAL_EMPL

,SUM(SAL) AS TOTAL_SAL

FROM EMP E, DEPT DP

WHERE E.DEPTNO = DP.DEPTNO

GROUP BY DNAME, JOB

ORDER BY DNAME, JOB

STEP2. ROLLUP 함수 사용

 

--ORCLE

SELECT

DNAME

,JOB

,COUNT(*) AS TOTAL_EMPL

,SUM(SAL) AS TOTLA_SAL

FROM EMP E, DEPT DP

WHERE E.DEPTNO = DP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB)

 

--MYSQL

SELECT     DNAME     ,JOB     ,COUNT(*) AS TOTAL_EMPL     ,SUM(SAL) AS TOTLA_SAL FROM EMP E, DEPT DP WHERE E.DEPTNO = DP.DEPTNO GROUP BY DNAME, JOB WITH ROLLUP

결과) MYSQL 쿼리문으로 돌린 결과

▲실행결과를 살펴보면 그룹핑 컬럼이 DNAME, JOB 에 대해 추가 LEVEL의 집계가 생성된 것을 볼 수 있음.

L1 - GROUP BY 수행시 생성되는 표준 집계 (9건)
L2 - DNAME 별 모든 JOB의 SUBTOTAL(3건)L3 - GRAND TOTAL(마지막 행 1건)

 

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

계층 내 정렬을 하고 싶으면 별도의 ORDER BY 절을 사용해야 됨.

 

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

--ORACLE

SELECT

DNAME

,JOB

,COUNT(*) AS TOTAL_EMPL

,SUM(SAL) AS TOTLA_SAL

FROM EMP E, DEPT DP

WHERE E.DEPTNO = DP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB) ORDER BY DANEM, JOB;

 

--MYSQL

SELECT     DNAME     ,JOB     ,COUNT(*) AS TOTAL_EMPL     ,SUM(SAL) AS TOTLA_SAL FROM EMP E, DEPT DP WHERE E.DEPTNO = DP.DEPTNO GROUP BY DNAME, JOB DESC WITH ROLLUP

STEP 3. GROUPING 함수 사용

ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가 됨.

- ROLLUP이나 CUBE에 의한 소계가 계산된 결과 : GROUPING(EXPR) = 1

- 그 외의 결과 : GROUPING(EXPR) = 0

- GROUPING 함수와 CASE/DECODE를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정 할 수 있음.

--ORACLE

SELECT

DNAME

,GROUPING(DNAME)

,JOB

,GROUPING(JOB)

,COUNT(*) AS TOTAL_EMPL

,SUM(SAL) AS TOTLA_SAL

FROM EMP E, DEPT DP

WHERE E.DEPTNO = DP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB) ORDER BY DANEM, JOB;

결과)

 

▲실행결과를 살펴보면 그룹핑 함수 파라미터 컬럼에 대해 1을 리턴한 것을 확인할 수 있음.

마지막 합계의 경우 DNAME, JOB 둘 다 1이기 때문에 1이 한개인 경우는 소계, 1이 두개인 경우는 합계로 볼 수 있음.

 

STEP 4. GROUPING 함수 + 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 함수를 사용해서 좀더 짧게 표현할 수 있음.

-- 하지만 속도를 생각하면 CASE 문을 쓰는게 좋음.

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-2. ROLLUP 함수 일부 사용

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 DNAME, ROLLUP(JOB)

결과)

▲실행결과를 살펴보면 집계는 JOB만 사용했기 때문에 합계는 나타나지 않음.

 

3. CUBE 함수

 

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

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

-Grand Total은 양쪽의 Query 에서 모두 생성이 되고 한 번의 Query에서는 제거되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많음.

-Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직

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

-CUBE 함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있음.

-표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같음

-CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 함.

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

결과)

▲실행결과를 살펴보면 GROUPING COLUMNS이 가질 수 있는 모든 경우의 수에 대하여 소계를 생성함.

ROLLUP함수 결과에서 업무별 집계를 표시한 5건의 레코드가 추가된 것을 확인할 수 있음.

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB 

UNION ALL

SELECT DNAME, 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME 

UNION ALL

SELECT 'All Departments', JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY JOB 

UNION ALL

SELECT 'All Departments', 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

결과)

▲실행결과를 살펴보면 CUBE 사용했을때와 결과 데이터는 같지만 행들의 정렬이 다름.

CUBE 함수를 사용하면 UNIONALL을 쓰면서 네 번이나 반복 액세스 하는 부분을 한 번으로 줄일 수 있음.속도도 CUBE가 훨씬 빠름.

4.GROUPING SETS 함수

 

- GROUP BY SQL 문장을 여러번 반복하지 않고 소계 집합을 만들 수 있음.

- ROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있음

- 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같음. 

- 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 함.

--일반 그룹함수를 이용한 SQL SELECT  	DNAME 	, 'All Jobs' JOB 	, COUNT(*) "Total Empl" 	, SUM(SAL) "Total Sal"  FROM EMP, DEPT  WHERE DEPT.DEPTNO = EMP.DEPTNO  GROUP BY DNAME  UNION ALL  SELECT  	'All Departments' DNAME 	, JOB 	, COUNT(*) "Total Empl" 	, SUM(SAL) "Total Sal"  FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY JOB ;

결과)

 

--GROUPING SETS을 이용한 SQL 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);

결과)

 

SELECT 

DNAME

, JOB

, MGR

, SUM(SAL) "Total Sal" 

FROM EMP, DEPT 

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR)); 

--GROUPING SETS 함수 사용시 괄호로 묶은 집합별로(괄호 내는 계층구조가 아닌 하나의 데이터로 간주함) 집계를 구할 수 있다.

결과)

▲실행결과를 살펴보면 첫 번째 10건의 데이터는 (DNAME+JOB+MGR)에 대한 집계

두번째 8건의 데이터는 (JOB + MGR) 기준의 집계세번째 9건의 데이터는 (DNAME + JOB) 기준의 집계