SQLD 4-5 ] 그룹 함수(GROUP FUNCTION)

❏ 데이터 분석 개요

 

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

   1. AGGREGATE FUNCTION

   2. GROUP FUNCTION

   3. WINDOW FUNCTION

 

 

1. AGGREGATE FUNCTION

 - GROUP FUNCTION 의 한 부분.  그 앞에서 배웠던 집계 함수들. COUNT, SUM, AVG, MAX, MIN 같은거 ..

 

2. GROUP FUNCTION

 - 이제야 좀 그룹 다운 기능을 제공하나? 스케일이 좀 커지나..??

 

 - 결산 개념의 업무를 가지는 원가/판매 시스템 에서 소계, 중계, 합계 등 

 - 그룹 함수를 사용한다면 하나의 SQL 로 테이블을 한번만 읽어서 빠르게 처리할 수 있다.

 - 종류는.. 

 - ROLLUP : 소그룹 간의 소계를 계산, 사용하기가 쉽다, 병렬로 수행이 가능하다, 시간 및 지역처럼 분류를 포함하는 데이터에 적합

 - CUBE : 소규모 그룹 그룹 간 다차원적인 소계를 계산할 수 있다. ROLLUP 에 비해 다양한 데이터를 얻긴 한데.. 좀 무겁다.

 - GROUPING SETS : 특정 항목에 대한 소계를 계산. 원하는 부분만 손쉽게 계산 가능하다

 - ROLLUP / CUBE / GROUPING SETS 결과에 대한 정렬이 필요한 경우에는 ORDER BY 절에 칼럼을 명시해야 한다. (당연한..)

 

3. WINDOWS FUNCTION

 - 분석함수 (ANALYTIC FUNCTION) 나 순위함수 (RANK FUNCTION) 로도 알려져 있는 윈도우 함수.

 

이렇게 데이터 분석 함수는 3가지(집계함수, 그룹함수, 윈도우함수) 가 있고 이번 절에서는 그룹함수에 대해 알아보자!!!

 

 

 

❏ ROLL UP 

 

  - ROLLUP 에 지정된 Grouping Column의 List 는 소계 를 생성하기 위해 사용.

    Grouping Column 의 수를 N 이라고 했을 때 N+1 Level 의 소계가 생성된다.

    중요한것은, ROLLUP 의 인수는 계층구조 이므로 인수 순서가 바뀌면 수행 결과도 바뀌어지므로 주의해야한다!

    일단 예제를 보며 이해하자,,

 

1> GROUP BY

 

부서명과 업무명을 기준으로 사원수와 급여 합을 집계.

select DNAME, JOB, COUNT(*) as "Total Empl", SUM(SAL) as "Total Sal"
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by DNAME, JOB;

 

2> GROUP BY + ORDER BY

select DNAME, JOB, COUNT(*) as "Total Empl", SUM(SAL) as "Total Sal"
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by DNAME, JOB
order by DNAME, JOB;

결과 정렬 했다.

 

 

3> ROLLUP

select DNAME, JOB, COUNT(*) as "Total Empl", SUM(SAL) as "Total Sal"
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by rollup (DNAME, JOB);

 

사용방법은 그냥 group by ROLLUP (그룹1, 그룹2, ...)

 

 

 

 

보면. 소계 도 보이고.. 총계 (맨밑) 도 보인다.

ROLLUP 설명에서 그룹핑 칼럼 수가 N 이면 N+1 의 소계가 생긴다는 말이 

(DNAME, JOB) 2개 했으니까 소계는 3개가 나온다는 말이다.

그리고. LEVEL 보는법.

 

LEVEL 1 -> 그냥 표준 결과 (9건)

LEVEL 2 -> 소계 (3건)

LEVEL 3 -> 총계 (1건)

 

보통 LEVEL  순서는 1->2->3 단계로 표시하지만..  계층 내의 정렬은 지원하지 않는다

 

 

