1. 데이터 분석 개요
- AGGREGATE FUNCTION
- GROUP FUNCTION
- WINDOW FUNCTION
AGGREGATE FUNCTION
- GROUP FUNCTION의 한 부분으로 분류하며, COUNT, SUM, AVG, MAX. MIN 외 각종 집계 함수들이 포함
GROUP FUNCTION
- 그룹 함수
- 소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만들때 사용하는 함수
- 기존에 여러 쿼리를 UNION, UNION ALL로 복잡한 단계를 거쳐 만들던 구조를 단순화함
- 그룹 함수 사용으로 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성할 수 있게 되었다.
- 종류
- ROLLUP
- GROUP BY의 확장된 형태로 사용하기가 쉬우며, 병렬로 수행이 가능하기 때문에 매우 효과적일 뿐 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합
- CUBE
- 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻는 장점이 있는 반면에, 시스템에 부하를 많이 주는 단점이 있다.
- GROUPING SETS
- 원하는 부분의 소계만 손쉽게 추출할 수 있는 장점
- ROLLUP
WINDOW FUNCTION
- 분석 함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전한 기능
2. ROLLUP 함수
- ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다.
STEP1. 일반적인 GROUP BY절 사용
- Oracle을 포함한 일부 DBMS의 과거 버전에서는 GROUP BY 절 사용 시 자동적으로 정렬을 수행
- 현재 대부분의 DBMS 버전은 집계 기능만 지원하고 있으며 정렬 필요 시 ORDER BY 절을 명시적으로 표기해야함
SELECT A.DNAME
,B.JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY A.DNAME, B.JOB;
DNAME JOB Total Empl Total Sal
------ ------ ---------- ----------
SALES MANAGER 1 2850
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
ACCOUNTING CLERK 1 1300
SALES SALESMAN 4 5600
SALES CLERK 1 950
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
RESEARCH MANAGER 1 2975
STEP1-2. GROUP BY절 + ORDER BY절 사용
SELECT A.DNAME
,B.JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY A.DNAME, B.JOB
ORDER BY A.DNAME, B.JOB;
DNAME JOB Total Empl Total Sal
------ ------ ---------- ----------
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
RESEARCH MANAGER 1 2975
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
STEP2. ROLLUP 함수 사용
- 실행 결과에서 2개의 ROLLUP(DNAME, JOB)에 대하여 아래의 LEVEL 집계가 생성된 것을 볼 수 있다.
- L1 - GROUP BY 수행 시 생성되는 표준 집계(9건)
- L2 - DNAME별 모든 JOB의 SUBTOTAL(3건)
- L3 - GRAND TOTAL(마지막 행, 1건)
- ROLLUP의 경우 계층 간 집계에 대해서는 LEVEL 별 순서(L1->L2->L3)를 정렬하지만, 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않는다. L1, L2, L3 계층 내 정렬을 위해서는 별도의 ORDER BY 절을 사용해야함
SELECT A.DNAME
,B.JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY ROLLUP(A.DNAME, B.JOB);
DNAME JOB Total Empl Total Sal
------ ------ ---------- ----------
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES 6 9400 L2
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
RESEARCH 5 10875 L2
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING 3 8750 L2
14 29025 L3
STEP2-2. ROLLUP 함수 + ORDER BY 절 사용
SELECT A.DNAME
,B.JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY ROLLUP(A.DNAME, B.JOB)
ORDER BY A.DNAME, B.JOB;
DNAME JOB Total Empl Total Sal
------ ------ ---------- ----------
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING 3 8750
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
RESEARCH MANAGER 1 2975
RESEARCH 5 10875
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES 6 9400
14 29025
STEP3. GROUPING 함수 사용
- ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가되었다.
- ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시되고,
- 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시된다.
- 부서별, 업무별과 전체 집계를 표시한 레코드에서는 GROUPING 함수가 1을 리턴한 것을 확인
SELECT A.DNAME
,GROUPING(A.DNAME)
,B.JOB
,GROUPING(B.JOB)
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY ROLLUP(A.DNAME, B.JOB);
DNAME GROUPING(DNAME) JOB GROUPING(JOB) Total Empl Total Sal
------ --------------- ------ ------------- ---------- ----------
SALES 0 CLERK 0 1 950
SALES 0 MANAGER 0 1 2850
SALES 0 SALESMAN 0 4 5600
SALES 0 1 6 9400
RESEARCH 0 CLERK 0 2 1900
RESEARCH 0 ANALYST 0 2 6000
RESEARCH 0 MANAGER 0 1 2975
RESEARCH 0 1 5 10875
ACCOUNTING 0 CLERK 0 1 1300
ACCOUNTING 0 MANAGER 0 1 2450
ACCOUNTING 0 PRESIDENT 0 1 5000
ACCOUNTING 0 1 3 8750
1 1 14 29025
STEP4. GROUPING 함수 + CASE 사용
-- CASE 사용
SELECT CASE GROUPING(A.DNAME) WHEN 0 THEN A.DNAME ELSE 'All Departments' END AS DNAME
,CASE GROUPING(B.JOB) WHEN 0 THEN B.JOB ELSE 'All Jobs' END AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY ROLLUP(A.DNAME, B.JOB);
-- DECODE 사용 (Oracle)
SELECT DECODE(GROUPING(A.DNAME), 0, A.DNAME, 'All Departments') AS DNAME
,DECODE(GROUPING(B.JOB), 0, B.JOB, 'All Jobs') AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY ROLLUP(A.DNAME, B.JOB);
DNAME GROUPING(DNAME) JOB GROUPING(JOB) Total Empl Total Sal
------ --------------- ------ ------------- ---------- ----------
SALES 0 CLERK 0 1 950
SALES 0 MANAGER 0 1 2850
SALES 0 SALESMAN 0 4 5600
SALES 0 All Jobs 1 6 9400
RESEARCH 0 CLERK 0 2 1900
RESEARCH 0 ANALYST 0 2 6000
RESEARCH 0 MANAGER 0 1 2975
RESEARCH 0 All Jobs 1 5 10875
ACCOUNTING 0 CLERK 0 1 1300
ACCOUNTING 0 MANAGER 0 1 2450
ACCOUNTING 0 PRESIDENT 0 1 5000
ACCOUNTING 0 All Jobs 1 3 8750
All Departments 1 All Jobs 1 14 29025
STEP4-2. ROLLUP 함수 일부 사용
- 결과는 마지막 "ALL DEPARTMENTS & ALL JOBS" 줄만 계산이 되지 않았다.
SELECT CASE GROUPING(A.DNAME) WHEN 0 THEN A.DNAME ELSE 'All Departments' END AS DNAME
,CASE GROUPING(B.JOB) WHEN 0 THEN B.JOB ELSE 'All Jobs' END AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY A.DNAME, ROLLUP(B.JOB);
DNAME JOB Total Empl Total Sal
------ ------ ---------- ----------
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES All Jobs 6 9400
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
RESEARCH All Jobs 5 10875
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING All Jobs 3 8750
STEP4-3. ROLLUP 함수 결합 컬럼 사용
- ROLLUP 함수 사용 시 괄호로 묶은 JOB과 MGR의 경우 하나의 (JOB+MGR) 컬럼으로 간주하여 괄호 내 각 컬럼별 집계를 구하지 않는다.
SELECT A.DNAME
,B.JOB
,B.MGR
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY ROLLUP(A.DNAME, (B.JOB, B.MGR));
DNAME JOB MGR Total Sal
------ ------ ------- ----------
SALES CLERK 7698 950
SALES MANAGER 7839 2850
SALES SALESMAN 7698 5600
SALES 9400
RESEARCH CLERK 7788 1100
RESEARCH CLERK 7902 800
RESEARCH ANALYST 7566 6000
RESEARCH MANAGER 7839 2975
RESEARCH 10875
ACCOUNTING CLERK 7782 1300
ACCOUNTING MANAGER 7839 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
29025
3. CUBE 함수
- CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다.
- CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한번의 Query를 추가 수행해야 한다.
- 뿐만 아니라 Grand Total은 양쪽의 Query에서 모두 생성이 되므로 한 번의 Query에서는 제거되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많다.
- CUBE 함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다.
STEP5. CUBE 함수 이용
SELECT CASE GROUPING(A.DNAME) WHEN 0 THEN A.DNAME ELSE 'All Departments' END AS DNAME
,CASE GROUPING(B.JOB) WHEN 0 THEN B.JOB ELSE 'All Jobs' END AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY CUBE(A.DNAME, B.JOB);
DNAME JOB Total Empl Total Sal
------ ------ ---------- ----------
All Departments All Jobs 14 29025
All Departments CLERK 4 4150 - 모든 경우의 수
All Departments ANALYST 2 6000 - 모든 경우의 수
All Departments MANAGER 3 8275 - 모든 경우의 수
All Departments SALESMAN 4 5600 - 모든 경우의 수
All Departments PRESIDENT 1 5000 - 모든 경우의 수
SALES All Jobs 6 9400
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
RESEARCH All Jobs 5 10875
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
ACCOUNTING All Jobs 3 8750
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
STEP5-2. UNION ALL 사용 SQL
- CUBE 사용시 동일한 결과
- UNION ALL 사용 시 DEPT와 EMP 테이블을 네 번이나 반복 액세스하는 부분을 CUBE 사용으로 SQL 한번으로 줄일 수 있는 부분
SELECT A.DNAME
,B.JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY A.DNAME, B.JOB
UNION ALL
SELECT A.DNAME
,'All Jobs' AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY A.DNAME
UNION ALL
SELECT 'All Departments' AS DNAME
,B.JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY B.JOB
UNION ALL
SELECT 'All Departments' AS DNAME
,'All Jobs' AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
;
4. GROUPING SETS 함수
-- Grouping Sets 결과
SELECT DECODE(GROUPING(A.DNAME), 0, A.DNAME, 'All Departments') AS DNAME
,DECODE(GROUPING(B.JOB), 0, B.JOB, 'All Jobs') AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY GROUPING SETS(A.DNAME, B.JOB);
-- Union All을 사용하여 Grouping Sets 결과와 동일
SELECT A.DNAME
,'All Jobs' AS JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY A.DNAME
UNION ALL
SELECT 'All Departments' AS DNAME
,B.JOB
,COUNT(*) "Total Empl"
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY B.JOB;
-- 두 SQL의 정렬이 다를 수 있다.
DNAME JOB Total Empl Total Sal
------ --------- ---------- ---------
All Departments ANALYST 2 6000
All Departments CLERK 4 4150
All Departments SALESMAN 4 5600
All Departments MANAGER 3 8275
All Departments PRESIDENT 1 5000
SALES All Jobs 6 9400
ACCOUNTING All Jobs 3 8750
RESEARCH All Jobs 5 10875
3개의 인수 사용
SELECT A.DNAME
,B.JOB
,B.MGR
,SUM(SAL) "Total Sal"
FROM DEPT A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO)
GROUP BY GROUPING SETS((A.DNAME, B.JOB, B.MGR), (A.DNAME, B.JOB), (B.JOB, B.MGR));
SALES CLERK 7698 950
SALES MANAGER 7839 2850
SALES SALESMAN 7698 5600
RESEARCH CLERK 7788 1100
RESEARCH CLERK 7902 800
RESEARCH ANALYST 7566 6000
RESEARCH MANAGER 7839 2975
ACCOUNTING CLERK 7782 1300
ACCOUNTING MANAGER 7839 2450
ACCOUNTING PRESIDENT 5000
CLERK 7902 800
CLERK 7782 1300
CLERK 7788 1100
CLERK 7698 950
SALESMAN 7698 5600
ANALYST 7566 6000
MANAGER 7839 8275
PRESIDENT 5000
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000반응형
'SQLD > SQL 전문가 가이드' 카테고리의 다른 글
| 2.2.4 제4절 서브쿼리 (0) | 2025.05.27 |
|---|---|
| 2.2.3 제3절 계층형 질의와 셀프 조인 (6) | 2025.05.26 |
| 2.2.2 제2절 집합 연산자(set operator) (0) | 2025.05.26 |
| 2.1.4 제4절 TCL (0) | 2024.11.05 |
| 2.1.3 제3절 DML (0) | 2024.11.04 |
