[SQLD 학습 자료 요약] SQL 기본 및 활용 2.5. 그룹 함수

본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.

SQL 전문가 가이드 국내도서 저자 : 한국데이터산업진흥원 출판 : 한국데이터산업진흥원 2020.05.29 상세보기

 

5. 그룹 함수

1. 데이터 분석 개요

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

 

1. AGGREGATE FUNCTION

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

 

2. GROUP FUNCTION

• ROLLUP 함수
  • 소그룹 간의 소계를 계산
  • 사용하기 쉽고 병렬로 수행이 가능하기 때문에 매우 효과적일 뿐 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터 집계에 적합
• CUBE 함수
  • GROUP BY 항목들 간 다차원적인 소계를 계산
  • 결합 가능한 모든 값에 대하여 다차원적 집계를 생성하지만, 시스템에 부하를 많이 준다.
• GROUPING SETS 함수
  • 특정 항목에 대한 소계를 계산
  • 원하는 부분의 소계만 손쉽게 추출할 수 있다.
• 그룹 함수는 집계된 레코드에서 집계 대상 컬럼 이외의 GROUP 대상 컬럼의 값은 NULL을 반환한다.
• 그룹 함수 모두 일반 그룹 함수로 동일한 결과를 추출할 수 있다.

 

3. WINDOW FUNCTION

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

 

---

2. ROLLUP 함수

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

 

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

ROLLUP 함수 실행 결과

 

 

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

• L1 - GROUP BY 수행시 생성되는 표준 집계 (DNAME, JOB 모두 null이 아닌 행, 9 건)
• L2 - DNAME 별 모든 JOB 의 SUBTOTAL (JOB = null인 행, 3 건)
• L3 - GRAND TOTAL (마지막 행, 1 건)

 

추가로 ROLLUP 의 경우 계층 간 집계에 대해서는 LEVEL 별 순서(L1→L2→L3)를 정렬하지만, 계층 내 GROUP BY 수행시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않는다. L1, L2, L3 계층 내 정렬을 위해서는 별도의 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;

ROLLUP 함수 적용 결과를 DNAME, JOB에 따라 정렬한 결과

 

 

ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 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)
ORDER BY DNAME, JOB;

빨간 박스의 좌측이 GROUPING(DNAME), 우측이 GROUPING(JOB) 컬럼

 

 

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

Subtotal, Total Level 집계 결과를 All Departments, All Jobs 문구로 대체

 

부서별과 전체 집계를 표시한 레코드에서 ‘ALL JOBS’와 ‘ALL DEPARTMENTS’라는 사용자 정의 텍스트를 확인할 수 있다.

 

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

ROLLUP 함수의 인수로 JOB 컬럼만 사용한 결과

 

ROLLUP 함수를 JOB에만 사용하면 마지막 ALL DEPARTMENTS & ALL JOBS 줄만 계산이 되지 않는다. DNAME에 대한 집계는 필요하지 않기 때문이다.

 

ROLLUP 함수에 컬럼을 괄호로 결합하여 사용하면 하나의 집합 컬럼으로 간주하여 괄호 내 각 컬럼별 집계를 구하지 않는다.

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

DNAME과 (JOB, MGR) 2가지 조합에 대한 집계 결과 반환

 

 

---

3. CUBE 함수

CUBE 를 사용할 경우에는 내부적으로는 Grouping Columns 의 순서를 바꾸어서 또 한 번의 Query 를 추가 수행해야 한다. 뿐만 아니라 Grand Total 은 양쪽의 Query 에서 모두 생성이 되므로 한 번의 Query 에서는 제거되어야만 하므로 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(*)                                                             "TotalEmpl",
       SUM(SAL)                                                             "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB);

CUBE 함수 실행 결과 (ROLLUP 함수에 비해 결과가 5건 많아졌다.)

 

 

모든 경우의 수에 대하여 Subtotal 을 생성하므로 GROUPING COLUMNS 의 수가 N 이라고 가정하면, 2 의 N 승 LEVEL 의 Subtotal 을 생성하게 된다. 실행 결과에서 CUBE 함수 사용으로 ROLLUP 함수의 결과에다 업무별 집계까지 추가해서 출력할 수 있는데, ROLLUP 함수에 비해 업무별 집계를 표시한 5 건의 레코드가 추가된 것을 확인할 수 있다.

 

UNION ALL을 사용하여 표현하면 아래와 같다.

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 문장도 더 짧아졌으므로 가독성도 높아졌다.

 

CUBE 함수 결과를 GROUPING SETS 함수를 통해 동일하게 만들 수 있다.

-- CUBE 활용
SELECT DNAME, JOB, SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB)
ORDER BY DNAME, JOB;

-- GROUPING SETS 활용
SELECT DNAME, JOB, SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS ((DNAME, JOB), (DNAME), (JOB), ())  -- 가능한 경우의 수 2*2
ORDER BY DNAME, JOB;

 

---

 

4. GROUPING SETS 함수

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

 

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 함수 실행 결과

 

 

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 함수 실행 결과 (2)

 

실행 결과에서 첫 번째 10 건의 데이터는 (DNAME+JOB+MGR) 기준의 집계이며, 두 번째 8 건의 데이터는 (JOB+MGR) 기준의 집계이며, 세 번째 9 건의 데이터는 (DNAME+JOB) 기준의 집계이다.

 

---

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

1.1. 데이터 모델링의 이해

1.2. 엔터티

1.3. 속성

1.4. 관계

1.5. 식별자

 

Part 2. 데이터 모델과 성능

2.1. 성능 데이터 모델링 개요

2.2. 정규화와 성능

2.3. 반정규화와 성능

2.4. 대량 데이터에 따른 성능

2.5. 데이터베이스 구조와 성능

2.6. 분산 데이터베이스와 성능

 

2장. SQL 기본 및 활용

Part 1. SQL 기본

1.1. 관계형 데이터베이스 개요

1.2. DDL

1.3. DML

1.4. TCL

1.5. WHERE 절

1.6. 함수 (FUNCTION)

1.7. GROUP BY, HAVING 절

1.8. ORDER BY 절

1.9. 조인 (JOIN)

 

Part 2. SQL 활용

2.1. 표준 조인

2.2. 집합 연산자

2.3. 계층형 질의와 셀프 조인

2.4. 서브쿼리

2.5. 그룹 함수

2.6. 윈도우 함수

2.7. DCL

2.8. 절차형 SQL

 

Part 3. SQL 최적화 기본원리

3.1. 옵티마이저와 실행계획

3.2. 인덱스 기본

3.3. 조인 수행 원리

 

따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.