그룹 함수 (1)

 

ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음 세 가지 함수를 제공한다.

 

1) AGGREGATE FUNCTION

 

 GROUP AGGREGATE function이라고도 부르며 count, sum, avg, max, min 등등 의 각종 집계 함수 등이 포함된다.

chinggin.tistory.com/489

집계 함수 (Aggregate Function)

 

 

2) GROUP FUNCTION

 

결산 개념의 업무를 가지는 원가나 판마 시스템 경우는 소계, 중계, 합계, 총합계 등 여러 레벨의 결산 보고서를 만드는 것이 중요 업무 중 하나라고 볼 수 있다.

 

개발자들이 이러한 보고서를 위한 데이터를 준비하기 위해서는 SQL이 포함된 배치 프로그램을 작성하거나, 레벨별 집계를 위한 여러 단계의 SQL문을 UNION, UNION ALL로 결과를 묶은 후 하나의 테이블을 여러 번 읽어 다시 재 정렬하는 복잡한 단계를 거쳐야 한다.

그러나 그룹함수를 사용하면 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성할 수 있다.

 

추가로 소계/합계를 표시하기 위해 Grouping 함수와 Case 함수를 이용하면 쉽게 원하는 포맷의 데이터 보고서를 작성할 수도 있다.

 

 

그룹 함수 소개

 

 

1. ROLLUP 

 - 소 그룹간의 소계를 계산하는 함수이다.

ROLLUP 함수는 GROUP BY의 확장된 형태로  사용하기가 쉬우며 병렬로 수행이 가능하기 때문에 매우 효과적일 뿐만 아니라, 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 접합하도록 되어 있다. 

 

이 롤업함수에 지정된 그룹핑 칼럼들은 소계를 생성하기 위해 사용된다. 이 GROUPING COLUMNS의 수를 N이라고 했을 때 N+1 LEVEL의 소계가 생성된다.

 

롤업의 인수는 계층 구조이기 때문에 , 그룹핑 칼럼들의 순서가 변경되면 수행 결과도 변경되기 때문에 인수의 순서에 주의를 기울여야 한다.

 

추가로 ROLLUP의 경우 계층 간 집계에 대해서는 LEVEL별 순서를 정렬하지만, 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않는다. ORDER BY 절을 별도로 사용하여 정렬해야 한다.

 

예제로 GROUP BY를 사용한 SQL문과 그 결과와 ROLLUP 함수를 사용한 SQL문과 그 결과를 보자.

부서별 직업 단위로 인원수와 연봉의 합계를 구하려고 한다.

(귀찮아서 JOIN 안 쓰고 , 로 연결했어요)

 

 

  ㄱ. GROUP BY를 사용한 SQL문

--GROUP BY절과 ORDER BY절사용
SELECT DNAME,JOB, COUNT(*) "Total Empl",SUM(SAL) "Total SAL" FROM EMP e ,DEPT d WHERE e.DEPTNO  = d.DEPTNO
GROUP BY DNAME,JOB
ORDER BY DNAME,JOB;

 ㄴ. 각 그룹핑된 칼럼 별 소계를 추가하기 위해 ROLLUP 함수를 사용해 보자.

--ROLLUP 함수 사용
SELECT DNAME,JOB, COUNT(*) "Total Empl",SUM(SAL) FROM EMP e ,DEPT d WHERE e.DEPTNO  = d.DEPTNO
GROUP BY ROLLUP(DNAME,JOB);

 

각 그룹핑 단위 레벨당 소계와  총합계까지 출력되는 걸 확인할 수 있다.  근데 NULL값이 거슬리지 않는가?

 

GROUPING 함수를 이용해서 필드에 원하는 문자열을 넣을 수 있다.

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

CUBE, ROLLUP에 의한 소계가 계산된 결과행에는 Grouping(EXPR)=1 이 성립하고 그 외 결과는 Grouping(EXPR)=0이 표시된다.

 

이 그룹핑 함수와 CASE/DECODE를 이용해 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있어서 보고서 작성 시 유용하게 사용할 수 있다. 즉 집계 레코드를 구분하기 쉽다는 장점이다.

 

  ㄷ. 그룹핑 함수를 사용한 ROLLUP 함수

 

--GROUPING 함수가 추가된 ROLLUP 함수
SELECT DNAME,GROUPING(DNAME),JOB,GROUPING(JOB), COUNT(*) "Total Empl",SUM(SAL) FROM EMP e ,DEPT d WHERE e.DEPTNO  = d.DEPTNO
GROUP BY ROLLUP(DNAME,JOB);

결과를 보면 JOB기준으로 소계가 작성되었을 때 GROUPING(JOB) 칼럼에 1을 확인할 수 있고 마지막 소계에는 (DNAME, JOB) 전부 1이 들어가 있는 것을 확인할 수 있다.

 

세부적인 NULL값을 CASE문과  오라클인 경우 DECODE를 사용해 다른 문자로 치환해보자.

--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 EMP.DEPTNO =DEPT.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 EMP.DEPTNO =DEPT.DEPTNO
GROUP BY ROLLUP(DNAME,JOB);

 

 