4> GROUPING

 - 소계를 한번 했으면 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);

 

 

 

 

 

 

 

5> GROUPING + CASE 

 - 아까 GROUPING 설명할 때 CASE/DECODE 쓰면 좋다고 얘기한거

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

 

 

 

6> 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 으로 했긴 했는데.. 그 소집계 는 JOB 만 했다.

 

제일 마지막줄 총합계 는 빠진다. 왜냐면 ROLLUP 이 JOB 에만 사용되었고 DNAME 에는 필요하지 않기 때문이다.

 

 

7> ROLLUP 함수 결합 칼럼 사용

 

JOB과 MGR은 하나의 집합으로 간주해보자

select DNAME, JOB, MGR, SUM(SAL) "Total Sal"
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by rollup (DNAME, (JOB, MGR));

 

 

 

ROLLUP 함수 사용시 괄호로 묶은 JOB과 MGR의 경우 하나의 집합(JOB+MGR) 칼럼으로 간주하여 괄호 내 각 칼럼별

집계를 구하지 않는다.

 

 

❏ CUBE

 

  - ROLLUP 에서는 가능한 소계 만을 생성했는데.. CUBE 는 결합 가능한 모든값에 대해 집계를 생성한다!! 감이 안오지만..

  - 모든 경우의 수에 대하여 계를 내기 때문에 딱히 인수에 순서에 상관은 없다

 

 

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

 

마지막에 ROLLUP (DNAME, JOB) 에서 CUBE (DNAME, JOB) 으로 바꿨다.

 

 

 

CUBE 는 그룹핑칼럼 이 가질 수 있는 모든 경우의 수에 대하여 소계를 생성한다.

그룹핑칼럼 의 수가 N 이면 2의 N승 LEVEL 의 소계를 생성한다

All Departments 에 마저 경우의수를 다 붙였네.

 

 

2> UNION ALL 사용 SQL

 

CUBE 랑 이렇게 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 DEPERTMENTS', JOB, COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
WHERE  DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB
UNION ALL
SELECT 'ALL DEPERTMENTS', 'ALL JOBS', COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM   EMP, DEPT
WHERE  DEPT.DEPTNO = EMP.DEPTNO ;

 

아니 왜이렇게까지..

 

 

 

 

❏ GROUPING SETS

 

  -  더욱 다양한 소계 집합? 을 만들 수 있다. GROUP BY 의 SQL 문장을 여러 번 반복하지 않아도 원하는 결과를 얻을 수 있다

  -  인수의 순서가 바뀌어도 상관 없다.( 인수의 순서 상관 있는게 아까 ROLLUP,  상관없는게 CUBE,GROUPING SETS )

 

1> 일반 그룹함수를 이용

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;

 

 

 

이거를..

 

 

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

 

 

 

 

 

이렇게 표기할 수 있다. GROUPING SETS 함수를 사용하면 UNION ALL 을 사용한 결과와 같다.

 

 

3> 3개의 인수를 이용한 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 을 정리하자면

그냥 group by DNAME, JOB 하면 DNAME 에도 해당하고 JOB 에도 해당하는 것 처리하는데.(교집합)

group by grouping sets (DNAME, JOB) 하면.

DNAME 그룹 결과 (합집합) JOB 그룹 결과

가 나온다. UNION ALL 여러번 할 필요가 없다.

 

 

CUBE 명령시에 내가 원치 않는 모든 조합의 결과물을 보여주므로 내가 원하는 않는 부분도 보게 되므로

원하는 부분만 세팅해서 볼수 있게 하는것이 GROUPING SETS 입니다.

 

예를들어서 형액형,직업별을 같이 조합한 결과물과  형액형별,부서별의 조합결과만 보고 싶다면

 

SELECT B_TYPE, JOB, DEPTNO

FROM 테이블이름

GROUPING SETS((B_TYPE, JOB),(B_TYPE,DEPTNO))

 

참고 : http://blog.daum.net/kwonmc/5918811