제 5 절 그룹함수 (Group Function)
# ANSI/ISO SQL 표준에서 정의하고 있는 데이터 분석 함수
-
AGGREGATE FUNCTION - COUNT, SUM, AVG, MAX, MIN
-
GROUP FUNCTION - ROLLUP, CUBE, GROUPING SETZS,
-
WINDOW FUNCTION - RANK 함수, AGGREGATE 관련 함수, 행 순서 관련 함수, 비율 관련 함수, 선형분석을 위한 통계 분석 관련 함수
* 참고 - 현재 대부분의 DBMS는 집계 기능만 지원하고 별도의 정렬을 하지 않는다.
정렬이 필요하면 명시적으로 ORDER BY 절을 사용해라.
1. ROLLUP 함수
# 특징
-
주어진 인수의 Sub Total을 생성하기 위해 사용
-
N개의 인수가 있다면 N+1 level의 SUB TOTAL 생성
-
계층 구조라 인수의 순서가 변경되면 결과도 바뀐다.
-
별도의 정렬을 수행하지 않는다.
< 일반적인 GROUP BY 절 사용 >
|
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
RESEARCH MANAGER 1 2975
SALES SALESMAN 4 5600
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2708255165
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
|
< GROUP BY + 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;
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.
Execution Plan
----------------------------------------------------------
Plan hash value: 2970111170
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
|
< ROLLUP 함수 사용 >
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
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES 6 9400
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
RESEARCH 5 10875
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
DNAME JOB Total Empl Total Sal
---------------------------- ------------------ ---------- ----------
ACCOUNTING 3 8750
14 29025
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
# 계층 간 집계에 대해서는 level 별 순서(DNAME -> JOB 순)로 정렬하지만, 계층 내에서는 별도의 정렬을 하지 않는다.
|
< 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 ;
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
DNAME JOB Total Empl Total Sal
---------------------------- ------------------ ---------- ----------
SALES 6 9400
14 29025
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
|
< GROUPING 함수 : ROLLUP이나 CUBE에 의해 소계된 결과에는 1이 표시되고, 그렇지 않으면 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);
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
DNAME GROUPING(DNAME) JOB GROUPING(JOB) Total Empl Total Sal
---------------------------- --------------- ------------------ ------------- ---------- ----------
ACCOUNTING 0 1 3 8750
1 1 14 29025
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
|
< 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
DNAME JOB Total Empl Total Sal
------------------------------ ------------------ ---------- ----------
ACCOUNTING All Jobs 3 8750
All Departments All Jobs 14 29025
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
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 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
DNAME JOB Total Empl Total Sal
------------------------------ ------------------ ---------- ----------
ACCOUNTING All Jobs 3 8750
All Departments All Jobs 14 29025
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
|
< 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
DNAME JOB Total Empl Total Sal
------------------------------ ------------------ ---------- ----------
ACCOUNTING All Jobs 3 8750
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
# GRANDTOTAL 부분이 빠졌다.
|
< 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.
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 448 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 448 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 266 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 14 | 266 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
# JOB, MGR을 하나의 집합으로 간주
|
2. CUBE 함수
# 특징
-
결합 가능한 모든 값에 대하여 SUBTOTAL 생성
-
내부적으로 주어진 인수의 순서를 바꿔가며 쿼리를 추가 수행해야 함
-
내부적으로 추가 수행된 쿼리상에서 추출된 GRAND TOTAL은 중복되므로, 제거 작업을 해야 함.
-
ROLLUP에 비해 시스템 부담을 많이 준다.
-
계층별 집계를 구할 수 있지만, 주어진 인수들의 관계는 평등하다. 그러므로 인수의 순서가 바뀌어도 결과는 동일함.
-
정렬이 필요할 땐 ORDER BY를 사용해야 함.
< CUBE 함수 >
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
DNAME JOB Total Empl Total Sal
------------------------------ ------------------ ---------- ----------
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: 2382666110
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | GENERATE CUBE | | 14 | 392 | 7 (29)| 00:00:01 |
| 3 | SORT GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 4 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 7 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
8 - filter("EMP"."DEPTNO" IS NOT NULL)
# 주어진 인수의 수가 N 개 라면 2의 N 승 LEVEL의 Subtotal이 생성됨
|
< 일반 GROUP BY + 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 ;
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
RESEARCH MANAGER 1 2975
SALES SALESMAN 4 5600
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
ACCOUNTING All Jobs 3 8750
RESEARCH All Jobs 5 10875
DNAME JOB Total Empl Total Sal
------------------------------ ------------------ ---------- ----------
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: 3581574626
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 514 | 21 (77)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 3 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
| 8 | HASH GROUP BY | | 2 | 40 | 7 (29)| 00:00:01 |
| 9 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 11 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 12 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 14 | HASH GROUP BY | | 5 | 75 | 4 (25)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 7 | | |
|* 17 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
7 - filter("EMP"."DEPTNO" IS NOT NULL)
12 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
13 - filter("EMP"."DEPTNO" IS NOT NULL)
15 - filter("EMP"."DEPTNO" IS NOT NULL)
17 - filter("EMP"."DEPTNO" IS NOT NULL)
# 이렇게 4번씩 access 해야 하는 것을 CUBE를 사용함으로써 한번의 Access로 구현함.
|
< CUBE 함수 인수가 3개인 경우>
DNAME JOB DEPTNO Total Empl Total Sal
------------------------------ ------------------ ---------- ---------- ----------
All Departments All Jobs 1 14 29025
SALES All Jobs 1 6 9400
RESEARCH All Jobs 1 5 10875
ACCOUNTING All Jobs 1 3 8750
All Departments CLERK 1 4 4150
SALES CLERK 1 1 950
RESEARCH CLERK 1 2 1900
ACCOUNTING CLERK 1 1 1300
All Departments ANALYST 1 2 6000
RESEARCH ANALYST 1 2 6000
All Departments MANAGER 1 3 8275
SALES MANAGER 1 1 2850
RESEARCH MANAGER 1 1 2975
ACCOUNTING MANAGER 1 1 2450
All Departments SALESMAN 1 4 5600
SALES SALESMAN 1 4 5600
All Departments PRESIDENT 1 1 5000
DNAME JOB DEPTNO Total Empl Total Sal
------------------------------ ------------------ ---------- ---------- ----------
ACCOUNTING PRESIDENT 1 1 5000
All Departments All Jobs 0 3 8750
ACCOUNTING All Jobs 0 3 8750
All Departments CLERK 0 1 1300
ACCOUNTING CLERK 0 1 1300
All Departments MANAGER 0 1 2450
ACCOUNTING MANAGER 0 1 2450
All Departments PRESIDENT 0 1 5000
ACCOUNTING PRESIDENT 0 1 5000
All Departments All Jobs 0 5 10875
RESEARCH All Jobs 0 5 10875
All Departments CLERK 0 2 1900
RESEARCH CLERK 0 2 1900
All Departments ANALYST 0 2 6000
RESEARCH ANALYST 0 2 6000
All Departments MANAGER 0 1 2975
RESEARCH MANAGER 0 1 2975
DNAME JOB DEPTNO Total Empl Total Sal
------------------------------ ------------------ ---------- ---------- ----------
All Departments All Jobs 0 6 9400
SALES All Jobs 0 6 9400
All Departments CLERK 0 1 950
SALES CLERK 0 1 950
All Departments MANAGER 0 1 2850
SALES MANAGER 0 1 2850
All Departments SALESMAN 0 4 5600
SALES SALESMAN 0 4 5600
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2382666110
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | GENERATE CUBE | | 14 | 392 | 7 (29)| 00:00:01 |
| 3 | SORT GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 4 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 7 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
8 - filter("EMP"."DEPTNO" IS NOT NULL)
3. GROUPING SETS
# 특징
-
주어진 인수들에 대한 개별 집계만 구할 수 있음
-
SUBTOTAL, GRANDTOTAL 없음
-
주어진 인수들이 평등한 관계이기 때문에 인수의 순서가 바뀌어도 결과는 동일함.
-
ORDER BY에 의한 명시적인 정렬이 필요함.
< 일반 그룹함수 및 Union All 절 이용 >
SELECT DNAME, 'All Jobs' JOB, 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 ;
DNAME JOB Total Empl Total Sal
------------------------------ ------------------ ---------- ----------
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
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 614975970
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 115 | 11 (55)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 2 | 40 | 7 (29)| 00:00:01 |
| 3 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 8 | HASH GROUP BY | | 5 | 75 | 4 (25)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
7 - filter("EMP"."DEPTNO" IS NOT NULL)
9 - filter("EMP"."DEPTNO" IS NOT NULL)
# DNAME 별 인원수와 급여를, JOB 별 인원수와 급여를 구한다. CUBE와 비교해 보면 각 인수별로 SUBTOTAL, GRANDTOTAL은 구하지 않는다는 것이 큰 차이점이다.
|
< GROUPING SETS 사용 >
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);
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.
Execution Plan
----------------------------------------------------------
Plan hash value: 1206596907
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 938 | 14 (22)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6606_E2619F0 | | | | |
| 3 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
| 8 | LOAD AS SELECT | SYS_TEMP_0FD9D6607_E2619F0 | | | | |
| 9 | HASH GROUP BY | | 1 | 19 | 3 (34)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_E2619F0 | 1 | 19 | 2 (0)| 00:00:01 |
| 11 | LOAD AS SELECT | SYS_TEMP_0FD9D6607_E2619F0 | | | | |
| 12 | HASH GROUP BY | | 1 | 22 | 3 (34)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_E2619F0 | 1 | 22 | 2 (0)| 00:00:01 |
| 14 | VIEW | | 1 | 67 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_E2619F0 | 1 | 54 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("SYS_TBL_$1$"."DEPTNO"="SYS_TBL_$2$"."DEPTNO")
filter("SYS_TBL_$1$"."DEPTNO"="SYS_TBL_$2$"."DEPTNO")
7 - filter("SYS_TBL_$2$"."DEPTNO" IS NOT NULL)
# GROUP BY와 UNION ALL을 사용한 SQL과 결과는 동일하지만 정렬 순서는 다를 수 있다.
|
< 주어진 인자의 순서를 변경>
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.
Execution Plan
----------------------------------------------------------
Plan hash value: 874850616
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 536 | 14 (22)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660A_E2619F0 | | | | |
| 3 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
| 8 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_E2619F0 | | | | |
| 9 | HASH GROUP BY | | 1 | 19 | 3 (34)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_E2619F0 | 1 | 19 | 2 (0)| 00:00:01 |
| 11 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_E2619F0 | | | | |
| 12 | HASH GROUP BY | | 1 | 22 | 3 (34)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_E2619F0 | 1 | 22 | 2 (0)| 00:00:01 |
| 14 | VIEW | | 1 | 67 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_E2619F0 | 1 | 54 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("SYS_TBL_$1$"."DEPTNO"="SYS_TBL_$2$"."DEPTNO")
filter("SYS_TBL_$1$"."DEPTNO"="SYS_TBL_$2$"."DEPTNO")
7 - filter("SYS_TBL_$2$"."DEPTNO" IS NOT NULL)
# 주어진 인수는 평등한 관계이기 때문에 순서가 바뀌어도 결과는 동일하다.
|
< 3개의 인수가 주어질 경우 >
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
ACCOUNTING CLERK 7782 1300
RESEARCH CLERK 7788 1100
RESEARCH CLERK 7902 800
RESEARCH ANALYST 7566 6000
SALES MANAGER 7839 2850
RESEARCH MANAGER 7839 2975
ACCOUNTING MANAGER 7839 2450
SALES SALESMAN 7698 5600
ACCOUNTING PRESIDENT 5000
CLERK 7698 950
DNAME JOB MGR Total Sal
---------------------------- ------------------ ---------- ----------
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 800
ANALYST 7566 6000
MANAGER 7839 8275
SALESMAN 7698 5600
PRESIDENT 5000
SALES MANAGER 2850
SALES CLERK 950
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
DNAME JOB MGR Total Sal
---------------------------- ------------------ ---------- ----------
ACCOUNTING PRESIDENT 5000
RESEARCH MANAGER 2975
SALES SALESMAN 5600
RESEARCH ANALYST 6000
RESEARCH CLERK 1900
27 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3609026730
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 14 (22)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | MULTI-TABLE INSERT | | | | | |
| 3 | DIRECT LOAD INTO | SYS_TEMP_0FD9D660E_E2619F0 | | | | |
| 4 | DIRECT LOAD INTO | SYS_TEMP_0FD9D660F_E2619F0 | | | | |
| 5 | SORT GROUP BY ROLLUP | | 14 | 448 | 7 (29)| 00:00:01 |
| 6 | MERGE JOIN | | 14 | 448 | 6 (17)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 9 | SORT JOIN | | 14 | 266 | 4 (25)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | EMP | 14 | 266 | 3 (0)| 00:00:01 |
| 11 | LOAD AS SELECT | SYS_TEMP_0FD9D660F_E2619F0 | | | | |
| 12 | HASH GROUP BY | | 1 | 28 | 3 (34)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_E2619F0 | 1 | 28 | 2 (0)| 00:00:01 |
| 14 | VIEW | | 2 | 82 | 4 (0)| 00:00:01 |
| 15 | VIEW | | 2 | 82 | 4 (0)| 00:00:01 |
| 16 | UNION-ALL | | | | | |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_E2619F0 | 1 | 41 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660F_E2619F0 | 1 | 41 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("SYS_TBL_$1$"."DEPTNO"="SYS_TBL_$2$"."DEPTNO")
filter("SYS_TBL_$1$"."DEPTNO"="SYS_TBL_$2$"."DEPTNO")
10 - filter("SYS_TBL_$2$"."DEPTNO" IS NOT NULL)
|
|
|
댓글 없음:
댓글 쓰기