추가 (ROLLUP 함수 일부만 사용 & 결합 칼럼 사용)

 

--ROLLUP 함수 일부 사용
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 EMP.DEPTNO =DEPT.DEPTNO
GROUP BY DNAME, ROLLUP(JOB);


--ROLLUP 함수 결합 컬럼 사용
--하나의 집합(JOB+MGR)으로 간주하여 괄호 내 각 칼럼별 집계를 구하지 않는다.
SELECT
DECODE(GROUPING(DNAME),1,'All Departments',DNAME) AS DNAME,
DECODE(GROUPING(JOB),1,'All Jobs',JOB) AS JOB,
DECODE(GROUPING(MGR),1,'All MGRS',MGR) AS MGR ,
SUM(SAL)
FROM EMP , DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO 
GROUP BY ROLLUP(DNAME,(JOB,MGR));

총 소계는필요없고 job만 소계한다. 

 

 

2. CUBE

 - GROUP BY 항목들 간 다차원적인 소계를 계산할 수 있는 함수이다.

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

 

롤업에서는 단지 가능한 소계만을 생성하였지만, cube는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다고 했다.

큐브 함수를 사용할 경우에는 내부적으로는 그룹핑 칼럼의 순서를 바꾸어서 또 한 번의 쿼리를 추가 수행해야 한다.

뿐만 아니라 총계는 양쪽 쿼리에서 모두 생성이 되기 때문에  결과 쿼리에서는 총계를 제거하기 위해서 추가적인 시스템의 연산이 일어난다.

 

이처럼 Grouping Columns들이 가질 수 있는 모든 경우에 대하여 소계를 생성해야 하는 경우에는 큐브 함수를 사용하는 것이 바람직하나  롤업에 비해 시스템에 많은 부담을 주기 때문에 사용에는 주의해야 한다.

 

큐브 함수는 롤업 함수와 달리 계층 구조 아니라 평등한 관계이기 때문에 인수의 순서가 바뀌는 경우 행간의 정렬순서는 바뀔 수 있어도 데이터의 결과는 같다. 

 

레벨별 소계의 개수는 그룹핑 칼럼의 수가 N이라고 가정하면 2의 N승만큼의 레벨 소계를 생성하게 된다.

 

큐브 함수를 대신 UNION ALL을 사용해보자. UNION ALL은 SET OPERATION 내용으로, 여러 SQL 문장을 연결하는 역할을 할 수 있다. CUBE의 함수와 결과는 같으나...

 

--4가지의 UNION ALL 

SELECT DNAME, JOB, COUNT(*) "Total Empl",SUM(SAL) "Total Sal"FROM EMP,DEPT WHERE EMP.DEPTNO =DEPT.DEPTNO GROUP BY DNAME,JOB
UNION ALL 
SELECT DNAME, 'ALL JOBS', COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP,DEPT WHERE EMP.DEPTNO =DEPT.DEPTNO GROUP BY DNAME  
UNION ALL 
SELECT 'All Department',JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP,DEPT WHERE EMP.DEPTNO =DEPT.DEPTNO GROUP BY JOB
UNION ALL 
SELECT 'All Department','ALL JOBS', COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP,DEPT WHERE EMP.DEPTNO =DEPT.DEPTNO;

 

롤업과 동일한 예제로 CUBE함수를 사용해보자.

--CUDE 함수이용
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 EMP.DEPTNO =DEPT.DEPTNO
GROUP BY CUBE(DNAME,JOB);

 

 

3. GROUPING SETS

 - 특정 항목에 대한 소계를 계산하는 함수이다.

 원하는 부분의 소계만 손쉽게 추출할 수 있는 장점이 있다.

 

GROUPING SETS는 그룹 쿼리이긴 하지만 UNION ALL 개념이 섞여 있다.

 

예를 들어 GROUPING SETS (EXPR1, EXPR2, EXPR3)을 연산 시 GROUPY BY EXPR1 UNION ALL GROUPT BY EXPR2... 과 같다.

 

grouping sets함수를 이용해 더욱 다양한 소계 집합을 만들 수 있는데, GROUP BY SQL 문장을 여러 번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있다.

이 경우 괄호로 묶은 집합 별로 집계를 구할 수 있으며, 괄호 내에는 계층 구조가 아닌 하나의 데이터로 간주한다.

 

빠르게 집합 별로 집계만을 얻을 수 있을 때 사용한다.

--GROUPING SETS 사용

SELECT 
DECODE(GROUPING(DNAME),1,'All Department',DNAME) AS DNAME,
DECODE(GROUPING(JOB),1,'All Jobs',JOB) AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total SAL"
FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO
GROUP BY GROUPING SETS (DNAME,JOB);

 

 

3) WINDOW FUNCTION

 - 분석함수 (ANALYTIC FUNCTION)이나  순위 함수 (RANK FUNCTION)로도 알려져 있는 윈도 함수는 데이터웨어하우스에서 발전한 기능이다.

 

윈도 함수는 다음에 정리해보도록 한다.