SQL 전문가 가이드 (2013년)
1. 데이터 분석 개요
ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음 세 가지 함수를 정의하고 있다.
- AGGREGATE FUNCTION : COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수 (동의어 : GROUP AGGREGATE FUNCTION)
- GROUP FUNCTION : ROLLUP , CUBE, GROUPING SETS (소계 , 중계, 총합계, 등 여러 레벨로 사용할때)
- WINDOW FUNCTION : RANK, DENSE_RANK ( 분석 함수(ANALYTIC FUNCTION), 순위 함수(RANK FUNCTION) )
2. ROLLUP 함수.
Useful Information
- ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다.
- 중요한 것은, ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의해야 한다.
- 일반 Group By 와 Rollup 사용 시 차이점.
SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB;
DNAME JOB Total Empl Total Sal
-------------- --------- ---------- ----------
SALES MANAGER 1 2850
SALES CLERK 1 950
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING CLERK 1 1300
SALES SALESMAN 4 5600
RESEARCH MANAGER 1 2975
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
9 rows selected.
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;
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
9 rows selected.
SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
DNAME JOB Total Empl Total Sal
-------------- --------- ---------- ----------
SALES CLERK 1 950 Level 1
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES 6 9400 > Level 2
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975 > Level 2
RESEARCH 5 10875
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING 3 8750 > Level 2
14 29025 > Level 3
13 rows selected.
* 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 절을 사용해야 한다
3. GROUPING 함수.
Useful Information
- ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가되었다.
- ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고, - 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시된다.
- GROUPING 함수와 CASE/DECODE를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있어, 보고서 작성시 유용하게 사용할 수 있다.
STEP 3. GROUPING 함수 사용
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);
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
13 rows selected.
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);
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
All Departments All Jobs 14 29025
13 rows selected.
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);
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
All Departments All Jobs 14 29025
13 rows selected.
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)
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
12 rows selected.
결과는 마지막 ALL DEPARTMENTS & ALL JOBS 줄만 계산이 되지 않았다.
ROLLUP이 JOB 칼럼에만 사용되었기 때문에 DNAME에 대한 집계는 필요하지 않기 때문이다.
STEP 4-3. 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 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
14 rows selected.
4.CUBE 함수
Useful Information
- ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다. CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행해야 한다. 뿐만 아니라 Grand Total은 양쪽의 Query 에서 모두 생성이 되므로 한 번의 Query에서는 제거되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많다.
- 이처럼 Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다. CUBE 함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다. 그리고 CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.
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) ;
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
18 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4232949899
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 756 | 8 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 756 | 8 (25)| 00:00:01 |
| 2 | GENERATE CUBE | | 14 | 756 | 8 (25)| 00:00:01 |
| 3 | SORT GROUP BY | | 14 | 756 | 8 (25)| 00:00:01 |
|* 4 | HASH JOIN | | 14 | 756 | 7 (15)| 00:00:01 |
| 5 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS STORAGE FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
1353 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
18 rows processed
- CUBE는 GROUPING COLUMNS이 가질 수 있는 모든 경우의 수에 대하여 Subtotal을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면, 2의 N승 LEVEL의 Subtotal을 생성하게 된다.
- 실행 결과에서 CUBE 함수 사용으로 ROLLUP 함수의 결과에다 업무별 집계까지 추가해서 출력할 수 있는데, ROLLUP 함수에 비해 업무별 집계를 표시한 5건의 레코드가 추가된 것을 확인할 수 있다.
- (All Departments - CLERK, ANALYST, MANAGER, SALESMAN, PRESIDENT 별 집계가 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;
DNAME JOB Total Empl Total Sal
--------------- --------- ---------- ----------
SALES MANAGER 1 2850
SALES CLERK 1 950
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING CLERK 1 1300
SALES SALESMAN 4 5600
RESEARCH MANAGER 1 2975
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
ACCOUNTING All Jobs 3 8750
RESEARCH All Jobs 5 10875
SALES All Jobs 6 9400
All Departments CLERK 4 4150
All Departments SALESMAN 4 5600
All Departments PRESIDENT 1 5000
All Departments MANAGER 3 8275
All Departments ANALYST 2 6000
All Departments All Jobs 14 29025
18 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2199532226
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 2097 | 29 (80)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 14 | 756 | 8 (25)| 00:00:01 |
|* 3 | HASH JOIN | | 14 | 756 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS STORAGE FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
| 6 | HASH GROUP BY | | 14 | 672 | 8 (25)| 00:00:01 |
|* 7 | HASH JOIN | | 14 | 672 | 7 (15)| 00:00:01 |
| 8 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS STORAGE FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
| 10 | HASH GROUP BY | | 14 | 630 | 8 (25)| 00:00:01 |
|* 11 | HASH JOIN | | 14 | 630 | 7 (15)| 00:00:01 |
| 12 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS STORAGE FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
| 14 | SORT AGGREGATE | | 1 | 39 | | |
|* 15 | HASH JOIN | | 14 | 546 | 7 (15)| 00:00:01 |
| 16 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS STORAGE FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
11 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
15 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
1354 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
4. GROUPING SETS 함수
Useful Information
- GROUP BY SQL 문장을 여러 번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있게 되었다.
- 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 (JOB, DNAME);
DNAME JOB Total Empl Total Sal
--------------- --------- ---------- ----------
All Departments CLERK 4 4150
All Departments SALESMAN 4 5600
All Departments PRESIDENT 1 5000
All Departments MANAGER 3 8275
All Departments ANALYST 2 6000
ACCOUNTING All Jobs 3 8750
RESEARCH All Jobs 5 10875
SALES All Jobs 6 9400
8 rows selected.
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));
DNAME JOB MGR Total Sal
-------------- --------- ---------- ----------
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
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
CLERK 7698 950
CLERK 7902 800
PRESIDENT 5000
CLERK 7788 1100
CLERK 7782 1300
SALESMAN 7698 5600
MANAGER 7839 8275
ANALYST 7566 6000
27 rows selected.
"구루비 데이터베이스 스터디모임" 에서 2013년에 "SQL 전문가 가이드" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/2381
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.