DATOR


2. Grouping 함수 ANSI 표준SQL


Grouping 함수              

 

 ROLLUP, CUBE, GROUPING SETS GROUP BY 구절에서 다양한 데이터 결과 셋을 얻고자 할 때 사용한다.

 

 (1) ROLLUP 함수

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

 

ROLLUP

ROLLUP GROUP BY 절에서 Grouping하고자 하는 컬럼들을 ()로 묶어주어 사용한다.

 

SELECT department_id, job_id, SUM(salary)

     FROM   employees

     WHERE  department_id < 60

GROUP BY ROLLUP(department_id, job_id);

 

DEPARTMENT_ID

JOB_ID

SUM(SALARY)

10

AD_ASST

4400

10

 

4400

20

MK_MAN

13000

20

MK_REP

6000

20

 

19000

30

PU_MAN

11000

30

PU_CLERK

13900

30

 

24900

40

HR_REP

6500

40

 

6500

50

ST_MAN

36400

50

SH_CLERK

64300

50

ST_CLERK

55700

50

 

156400

 

 

211200

 

 위의 결과에서 녹색 글씨는 부서ID와 직책ID, 파랑 글씨는 부서ID, 짙은 노랑색의 결과는 전체에 대한 부서 금액에 대한 급여 총계이다.

 만약 위의 쿼리의 Grouping 컬럼의 순서가 바뀌면 아래와 같이 결과 셋이 달라지게 된다.

 

 

SELECT job_id,department_id,  SUM(salary)

     FROM   employees

     WHERE  department_id < 60

GROUP BY ROLLUP(job_id, department_id);

 

DEPARTMENT_ID

JOB_ID

SUM(SALARY)

HR_REP

40

6500

HR_REP

 

6500

MK_MAN

20

13000

MK_MAN

 

13000

MK_REP

20

6000

MK_REP

 

6000

PU_MAN

30

11000

PU_MAN

 

11000

ST_MAN

50

36400

ST_MAN

 

36400

AD_ASST

10

4400

AD_ASST

 

4400

PU_CLERK

30

13900

PU_CLERK

 

13900

SH_CLERK

50

64300

SH_CLERK

 

64300

ST_CLERK

50

55700

ST_CLERK

 

55700

 

 

211200

 

위의 결과에서 녹색 글씨는 직책ID와 부서ID, 파랑 글씨는 직책ID, 짙은 노랑색의 결과는 전체에 대한 부서 금액에 대한 급여 총계이다.

 

아래와 같이 GROUP BY 중 일부 컬럼만 ROLLUP으로 지정하여 일부컬럼만의 SUBTOTAL을 구할 수도 있다.

 

SELECT department_id, job_id,  SUM(salary)

     FROM   employees

     WHERE  department_id < 60

GROUP BY department_id, ROLLUP(job_id);

 

DEPARTMENT_ID

JOB_ID

SUM(SALARY)

10

AD_ASST

4400

10

 

4400

20

MK_MAN

13000

20

MK_REP

6000

20

 

19000

30

PU_MAN

11000

30

PU_CLERK

13900

30

 

24900

40

HR_REP

6500

40

 

6500

50

ST_MAN

36400

50

SH_CLERK

64300

50

ST_CLERK

55700

50

 

156400

 

 결과를 보면 직책ID에 대한 SUBTOTAL은 나왔지만, 부서ID SUBTOTAL인 전체 합계에 대한 결과가 나오지 않는 다.

 

 아래와 같이 괄호를 이용해 여러 개의 컬럼을 하나의 SUBTOTAL로 묶어 줄 수 있다.

 

SELECT department_id, manager_id, job_id,  SUM(salary)

     FROM   employees

     WHERE  department_id < 60

GROUP BY ROLLUP(department_id, (manager_id,job_id));

 

DEPARTMENT_ID

MANAGER_ID

JOB_ID

SUM(SALARY)

10

101

AD_ASST

4400

10

 

 

4400

20

100

MK_MAN

13000

20

201

MK_REP

6000

20

 

 

19000

30

100

PU_MAN

11000

30

114

PU_CLERK

13900

30

 

 

24900

40

101

