[SQLD] #025 SQL 활용 - 그룹 함수

목차 

1. 데이터 분석 개요

2. ROLLUP 함수

3. CUBE 함수

4. GROUPING SETS 함수

 

 

 

---

 

 

 

1. 데이터 분석 개요

ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음 세가지 함수를 정의하고 있다. 

 

 

AGGREGATE FUNCTION 

GROUP AGGREGATE FUNCTION 이라고도 부르며, GROUP FUNCTION의 한 부분으로 분류할 수 있다. COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들이 포함되어있다. 

 

 

 

GROUP FUNCTION

여러 레벨의 결산 보고서를 만들 때 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러번 읽어 다시 재정렬하는 복잡한 단계를 거쳐야 했다. 그러나 그룹함수를 사용하면 하나의 SQL로 테이블을 한번만 읽어서 빠르게 레포트를 작성할 수 있다. 

 

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

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

- GROUPING SETS는 원하는 부분의 소계만 손쉽게 추출할 수 윘다. 

 

 

 

WINDOW FUNCTION

분석 함수나 순위 함수로도 알려져있는 윈도우 함수는 Data Warehouse에서 발전한 기능이다. 

 

 

 

 

 

 

 

 

 

 

 

2. ROLLUP 함수

ROLLUP 에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 level의 Subtotal이 생성된다. 중요한 것은, ROLLUP의 인수는 계층구조이므로 인수 순서가 바뀌면 수행 결과도 바뀐다. 

ROLLUP과 CUBE의 효과는 다음 예시에서 확인할 수 있다. 

 

 

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

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

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

 

 

 

 

 

STEP 1-2. GROUP BY 절 + ORDER BY 절 사용

예제) 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ORDER BY 절을 사용함으로써 부서, 업무별로 정렬이 이뤄진다. 

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT 
WHERE DEPT.DEPTNO = EMP.DEPTNO 
GROUP BY DNAME, JOB
ORDER BY DNAME, JOB;

 

 

 

 

STEP 2. ROLLUP 함수 사용

예제) 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용한다. 

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

실행 결과 2개의 GROUPING COLUMNS (DNAME, JOB) 에 대하여 다음과 같은 추가 LEVEL의 집계가 생성된 것을 볼 수 있다.

 

L1 - GROUP BY 수행시 생성되는 표준 집계

L2 - DNAME별 모든 JOB의 SUBTOTAL

L3 - GRAND TOTAL

 

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

 

 

 

 

 

 

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

예제) 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용하고, 추가로 ORDER BY 절을 사용해서 부서, 업무별로 정렬한다. 

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

 

 

 

 

 

 

STEP 3. GROUPING 함수 사용

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

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

 

예제) ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가된 SQL 문장이다. 

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);

 

 

 

 

 

STEP 4. GROUPING 함수 + CASE 사용

예제) ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수와 CASE 함수를 함께 사용한 SQL 문장을 작성한다.

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);

 

 

 

 

 

 

STEP 4-2. ROLLUP 함수 일부 사용

예제) GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY DNAME, ROLLUP(JOB) 조건으로 변경한 경우

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);

 

 

 

 

STEP 4-3. ROLLUP 함수 결합 칼럼 사용

예제) JOB과 MGR은 하나의 집합으로 간주하고, 부서별, JOB & MGR에 대한 ROLLUP 결과를 출력한다.

SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal" 
FROM EMP, DEPT 
WHERE DEPT.DEPTNO = EMP.DEPTNO 
GROUP BY ROLLUP (DNAME, (JOB, MGR));

ROLLUP 함수 사용시 괄호로 묶은 JOB과 MGR의 경우 하나의 집합(JOB+MGR) 칼럼으로 간주하여 괄호 내 각 칼럼별 집계를 구하지 않는다. 

 

 

 

 

 

 

 

 

 

3. CUBE 함수

ROLLUP 에서는 단지 가능한 Subtotal 만 생성하였지만, CUBE는 결합 가능한 모든 값에 대해 다차원 집계를 생성한다. CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꿔서 또 한번의 쿼리를 추가로 수행해야한다. 뿐만 아니라 Grand Total은 양쪽의 쿼리에서 모두 생성되므로, 한번의 쿼리에서는 제거해야 한다. 

이처럼 Grouping Columns이 가질 수 있는 모든 경우에 대해 Subtotal을 생성해야 하는 경우에는 CUBE 함수를 쓰는게 낫지만, ROLLUP에 비해 시스템에 많은 부담을 주기 때문에 사용에 주의해야한다. 

CUBE함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있고, 이 때 표시된 인수들 간에는 계층 구조인 ROLLUP 과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우에도 데이터의 결과는 같다. 그리고 CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시되어야 한다. 

 

 

 

 

STEP 5. CUBE 함수 이용

예제) GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY CUBE (DNAME, JOB) 조건으로 변경해서 수행한다. 

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);

CUBE는 GROUPING COLUMNS이 가질 수 있는 모든 경우의 수에 대해 Subtotal을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면, 2의 N승 레벨의 Subtotal을 생성하게 된다. 

 

 

 

 

 

 

STEP 5-2 . UNION ALL 사용 SQL

UNION ALL은 Set Operation 내용으로, 여러 SQL문장을 연결하는 역할을 한다. 위 SQL은 첫번째 SQL 모듈부터 차례대로 결과가 나오므로, 위 CUBE SQL과 결과 데이터는 같으나 행들의 정렬은 다를 수 있다. 

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 사용 전 SQL에서 EMP, DEPT 테이블을 네 번이나 반복 액세스 하는 부분을 CUBE 사용 SQL에서는 한번으로 줄일 수 있다는 것이다. 기존의 같은 테이블을 네번 액세스 하는 이유가 되었던 부서와 업무별 소계와 총계 부분을 CUBE 함수를 사용함으로써 한번의 액세스만으로 구현한다. 결과적으로 수행속도 및 자원 사용율을 개선할 수 윘으며, SQL문장도 더 짧아졌으므로 가독성도 높아진다. 

 

 

 

 

 

 

 

 

 

 

 

4. GROUPING SETS 함수

GROUPING SETS를 이용해 더욱 다양한 소계 집합을 만들 수 있는데, GROUP BY SQL 문장을 여러번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있게 되었다. GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이 때 표시된 인수들 간에는 계층 구조인 ROLLUP 과 달리 평등한 관계이므로, 인수의 순서가 바껴도 결과는 같다. 

그리고 GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다. 

 

 

 

일반 그룹함수를 이용한 SQL

예제) 일반 그룹함수를 이용하여 부서별, JOB별 인원수와 급여 합을 구하라.

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', DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB;

실행 결과는 별도의 ORDER BY 조건을 명시하지 않았기 때문에 DNAME이나 JOB에 대해 정렬이 되어있지 않다. 

 

 

 

 

 

GROUPING SETS 사용 SQL

예제) 일반 그룹함수를 GROUPING SETS 함수로 변경하여 부서별, JOB별 인원수와 급여 합을 구하라. 

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);

 

 

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.