Oracle/13일
13일차 1
Caprica Six
2012. 9. 20. 10:03
부서별 집계, 전체집계 만들기
예)
| DEPTNO | JOB | SUM(SUM_SAL) |
| 10 | CLERK | 1300 |
| 10 | MANAGER | 2450 |
| 10 | PRESIDENT | 5000 |
| 소계 | 8750 | |
| 20 | ANALYST | 1000 |
| 20 | AAA | 1000 |
| 소계 | 2000 | |
| 총합 | 10750 |
카르테시안 조인을 이용한다..
SCOTT 계정
SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job ORDER BY 1,2;
DEPTNO JOB SUM(SAL)
------ --------- --------
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 800
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
NULL CLERK 1300
이걸 3배로 뻥튀기해서 출력해서 부분합, 전체합에 사용할거야.
일련번호 들어간 조인용 테이블 만들거나..
rownum : rowid같은 가상의 컬럼. SELECT하는 순간에 발생
SELECT rownum AS no FROM emp;
NO -- 1 2 3 4 5 6 7 8 9 10 11 12
이걸 사용해서 3배로 뻥튀기할 조인용테이블 만들어
SELECT rownum AS no FROM emp WHERE rownum<=3;
NO -- 1 2 3
SELECT no,
deptno,
job,
sum_sal
FROM
( SELECT deptno, job, SUM(sal) AS sum_sal
FROM emp
GROUP BY deptno, job
ORDER BY 1,2 ),
( SELECT rownum AS no
FROM emp
WHERE rownum<=3);
NO DEPTNO JOB SUM_SAL -- ------ --------- ------- 1 10 MANAGER 2450 1 10 PRESIDENT 5000 1 20 ANALYST 3000 1 20 CLERK 800 1 20 MANAGER 2975 1 30 CLERK 950 1 30 MANAGER 2850 1 30 SALESMAN 5600 1 NULL CLERK 1300 2 10 MANAGER 2450 2 10 PRESIDENT 5000 2 20 ANALYST 3000 2 20 CLERK 800 2 20 MANAGER 2975 2 30 CLERK 950 2 30 MANAGER 2850 2 30 SALESMAN 5600 2 NULL CLERK 1300 3 10 MANAGER 2450 3 10 PRESIDENT 5000 3 20 ANALYST 3000 3 20 CLERK 800 3 20 MANAGER 2975 3 30 CLERK 950 3 30 MANAGER 2850 3 30 SALESMAN 5600 3 NULL CLERK 1300
SELECT no,
DECODE(no, 1, deptno, 2, deptno) AS dept, --1,2일때만 deptno를 남겨
DECODE(no, 1, job) AS job,
sum_sal
FROM
( SELECT NVL(deptno, 0) AS deptno, job, SUM(sal) AS sum_sal
FROM emp
GROUP BY deptno, job ),
( SELECT rownum AS no
FROM emp
WHERE rownum<=3)
ORDER BY 1,2;
NO DEPT JOB SUM_SAL -- ---- --------- ------- 1 0 CLERK 1300 1 10 PRESIDENT 5000 1 10 MANAGER 2450 1 20 CLERK 800 1 20 ANALYST 3000 1 20 MANAGER 2975 1 30 CLERK 950 1 30 MANAGER 2850 1 30 SALESMAN 5600 2 0 NULL 1300 2 10 NULL 5000 2 10 NULL 2450 2 20 NULL 2975 2 20 NULL 800 2 20 NULL 3000 2 30 NULL 5600 2 30 NULL 950 2 30 NULL 2850 3 NULL NULL 1300 3 NULL NULL 2850 3 NULL NULL 5000 3 NULL NULL 950 3 NULL NULL 2975 3 NULL NULL 5600 3 NULL NULL 800 3 NULL NULL 2450 3 NULL NULL 3000
no 2 : 소계용
no 3 : 총계용
SELECT DECODE(no, 1, deptno, 2, deptno) AS dept, --1,2일때만 deptno를 남겨
DECODE(no, 1, job) AS job,
sum_sal
FROM
( SELECT NVL(deptno, 0) AS deptno, job, SUM(sal) AS sum_sal
FROM emp
GROUP BY deptno, job
ORDER BY 1,2 ),
( SELECT rownum AS no
FROM emp
WHERE rownum<=3);
DEPT JOB SUM_SAL
---- --------- -------
0 CLERK 1300
0 NULL 1300
NULL NULL 1300
10 MANAGER 2450
10 NULL 2450
NULL NULL 2450
10 PRESIDENT 5000
10 NULL 5000
NULL NULL 5000
20 ANALYST 3000
20 NULL 3000
NULL NULL 3000
20 CLERK 800
20 NULL 800
NULL NULL 800
20 MANAGER 2975
20 NULL 2975
NULL NULL 2975
30 CLERK 950
30 NULL 950
NULL NULL 950
30 MANAGER 2850
30 NULL 2850
NULL NULL 2850
30 SALESMAN 5600
30 NULL 5600
NULL NULL 5600
여기에 GROUP BY, ORDER BY 해주면 되겠지.
SELECT DECODE(no, 1, deptno, 2, deptno) AS dept,
DECODE(no, 1, job) AS job,
SUM(sum_sal)
FROM
( SELECT NVL(deptno, 0) AS deptno, job, SUM(sal) AS sum_sal
FROM emp
GROUP BY deptno, job ),
( SELECT rownum AS no
FROM emp
WHERE rownum<=3)
GROUP BY DECODE(no, 1, deptno, 2, deptno),
DECODE(no, 1, job)
ORDER BY 1,2;
DEPT JOB SUM(SUM_SAL)
---- --------- ------------
0 CLERK 1300
0 NULL 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 NULL 7450
20 ANALYST 3000
20 CLERK 800
20 MANAGER 2975
20 NULL 6775
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 NULL 9400
NULL NULL 24925
이걸 좀 더 쉽게 해보자..
rollup (9i이후)
SELECT deptno, job, SUM(sal) FROM emp GROUP BY ROLLUP(deptno, job);
DEPTNO JOB SUM(SAL)
------ --------- --------
NULL CLERK 1300
NULL NULL 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 NULL 7450
20 CLERK 800
20 ANALYST 3000
20 MANAGER 2975
20 NULL 6775
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 NULL 9400
NULL NULL 24925
grouping을 통해 확인할 수 있어
SELECT deptno, GROUPING(deptno),
job, GROUPING(job),
SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno, job);
DEPTNO GROUPING(DEPTNO) JOB GROUPING(JOB) SUM(SAL)
------ ---------------- --------- ------------- --------
NULL 0 CLERK 0 1300
NULL 0 NULL 1 1300
10 0 MANAGER 0 2450
10 0 PRESIDENT 0 5000
10 0 NULL 1 7450
20 0 CLERK 0 800
20 0 ANALYST 0 3000
20 0 MANAGER 0 2975
20 0 NULL 1 6775
30 0 CLERK 0 950
30 0 MANAGER 0 2850
30 0 SALESMAN 0 5600
30 0 NULL 1 9400
NULL 1 NULL 1 24925
cube
SELECT deptno, GROUPING(deptno),
job, GROUPING(job),
SUM(sal)
FROM emp
GROUP BY CUBE(deptno, job);
DEPTNO GROUPING(DEPTNO) JOB GROUPING(JOB) SUM(SAL)
------ ---------------- --------- ------------- --------
NULL 0 NULL 1 1300
NULL 1 NULL 1 24925
NULL 0 CLERK 0 1300
NULL 1 CLERK 0 3050
NULL 1 ANALYST 0 3000
NULL 1 MANAGER 0 8275
NULL 1 SALESMAN 0 5600
NULL 1 PRESIDENT 0 5000
10 0 NULL 1 7450
10 0 MANAGER 0 2450
10 0 PRESIDENT 0 5000
20 0 NULL 1 6775
20 0 CLERK 0 800
20 0 ANALYST 0 3000
20 0 MANAGER 0 2975
30 0 NULL 1 9400
30 0 CLERK 0 950
30 0 MANAGER 0 2850
30 0 SALESMAN 0 5600
grouping 값 확인하면서 순서주면 좋겠지
SELECT deptno, GROUPING(deptno) AS g1,
job, GROUPING(job) AS g2,
SUM(sal)
FROM emp
GROUP BY CUBE(deptno, job)
ORDER BY g1, g2;
DEPTNO G1 JOB G2 SUM(SAL)
------ -- --------- -- --------
10 0 MANAGER 0 2450
30 0 MANAGER 0 2850
30 0 CLERK 0 950
20 0 MANAGER 0 2975
20 0 ANALYST 0 3000
20 0 CLERK 0 800
10 0 PRESIDENT 0 5000
30 0 SALESMAN 0 5600
NULL 0 CLERK 0 1300
30 0 NULL 1 9400
10 0 NULL 1 7450
20 0 NULL 1 6775
NULL 0 NULL 1 1300
NULL 1 CLERK 0 3050
NULL 1 ANALYST 0 3000
NULL 1 SALESMAN 0 5600
NULL 1 PRESIDENT 0 5000
NULL 1 MANAGER 0 8275
NULL 1 NULL 1 24925
0-0 : 부서별-잡별 집계
0-1 : 부서별 집계
1-0 : 잡별 집계
1-1 : 총계
rollup : 부서별-잡별 집계. 순서가 중요해.
cube : 부서별-잡별, 잡별,부서별 집계 다 나와. 느려.. 순서가 중요하지 않겠지..
주로 rollup 쓰겠지
oraclejava 계정에서도 연습해보자.
SELECT department_id, job_id, salary FROM employees;
DEPARTMENT_ID JOB_ID SALARY
------------- ---------- ------
90 AD_PRES 24000
90 AD_VP 17000
90 AD_VP 17000
60 IT_PROG 9000
60 IT_PROG 6000
60 IT_PROG 4200
50 ST_MAN 5800
50 ST_CLERK 3500
50 ST_CLERK 3100
50 ST_CLERK 2600
50 ST_CLERK 2500
80 SA_MAN 10500
80 SA_REP 11000
80 SA_REP 8600
NULL SA_REP 7000
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
110 AC_MGR 12000
110 AC_ACCOUNT 8300
SELECT no, department_id, job_id, salary FROM ( SELECT NVL(department_id, 0) AS department_id, job_id, salary FROM employees), ( SELECT rownum AS no FROM employees WHERE rownum<=3);
NO DEPARTMENT_ID JOB_ID SALARY -- ------------- ---------- ------ 1 90 AD_PRES 24000 1 90 AD_VP 17000 1 90 AD_VP 17000 1 60 IT_PROG 9000 1 60 IT_PROG 6000 1 60 IT_PROG 4200 1 50 ST_MAN 5800 1 50 ST_CLERK 3500 1 50 ST_CLERK 3100 1 50 ST_CLERK 2600 1 50 ST_CLERK 2500 1 80 SA_MAN 10500 1 80 SA_REP 11000 1 80 SA_REP 8600 1 0 SA_REP 7000 1 10 AD_ASST 4400 1 20 MK_MAN 13000 1 20 MK_REP 6000 1 110 AC_MGR 12000 1 110 AC_ACCOUNT 8300 2 90 AD_PRES 24000 2 90 AD_VP 17000 2 90 AD_VP 17000 2 60 IT_PROG 9000 2 60 IT_PROG 6000 2 60 IT_PROG 4200 2 50 ST_MAN 5800 2 50 ST_CLERK 3500 2 50 ST_CLERK 3100 2 50 ST_CLERK 2600 2 50 ST_CLERK 2500 2 80 SA_MAN 10500 2 80 SA_REP 11000 2 80 SA_REP 8600 2 0 SA_REP 7000 2 10 AD_ASST 4400 2 20 MK_MAN 13000 2 20 MK_REP 6000 2 110 AC_MGR 12000 2 110 AC_ACCOUNT 8300 3 90 AD_PRES 24000 3 90 AD_VP 17000 3 90 AD_VP 17000 3 60 IT_PROG 9000 3 60 IT_PROG 6000 3 60 IT_PROG 4200 3 50 ST_MAN 5800 3 50 ST_CLERK 3500 3 50 ST_CLERK 3100 3 50 ST_CLERK 2600 3 50 ST_CLERK 2500 3 80 SA_MAN 10500 3 80 SA_REP 11000 3 80 SA_REP 8600 3 0 SA_REP 7000 3 10 AD_ASST 4400 3 20 MK_MAN 13000 3 20 MK_REP 6000 3 110 AC_MGR 12000 3 110 AC_ACCOUNT 8300
SELECT no,
DECODE(no, 1, department_id, 2, department_id) AS dept,
DECODE(no, 1,job_id) AS job,
salary
FROM
( SELECT NVL(department_id, 0) AS department_id, job_id, salary
FROM employees),
( SELECT rownum AS no
FROM employees
WHERE rownum<=3);
NO DEPT JOB SALARY -- ---- ---------- ------ 1 90 AD_PRES 24000 1 90 AD_VP 17000 1 90 AD_VP 17000 1 60 IT_PROG 9000 1 60 IT_PROG 6000 1 60 IT_PROG 4200 1 50 ST_MAN 5800 1 50 ST_CLERK 3500 1 50 ST_CLERK 3100 1 50 ST_CLERK 2600 1 50 ST_CLERK 2500 1 80 SA_MAN 10500 1 80 SA_REP 11000 1 80 SA_REP 8600 1 0 SA_REP 7000 1 10 AD_ASST 4400 1 20 MK_MAN 13000 1 20 MK_REP 6000 1 110 AC_MGR 12000 1 110 AC_ACCOUNT 8300 2 90 NULL 24000 2 90 NULL 17000 2 90 NULL 17000 2 60 NULL 9000 2 60 NULL 6000 2 60 NULL 4200 2 50 NULL 5800 2 50 NULL 3500 2 50 NULL 3100 2 50 NULL 2600 2 50 NULL 2500 2 80 NULL 10500 2 80 NULL 11000 2 80 NULL 8600 2 0 NULL 7000 2 10 NULL 4400 2 20 NULL 13000 2 20 NULL 6000 2 110 NULL 12000 2 110 NULL 8300 3 NULL NULL 24000 3 NULL NULL 17000 3 NULL NULL 17000 3 NULL NULL 9000 3 NULL NULL 6000 3 NULL NULL 4200 3 NULL NULL 5800 3 NULL NULL 3500 3 NULL NULL 3100 3 NULL NULL 2600 3 NULL NULL 2500 3 NULL NULL 10500 3 NULL NULL 11000 3 NULL NULL 8600 3 NULL NULL 7000 3 NULL NULL 4400 3 NULL NULL 13000 3 NULL NULL 6000 3 NULL NULL 12000 3 NULL NULL 8300
SELECT --no,
DECODE(no, 1, department_id, 2, department_id) AS dept,
DECODE(no, 1,job_id) AS job,
SUM(salary)
FROM
( SELECT NVL(department_id, 0) AS department_id, job_id, salary
FROM employees),
( SELECT rownum AS no
FROM employees
WHERE rownum<=3)
GROUP BY
DECODE(no, 1, department_id, 2, department_id),
DECODE(no, 1,job_id)
ORDER BY 1,2;
DEPT JOB SUM(SALARY)
---- ---------- -----------
0 SA_REP 7000
0 NULL 7000
10 AD_ASST 4400
10 NULL 4400
20 MK_MAN 13000
20 MK_REP 6000
20 NULL 19000
50 ST_CLERK 11700
50 ST_MAN 5800
50 NULL 17500
60 IT_PROG 19200
60 NULL 19200
80 SA_MAN 10500
80 SA_REP 19600
80 NULL 30100
90 AD_PRES 24000
90 AD_VP 34000
90 NULL 58000
110 AC_ACCOUNT 8300
110 AC_MGR 12000
110 NULL 20300
NULL NULL 175500
9i이후부터는 rollup을 사용하여 간단하게 할 수 있다고.
SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY ROLLUP(department_id, job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
NULL SA_REP 7000
NULL NULL 7000
10 AD_ASST 4400
10 NULL 4400
20 MK_MAN 13000
20 MK_REP 6000
20 NULL 19000
50 ST_MAN 5800
50 ST_CLERK 11700
50 NULL 17500
60 IT_PROG 19200
60 NULL 19200
80 SA_MAN 10500
80 SA_REP 19600
80 NULL 30100
90 AD_VP 34000
90 AD_PRES 24000
90 NULL 58000
110 AC_MGR 12000
110 AC_ACCOUNT 8300
110 NULL 20300
NULL NULL 175500
cube사용 : 부서별, job별 각각의 통계까지 가능
SELECT department_id, GROUPING(department_id) AS g1, job_id, GROUPING(job_id) AS g2, SUM(salary) FROM employees GROUP BY CUBE(department_id, job_id) ORDER BY g1, g2, 1, 3;
DEPARTMENT_ID G1 JOB_ID G2 SUM(SALARY)
------------- -- ---------- -- -----------
10 0 AD_ASST 0 4400
20 0 MK_MAN 0 13000
20 0 MK_REP 0 6000
50 0 ST_CLERK 0 11700
50 0 ST_MAN 0 5800
60 0 IT_PROG 0 19200
80 0 SA_MAN 0 10500
80 0 SA_REP 0 19600
90 0 AD_PRES 0 24000
90 0 AD_VP 0 34000
110 0 AC_ACCOUNT 0 8300
110 0 AC_MGR 0 12000
NULL 0 SA_REP 0 7000
10 0 NULL 1 4400
20 0 NULL 1 19000
50 0 NULL 1 17500
60 0 NULL 1 19200
80 0 NULL 1 30100
90 0 NULL 1 58000
110 0 NULL 1 20300
NULL 0 NULL 1 7000
NULL 1 AC_ACCOUNT 0 8300
NULL 1 AC_MGR 0 12000
NULL 1 AD_ASST 0 4400
NULL 1 AD_PRES 0 24000
NULL 1 AD_VP 0 34000
NULL 1 IT_PROG 0 19200
NULL 1 MK_MAN 0 13000
NULL 1 MK_REP 0 6000
NULL 1 SA_MAN 0 10500
NULL 1 SA_REP 0 26600
NULL 1 ST_CLERK 0 11700
NULL 1 ST_MAN 0 5800
NULL 1 NULL 1 175500
.
.
.
.
.