2장_5절. 그룹함수(Group Function)
1. 데이터 분석 개요
1)Aggregate Function
-GROUP AGGREGATE FUNCTION이라고도 부르며, GROUP FUNCTION의 한 부분으로 분류된다.
-COUNT, SUM, AVG, MAX, MIN과 각종 집계함수가 포함된다.
2)Group Function
-결산 개념의 업무를 가지는 것들은 소계, 중계, 합계등 여러 레벨의 결산보고서가 중요하다.
-그룹함수를 사용하면 하나의 sql로 테이블을 한 번만 읽어서 빠르게 원하는 정보를 찾을 수 있다.
-Roll Up : 소그룹 간의 소계를 계산 // group by의 확장된 형태로 병렬수행이 가능하다
-Cube : Group by 항목들간 다 차원적인 소계를 계산 // 결합 가능한 모든 값에 대하여 집계를 생성할 수 있지만 시스템에 부하를 많이 준다.
-Grouping Sets : 특정 항목에 대한 소계를 계산 // 원하는 부분의 소계만을 추출할 수 있다.
3)Window Function
-분석함수나 순위함수로 알려져있다.
-데이터웨어하우스에서 발전한 기능이다.
2.Roll Up함수
-롤업에 지정된 그룹핑 컬럼의 리스트는 서브집계를 생성하기위해 사용된다.
-그룹핑 컬럼의 수가 N일때 N+1레벨의 서브집계를 생성한다.
-롤업의 인수는 계층구조로 인수의 순서가 바뀌면 수행결과도 바뀐다.
단계1. 일반적인 group by절 사용
-부서명과 업무명을 기준으로 사원수와 급여 합을 집계하라.
(1)부서명기준
(2)업무명기준
(3)부서명+업무명기준
SQL> SELECT DNAME, JOB, COUNT(*) "총사원수", SUM(SAL) "총급여"
2 FROM EMP, DEPT
3 WHERE DEPT.DEPTNO = EMP.DEPTNO
4 GROUP BY DNAME, JOB;
단계1-2). group by절 + order by절 사용
부서명과 업무명을 기준으로 집계한 일반적인 group by문장은 order by를 사용함으로써 부서,업무별로 정렬이 이루어진다.
SQL> SELECT DNAME, JOB, COUNT(*) "총사원수", SUM(SAL) "총급여"
2 FROM EMP, DEPT
3 WHERE DEPT.DEPTNO = EMP.DEPTNO
4 GROUP BY DNAME, JOB
5 ORDER BY 1,2;
단계2. Roll Up함수 사용하기
-롤업은 파라미터 순서대로 정렬한후 레벨별 소계를 내준다.
SQL> SELECT DNAME, JOB, COUNT(*) "총사원수", SUM(SAL) "총급여"
2 FROM EMP, DEPT
3 WHERE DEPT.DEPTNO = EMP.DEPTNO
4 GROUP BY ROLLUP (DNAME, JOB);
->그룹핑 컬럼의 개수가 2개(DNAME,JOB)일때 2+1개(DNAME별, JOB별, 전체사원)의 서브집계를 한다.
->SALES업부무서의 총급여는 950+2850+5600 = 9400원
->모든부서의 총급여는 9400+10875+8750 =29025원
단계3. Grouping함수 사용
-grouping함수의 뜻은 괄호안의 컬럼의 그룹의 소계가 다 됬다는의미???
-rollup, cube, grouping sets등 그룹함수를 지원하기 위해 grouping함수가 추가되었다.
-rollup이나 cube에 의한 소계가 계산완료된 결과는 1이 표시되고, 계산안됬으면 0이 표시됨
-grouping함수와 case/decode함수를 이용해, 소계를 나타내는 곳에 원하는 문자열을 지정할 수 있다.
SQL> SELECT DNAME, GROUPING(DNAME),
2 JOB, GROUPING(JOB),
3 COUNT(*) "총사원수",
4 SUM(SAL) "총급여"
5 FROM EMP, DEPT
6 WHERE DEPT.DEPTNO = EMP.DEPTNO
7 GROUP BY ROLLUP (DNAME,JOB);
->부서별로 그룹핑 한후, 직무별 그룹핑을 하였다.
->SALES부서의 총 소계가 완성되서 GROUPING(JOB)값이 1임.
단계4. Grouping함수 + Case함수
-그룹핑함수는 집계유무를 구분하고, Case를 통해 0,1를 문자로 바꿀수 있음.
SQL> SELECT
2 CASE GROUPING(DNAME) WHEN 1 THEN '모든부서들' ELSE DNAME END AS DNAME,
3 CASE GROUPING(JOB) WHEN 1 THEN '모든직무들' ELSE JOB END AS JOB,
4 COUNT(*) "총사원수",
5 SUM(SAL) "총급여"
6 FROM EMP, DEPT
7 WHERE DEPT.DEPTNO = EMP.DEPTNO
8 GROUP BY ROLLUP(DNAME,JOB);
->GROUPING(DNAME)과 GROUPING(JOB)은 0또는 1값을 가지는데, CASE문을 사용하여 바꾸었다.
즉
SQL> SELECT
2 GROUPING(DNAME),
3 GROUPING(JOB),
4 COUNT(*) "총사원수",
5 SUM(SAL) "총급여"
6 FROM EMP, DEPT
7 WHERE DEPT.DEPTNO = EMP.DEPTNO
8 GROUP BY ROLLUP(DNAME,JOB);
여기따가 GROUPING(DNAME)과 GROUPING(JOB)을 CASE를 사용하여보고서용에맞게 0,1에서 글자로 바꿈
단계4-2) ROLLUP함수 일부 사용?
-GROUP BY ROLLUP(DNAME,JOB)을 GROUP BY DNAME,ROLLUP(JOB)으로 바꾸면 어떻게 되까??
SQL> SELECT
2 CASE GROUPING(DNAME) WHEN 1 THEN '모든부서들' ELSE DNAME END AS DNAME,
3 CASE GROUPING(JOB) WHEN 1 THEN '모든직무들' ELSE JOB END AS JOB,
4 COUNT(*) "총사원수",
5 SUM(SAL) "총급여"
6 FROM EMP, DEPT
7 WHERE DEPT.DEPTNO = EMP.DEPTNO
8 GROUP BY DNAME, ROLLUP(JOB);
->DNAME기준으로 그룹핑한후->JOB별로 세부집계가 나온다.
->????마지막에 총사원수와 총급여의 합계가 사라졌다??
->ROLLUP이 DNAME+JOB두개 모두에서 ->JOB 한개만 기준으로 소집계해서 마지막줄
사라짐.
#비교1 DNAME만 롤업
->JOB기준으로 그룹핑후->DNAME별로 세부집계가 나온다.
SQL> SELECT
2 CASE GROUPING(DNAME) WHEN 1 THEN '모든부서들' ELSE DNAME END AS DNAME,
3 CASE GROUPING(JOB) WHEN 1 THEN '모든직무들' ELSE JOB END AS JOB,
4 COUNT(*) "총사원수",
5 SUM(SAL) "총급여"
6 FROM EMP, DEPT
7 WHERE DEPT.DEPTNO = EMP.DEPTNO
8 GROUP BY ROLLUP(DNAME), JOB;
#비교2 : DNAME과 JOB모두 롤업
->DNAME기준으로 그룹핑후->JOB별로 세부집계가 나오고->마지막에 DNAME기준 집계도 나옴
SQL> SELECT
2 CASE GROUPING(DNAME) WHEN 1 THEN '모든부서들' ELSE DNAME END AS DNAME,
3 CASE GROUPING(JOB) WHEN 1 THEN '모든직무들' ELSE JOB END AS JOB,
4 COUNT(*) "총사원수",
5 SUM(SAL) "총급여"
6 FROM EMP, DEPT
7 WHERE DEPT.DEPTNO = EMP.DEPTNO
8 GROUP BY ROLLUP(DNAME,JOB);
댓글
댓글 쓰기