HR_REP

6500

40

 

 

6500

50

100

ST_MAN

36400

50

120

SH_CLERK

11600

50

120

ST_CLERK

10500

50

121

SH_CLERK

14700

50

121

ST_CLERK

10700

50

122

SH_CLERK

12800

50

122

ST_CLERK

10800

50

123

SH_CLERK

13900

50

123

ST_CLERK

12000

50

124

SH_CLERK

11300

50

124

ST_CLERK

11700

50

 

 

156400

 

 

 

211200

 

 결과를 보면 부서는 하나의 컬럼으로 SUBTOTAL을 나타내지만, 매니저ID와 직책ID는 하나로 묶어 SUBTOTAL을 나타내고 있다.

 

 이 처럼 ROLLUP함수는 앞에서 GROUP BY 절에 들어간 순서대로의 계층간의 집계를 구하고자 할 때 사용된다.

 

(2) GROUPING 함수

 ROLLUP, CUBE, GROUPING SETS등 그룹 함수를 지원하기 위해 사용되는 함수이다.

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

 

GROUPING

GROUPING함수는 ROLLUP절에 사용되는 컬럼에 대해 사용한다.

 

SELECT

  department_id

  , GROUPING(department_id) is_department

  , job_id

  , GROUPING(job_id) is_job

  ,  SUM(salary)

     FROM   employees

     WHERE  department_id < 60

GROUP BY ROLLUP(department_id, job_id);

 

DEPARTMENT_ID

IS_DEPT

MANAGER_ID

JOB_ID

SUM(SALARY)

10

0

AD_ASST

0

4400

10

0

 

1

4400

20

0

MK_MAN

0

13000

20

0

MK_REP

0

6000

20

0

 

1

19000

30

0

PU_MAN

0

11000

30

0

PU_CLERK

0

13900

30

0

 

1

24900

40

0

HR_REP

0

6500

40

0

 

1

6500

50

0

ST_MAN

0

36400

50

0

SH_CLERK

0

64300

50

0

ST_CLERK

0

55700

50

0

 

1

156400

 

1

 

1

211200

 

 결과를 보면 직책ID SUBTOTAL IS_JOB의 값에 대해 1로 나타났으며, 부서ID IS_DEPT값에 대한 값은 선행 컬럼이기 때문에 전체 합계에 대해서만 결과를 1의 값을 가진다.

 

 이러한 특성을 이용해 아래와 같이 응용하여 결과를 보여줄 수 있다.

 

SELECT

  CASE WHEN GROUPING(d.department_name) = 1

    THEN '전체합계' ELSE d.department_name END as department_name

  , CASE WHEN GROUPING(job_id) =1

    THEN '직책합계' ELSE e.job_id END AS job_id

  ,  SUM(e.salary)

     FROM   employees e INNER JOIN departments d

     ON e.department_id = d.department_id

     WHERE  d.department_id < 60

GROUP BY ROLLUP(department_name, job_id);

 

DEPARTMENT_ID

JOB_ID

SUM(SALARY)

Shipping

ST_MAN

36400

Shipping

SH_CLERK

64300

Shipping

ST_CLERK

55700

Shipping

직책합계

156400

Marketing

MK_MAN

13000

Marketing

MK_REP

6000

Marketing

직책합계

19000

Purchasing

PU_MAN

11000

Purchasing

PU_CLERK

13900

Purchasing

직책합계

24900

Administration

AD_ASST

4400

Administration

직책합계

4400

Human Resources

HR_REP

6500

Human Resources

직책합계

6500

전체합계

직책합계

211200

 

 

 

(3) CUBE

 

 ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대한 다차원 집계를 생성한다. CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한 번의  Query를 추가 수행해야 하며, Grand Total은 양쪽의 Query에서 모두 생성이 되므로 한번의 Query에서는 제거되어야 하므로, ROLLUP에 비해 시스템의 연산 대상이 많다.

 CUBE함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이 때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로, 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터의 결과 셋은 같다.

CUBE

CUBEGROUP BY절에 CUBE라는 명령어를 기술한다.

 

 

SELECT department_id,  job_id,  SUM(salary)

     FROM   employees

     WHERE  department_id < 60

GROUP BY CUBE(department_id, job_id);

 

 

DEPARTMENT_ID

JOB_ID

SUM(SALARY)

 

 

211200

 

HR_REP

6500

 

MK_MAN

13000

 

MK_REP

6000

 

PU_MAN

11000

 

ST_MAN

36400

 

AD_ASST

4400

 

PU_CLERK

13900

 

SH_CLERK

64300

 

ST_CLERK

55700

10

 

4400

10

AD_ASST

4400

20

 

19000

20

MK_MAN

13000

20

MK_REP

6000

30

 

24900

30

PU_MAN

11000

30

PU_CLERK

13900

40

 

6500

40

HR_REP

6500

50

 

156400

50

ST_MAN

36400

50

SH_CLERK

64300

50

ST_CLERK

55700

순서가 뒤죽박죽으로 보이지만 위의 결과 셋은 아래와 같이 다양한 결과 셋을 나타낸다.

 

1. 전체 통계

2. 직책 통계

3. 부서 통계

4. 부서 + 직책 통계

 

 이와 같이 CUBE는 하나의 쿼리 문을 통해 다양한 결과 셋을 보여준다.

 

(4) GROUPING SETS

 GROUPING SETS는 표시된 인수들에 대한 개별 집계를 구할 수 있는 함수로, 사용자가 직접 GROUPING하고자 하는 레벨을 지정할 수 있다는 점에서 간편히 사용될 수 있는 함수이다.

 

GROUPING SETS

GROUPING SETSGROUP BY절에 GROUPING SETS라는 명령어를 기술한다.

 

SELECT department_id, job_id,  SUM(salary)

     FROM   employees

GROUP BY GROUPING SETS (department_id,job_id)

 

DEPARTMENT_ID

JOB_ID

SUM(SALARY)

 

AC_MGR

12008

 

AC_ACCOUNT

8300

 

IT_PROG

28800

 

ST_MAN

36400

 

AD_ASST

4400

 

PU_MAN

11000

 

SH_CLERK

64300

 

AD_VP

34000

 

FI_ACCOUNT

39600

 

MK_MAN

13000

 

PR_REP

10000

 

FI_MGR

12008

 

PU_CLERK

13900

 

SA_MAN

61000

 

MK_REP

6000

 

AD_PRES

24000

 

SA_REP

250500

 

HR_REP

6500

 

ST_CLERK

55700

100

 

51608

30

 

24900

 

 

7000

20

 

19000

70

 

10000

90

 

58000

110

 

20308

50

 

156400

40

 

6500

80

 

304500

10

 

4400

60

 

28800

쿼리에 대한 결과 값은 부서ID 직책ID 대한 결과를 각각 나타냈다.

쿼리의 결과는 아래의 쿼리와 같다.

 

SELECT null, job_id,  SUM(salary)

     FROM   employees

GROUP BY job_id

UNION ALL

SELECT department_id, null,  SUM(salary)

     FROM   employees

GROUP BY department_id

 

 

 아래와 같이 () 이용해 하나의 GROUP으로 묶어 사용할 수도 있다.

 

SELECT department_id, job_id, SUM(salary)

     FROM   employees

     WHERE  department_id < 60

GROUP BY GROUPING SETS ((department_id, job_id),(department_id),()) ;

 

DEPARTMENT_ID

JOB_ID

SUM(SALARY)

10

AD_ASST

4400

10

 

4400

20

MK_MAN

13000

20

MK_REP

6000

20

 

19000

30

PU_MAN

11000

30

PU_CLERK

13900

30

 

24900

40

HR_REP

6500

40

 

6500

50

ST_MAN

36400

50

SH_CLERK

64300

50

ST_CLERK

55700

50

 

156400

 

 

211200

 

 

 

 

 위의 결과 셋에 나온 집합은 다음과 같다.

 

1. 부서ID + 직책ID (department_id, job_id)

2. 부서ID (department_id)

3. 전체 ()

 

 이러한 방법으로 GROUPING SETS 여러 가지 사용자 지정에 따른 GROUP BY 결과를 나타낼 있다.

Tag :

Leave Comments