다중행 함수와 데이터 그룹화1

다중행 함수

1. SUM : 합계

2. COUNT : 데이터 개수

3. MAX, MIN

4. AVG : 평균

---

데이터 그룹화

1. GROUP BY

2. HAVING : GROUP BY절이 존재할 때만 사용 가능

※ HAVING절 과 WHERE절 차이

a. Having절

b. Where절

-> Where절이 Group by절, Having절보다 먼저 실행된다.

- ROLLUP 및 CUBE 시 사용될 테이블

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
198	Donald	OConnell	DOCONNEL	650.507.9833	21-Jun-07	SH_CLERK	2600	-	124	50
199	Douglas	Grant	DGRANT	650.507.9844	13-Jan-08	SH_CLERK	2600	-	124	50
200	Jennifer	Whalen	JWHALEN	515.123.4444	17-Sep-03	AD_ASST	4400	-	101	10
201	Michael	Hartstein	MHARTSTE	515.123.5555	17-Feb-04	MK_MAN	13000	-	100	20
202	Pat	Fay	PFAY	603.123.6666	17-Aug-05	MK_REP	6000	-	201	20
203	Susan	Mavris	SMAVRIS	515.123.7777	37414	HR_REP	6500	-	101	40
204	Hermann	Baer	HBAER	515.123.8888	37414	PR_REP	10000	-	101	10-Mar-00
205	Shelley	Higgins	SHIGGINS	515.123.8080	37414	AC_MGR	12008	-	101	19-Apr-00
206	William	Gietz	WGIETZ	515.123.8181	37414	AC_ACCOUNT	8300	-	205	19-Apr-00
100	Steven	King	SKING	515.123.4567	37789	AD_PRES	24000	-	-	30-Mar-00
101	Neena	Kochhar	NKOCHHAR	515.123.4568	38616	AD_VP	17000	-	100	30-Mar-00
102	Lex	De Haan	LDEHAAN	515.123.4569	36904	AD_VP	17000	-	100	30-Mar-00
103	Alexander	Hunold	AHUNOLD	590.423.4567	38720	IT_PROG	9000	-	102	29-Feb-00
104	Bruce	Ernst	BERNST	590.423.4568	39223	IT_PROG	6000	-	103	29-Feb-00
105	David	Austin	DAUSTIN	590.423.4569	38528	IT_PROG	4800	-	103	29-Feb-00
106	Valli	Pataballa	VPATABAL	590.423.4560	38753	IT_PROG	4800	-	103	29-Feb-00
107	Diana	Lorentz	DLORENTZ	590.423.5567	39120	IT_PROG	4200	-	103	29-Feb-00
108	Nancy	Greenberg	NGREENBE	515.124.4569	37485	FI_MGR	12008	-	101	09-Apr-00
109	Daniel	Faviet	DFAVIET	515.124.4169	37484	FI_ACCOUNT	9000	-	108	09-Apr-00
110	John	Chen	JCHEN	515.124.4269	38623	FI_ACCOUNT	8200	-	108	09-Apr-00
111	Ismael	Sciarra	ISCIARRA	515.124.4369	38625	FI_ACCOUNT	7700	-	108	09-Apr-00
112	Jose Manuel	Urman	JMURMAN	515.124.4469	38783	FI_ACCOUNT	7800	-	108	09-Apr-00
113	Luis	Popp	LPOPP	515.124.4567	39423	FI_ACCOUNT	6900	-	108	09-Apr-00
114	Den	Raphaely	DRAPHEAL	515.127.4561	37597	PU_MAN	11000	-	100	30-Jan-00
115	Alexander	Khoo	AKHOO	515.127.4562	37759	PU_CLERK	3100	-	114	30-Jan-00
116	Shelli	Baida	SBAIDA	515.127.4563	38710	PU_CLERK	2900	-	114	30-Jan-00
117	Sigal	Tobias	STOBIAS	515.127.4564	38557	PU_CLERK	2800	-	114	30-Jan-00
118	Guy	Himuro	GHIMURO	515.127.4565	39036	PU_CLERK	2600	-	114	30-Jan-00
119	Karen	Colmenares	KCOLMENA	515.127.4566	39304	PU_CLERK	2500	-	114	30-Jan-00
120	Matthew	Weiss	MWEISS	650.123.1234	38186	ST_MAN	8000	-	100	19-Feb-00
121	Adam	Fripp	AFRIPP	650.123.2234	38452	ST_MAN	8200	-	100	19-Feb-00
122	Payam	Kaufling	PKAUFLIN	650.123.3234	37742	ST_MAN	7900	-	100	19-Feb-00
123	Shanta	Vollman	SVOLLMAN	650.123.4234	38635	ST_MAN	6500	-	100	19-Feb-00
124	Kevin	Mourgos	KMOURGOS	650.123.5234	39402	ST_MAN	5800	-	100	19-Feb-00
125	Julia	Nayer	JNAYER	650.124.1214	38549	ST_CLERK	3200	-	120	19-Feb-00
126	Irene	Mikkilineni	IMIKKILI	650.124.1224	38988	ST_CLERK	2700	-	120	19-Feb-00
127	James	Landry	JLANDRY	650.124.1334	39096	ST_CLERK	2400	-	120	19-Feb-00
128	Steven	Markle	SMARKLE	650.124.1434	39515	ST_CLERK	2200	-	120	19-Feb-00
129	Laura	Bissot	LBISSOT	650.124.5234	38584	ST_CLERK	3300	-	121	19-Feb-00
130	Mozhe	Atkinson	MATKINSO	650.124.6234	38655	ST_CLERK	2800	-	121	19-Feb-00
131	James	Marlow	JAMRLOW	650.124.7234	38399	ST_CLERK	2500	-	121	19-Feb-00
132	TJ	Olson	TJOLSON	650.124.8234	39182	ST_CLERK	2100	-	121	19-Feb-00
133	Jason	Mallin	JMALLIN	650.127.1934	38152	ST_CLERK	3300	-	122	19-Feb-00
134	Michael	Rogers	MROGERS	650.127.1834	38955	ST_CLERK	2900	-	122	19-Feb-00
135	Ki	Gee	KGEE	650.127.1734	39428	ST_CLERK	2400	-	122	19-Feb-00
136	Hazel	Philtanker	HPHILTAN	650.127.1634	39484	ST_CLERK	2200	-	122	19-Feb-00
137	Renske	Ladwig	RLADWIG	650.121.1234	37816	ST_CLERK	3600	-	123	19-Feb-00
138	Stephen	Stiles	SSTILES	650.121.2034	38651	ST_CLERK	3200	-	123	19-Feb-00
139	John	Seo	JSEO	650.121.2019	38760	ST_CLERK	2700	-	123	19-Feb-00
140	Joshua	Patel	JPATEL	650.121.1834	38813	ST_CLERK	2500	-	123	19-Feb-00

3. ROLLUP

예시)

결과)

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	ROUND(AVG(SALARY),0)
10	AD_ASST	4400	4400	4400
20	MK_MAN	13000	13000	13000
20	MK_REP	6000	6000	6000
30	PU_CLERK	3100	13900	2780
30	PU_MAN	11000	11000	11000
40	HR_REP	6500	6500	6500
50	SH_CLERK	4200	64300	3215
50	ST_CLERK	3600	55700	2785
50	ST_MAN	8200	36400	7280
60	IT_PROG	9000	28800	5760
70	PR_REP	10000	10000	10000
80	SA_MAN	14000	61000	12200
80	SA_REP	11500	243500	8397
90	AD_PRES	24000	24000	24000
90	AD_VP	17000	34000	17000
100	FI_ACCOUNT	9000	39600	7920
100	FI_MGR	12008	12008	12008
110	AC_ACCOUNT	8300	8300	8300
110	AC_MGR	12008	12008	12008
-	SA_REP	7000	7000	7000

예시2)

결과)

ROLLUP 함수는 명시한 열을 소그룹부터 대그룹의 순서로 각 그룹별 결과를 출력하고 마지막에 총 데이터의 결과를 출력

즉 각 부서의 월급의 최고, 합, 평균값 그리고 전체 데이터 대상으로 월급의 최고, 합, 평균값을 출력합니다

-> ROLLUP함수에 명시한 열에 한하여 결과가 출력

-> ROLLUP 함수에는 그룹 함수를 지정할 수 없는 것

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	ROUND(AVG(SALARY),0)
-	SA_REP	7000	7000	7000
-	-	7000	7000	7000
10	AD_ASST	4400	4400	4400
10	-	4400	4400	4400
20	MK_MAN	13000	13000	13000
20	MK_REP	6000	6000	6000
20	-	13000	19000	9500
30	PU_MAN	11000	11000	11000
30	PU_CLERK	3100	13900	2780
30	-	11000	24900	4150
40	HR_REP	6500	6500	6500
40	-	6500	6500	6500
50	ST_MAN	8200	36400	7280
50	SH_CLERK	4200	64300	3215
50	ST_CLERK	3600	55700	2785
50	-	8200	156400	3476
60	IT_PROG	9000	28800	5760
60	-	9000	28800	5760
70	PR_REP	10000	10000	10000
70	-	10000	10000	10000
80	SA_MAN	14000	61000	12200
80	SA_REP	11500	243500	8397
80	-	14000	304500	8956
90	AD_VP	17000	34000	17000
90	AD_PRES	24000	24000	24000
90	-	24000	58000	19333
100	FI_MGR	12008	12008	12008
100	FI_ACCOUNT	9000	39600	7920
100	-	12008	51608	8601
110	AC_MGR	12008	12008	12008
110	AC_ACCOUNT	8300	8300	8300
110	-	12008	20308	10154
-	-	24000	691416	6462

4. CUBE

결과)

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	ROUND(AVG(SALARY),0)
10	AD_ASST	4400	4400	4400
10	-	4400	4400	4400
20	MK_MAN	13000	13000	13000
20	MK_REP	6000	6000	6000
20	-	13000	19000	9500
30	PU_CLERK	3100	13900	2780
30	PU_MAN	11000	11000	11000
30	-	11000	24900	4150
40	HR_REP	6500	6500	6500
40	-	6500	6500	6500
50	SH_CLERK	4200	64300	3215
50	ST_CLERK	3600	55700	2785
50	ST_MAN	8200	36400	7280
50	-	8200	156400	3476
60	IT_PROG	9000	28800	5760
60	-	9000	28800	5760
70	PR_REP	10000	10000	10000
70	-	10000	10000	10000
80	SA_MAN	14000	61000	12200
80	SA_REP	11500	243500	8397
80	-	14000	304500	8956
90	AD_PRES	24000	24000	24000
90	AD_VP	17000	34000	17000
90	-	24000	58000	19333
100	FI_ACCOUNT	9000	39600	7920
100	FI_MGR	12008	12008	12008
100	-	12008	51608	8601
110	AC_ACCOUNT	8300	8300	8300
110	AC_MGR	12008	12008	12008
110	-	12008	20308	10154
-	AC_ACCOUNT	8300	8300	8300
-	AC_MGR	12008	12008	12008
-	AD_ASST	4400	4400	4400
-	AD_PRES	24000	24000	24000
-	AD_VP	17000	34000	17000
-	FI_ACCOUNT	9000	39600	7920
-	FI_MGR	12008	12008	12008
-	HR_REP	6500	6500	6500
-	IT_PROG	9000	28800	5760
-	MK_MAN	13000	13000	13000
-	MK_REP	6000	6000	6000
-	PR_REP	10000	10000	10000
-	PU_CLERK	3100	13900	2780
-	PU_MAN	11000	11000	11000
-	SA_MAN	14000	61000	12200
-	SA_REP	7000	7000	7000
-	SA_REP	11500	250500	8350
-	SH_CLERK	4200	64300	3215
-	ST_CLERK	3600	55700	2785
-	ST_MAN	8200	36400	7280

CUBE 함수는 ROLLUP 함수를 사용했을 때보다 더 많은 정보가 나옵니다.

CUBE 함수는 지정한 모든 열에서 가능한 조합의 결과를 모두 출력합니다.

정리하자면 ROLLUP함수는 지정한 열 수에 따라 다음과 같이 결과 값이 조합됩니다.

ROLLUP(A,B,C) 1. A그룹별 B그룹별 C그룹별 해당하는 결과 출력 2. A그룹별 B그룹에 해당하는 결과 출력 3. A그룹에 해당하는 결과 출력 4. 전체 데이터 결과 출력

이번에는 CBUE함수는 아래와 같이 조합됩니다.

CUBE(A,B,C) 1. A그룹별 B그룹별 C그룹별에 해당하는 결과 출력 2. A그룹 B그룹의 결과 출력 3. B그룹 C그룹의 결과 출력 4. A그룹 C그룹의 결과 출력 5. A그룹 결과 6. B그룹 결과 7. C그룹 결과 8. 전체 데이터 결과

CUBE는 N을 넣으면 $2^(N)$개가 나옵니다.

그러므로 그룹화 열 중 일부만을 지정할수 있습니다. 이를 Partial Rollup/Cube라고 합니다. 부분ROLLUP, CUBE라고도 한다.

결과)

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	ROUND(AVG(SALARY),0)
-	SA_REP	7000	7000	7000
-	-	7000	7000	7000
10	AD_ASST	4400	4400	4400
10	-	4400	4400	4400
20	MK_MAN	13000	13000	13000
20	MK_REP	6000	6000	6000
20	-	13000	19000	9500
30	PU_MAN	11000	11000	11000
30	PU_CLERK	3100	13900	2780
30	-	11000	24900	4150
40	HR_REP	6500	6500	6500
40	-	6500	6500	6500
50	ST_MAN	8200	36400	7280
50	SH_CLERK	4200	64300	3215
50	ST_CLERK	3600	55700	2785
50	-	8200	156400	3476
60	IT_PROG	9000	28800	5760
60	-	9000	28800	5760
70	PR_REP	10000	10000	10000
70	-	10000	10000	10000
80	SA_MAN	14000	61000	12200
80	SA_REP	11500	243500	8397
80	-	14000	304500	8956
90	AD_VP	17000	34000	17000
90	AD_PRES	24000	24000	24000
90	-	24000	58000	19333
100	FI_MGR	12008	12008	12008
100	FI_ACCOUNT	9000	39600	7920
100	-	12008	51608	8601
110	AC_MGR	12008	12008	12008
110	AC_ACCOUNT	8300	8300	8300
110	-	12008	20308	10154

결과)

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	ROUND(AVG(SALARY),0)
90	AD_VP	17000	34000	17000
-	AD_VP	17000	34000	17000
110	AC_MGR	12008	12008	12008
-	AC_MGR	12008	12008	12008
100	FI_MGR	12008	12008	12008
-	FI_MGR	12008	12008	12008
40	HR_REP	6500	6500	6500
-	HR_REP	6500	6500	6500
20	MK_MAN	13000	13000	13000
-	MK_MAN	13000	13000	13000
20	MK_REP	6000	6000	6000
-	MK_REP	6000	6000	6000
70	PR_REP	10000	10000	10000
-	PR_REP	10000	10000	10000
30	PU_MAN	11000	11000	11000
-	PU_MAN	11000	11000	11000
80	SA_MAN	14000	61000	12200
-	SA_MAN	14000	61000	12200
-	SA_REP	7000	7000	7000
80	SA_REP	11500	243500	8397
-	SA_REP	11500	250500	8350
50	ST_MAN	8200	36400	7280
-	ST_MAN	8200	36400	7280
10	AD_ASST	4400	4400	4400
-	AD_ASST	4400	4400	4400
90	AD_PRES	24000	24000	24000
-	AD_PRES	24000	24000	24000
60	IT_PROG	9000	28800	5760
-	IT_PROG	9000	28800	5760
30	PU_CLERK	3100	13900	2780
-	PU_CLERK	3100	13900	2780
50	SH_CLERK	4200	64300	3215
-	SH_CLERK	4200	64300	3215
50	ST_CLERK	3600	55700	2785
-	ST_CLERK	3600	55700	2785
110	AC_ACCOUNT	8300	8300	8300
-	AC_ACCOUNT	8300	8300	8300
100	FI_ACCOUNT	9000	39600	7920
-	FI_ACCOUNT	9000	39600	7920

GROUPING SETS 함수

- 같은 수준의 그룹화 열이 여러 개일 때 각 열별 그룹화를 통해 결과 값을 출력하는데 사용

결과)

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	ROUND(AVG(SALARY),0)
10	-	4400	4400	4400
20	-	13000	19000	9500
30	-	11000	24900	4150
40	-	6500	6500	6500
50	-	8200	156400	3476
60	-	9000	28800	5760
70	-	10000	10000	10000
80	-	14000	304500	8956
90	-	24000	58000	19333
100	-	12008	51608	8601
110	-	12008	20308	10154
-	AC_ACCOUNT	8300	8300	8300
-	AC_MGR	12008	12008	12008
-	AD_ASST	4400	4400	4400
-	AD_PRES	24000	24000	24000
-	AD_VP	17000	34000	17000
-	FI_ACCOUNT	9000	39600	7920
-	FI_MGR	12008	12008	12008
-	HR_REP	6500	6500	6500
-	IT_PROG	9000	28800	5760
-	MK_MAN	13000	13000	13000
-	MK_REP	6000	6000	6000
-	PR_REP	10000	10000	10000
-	PU_CLERK	3100	13900	2780
-	PU_MAN	11000	11000	11000
-	SA_MAN	14000	61000	12200
-	SA_REP	11500	250500	8350
-	SH_CLERK	4200	64300	3215
-	ST_CLERK	3600	55700	2785
-	ST_MAN	8200	36400	7280
-	-	7000	7000	7000

그룹화 함수

- 데이터 자체의 가공이나 특별한 연산 기능을 수행하지는 않지만 그룹화 데이터의 식별이 쉽고 가독성이 높이기 위한 목적

1. GROUPING 함수

- ROLLUP 또는 CUBE 함수를 사용한 GROUP BY절에 그룹화 대상으로 지정한 열이 그룹화된 상태로 결과가 집계되었는지 확인하는데 사용

결과)

0은 GROUPING 함수에 지정한 열이 그룹화되었음을 의미

0 1 은 DEPARTMENT_ID에는 그룹화되어있고 JOB_ID는 그룹화되어있지 않음

1 0 은 반대

1 1 은 DEPARTMENT_ID는 그룹화, JOB_ID 또한 그룹화 되어있다는 뜻임, 즉 모두 그룹화하여 집계한 결과

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	평균	GROUPING(DEPARTMENT_ID)	GROUPING(JOB_ID)
10	AD_ASST	4400	4400	4400	0	0
10	-	4400	4400	4400	0	1
20	MK_MAN	13000	13000	13000	0	0
20	MK_REP	6000	6000	6000	0	0
20	-	13000	19000	9500	0	1
30	PU_CLERK	3100	13900	2780	0	0
30	PU_MAN	11000	11000	11000	0	0
30	-	11000	24900	4150	0	1
40	HR_REP	6500	6500	6500	0	0
40	-	6500	6500	6500	0	1
50	SH_CLERK	4200	64300	3215	0	0
50	ST_CLERK	3600	55700	2785	0	0
50	ST_MAN	8200	36400	7280	0	0
50	-	8200	156400	3476	0	1
60	IT_PROG	9000	28800	5760	0	0
60	-	9000	28800	5760	0	1
70	PR_REP	10000	10000	10000	0	0
70	-	10000	10000	10000	0	1
80	SA_MAN	14000	61000	12200	0	0
80	SA_REP	11500	243500	8397	0	0
80	-	14000	304500	8956	0	1
90	AD_PRES	24000	24000	24000	0	0
90	AD_VP	17000	34000	17000	0	0
90	-	24000	58000	19333	0	1
100	FI_ACCOUNT	9000	39600	7920	0	0
100	FI_MGR	12008	12008	12008	0	0
100	-	12008	51608	8601	0	1
110	AC_ACCOUNT	8300	8300	8300	0	0
110	AC_MGR	12008	12008	12008	0	0
110	-	12008	20308	10154	0	1
-	SA_REP	7000	7000	7000	0	0
-	-	7000	7000	7000	0	1
-	-	24000	691416	6462	1	1

2. GROUPING_ID

- GROUPING함수와 달리 한 번에 여러 열을 지정할 수 있음

결과)

GROUPING_ID는 아래와 같이 출력 결과를 알수 있다.

그룹화 된 열	그룹화 비트 벡터	최종 결과
a, b	0 0	0
a	0 1	1
b	1 0	2
없음	1 1	3

DEPARTMENT_ID	JOB_ID	MAX(SALARY)	SUM(SALARY)	평균	GROUPING(DEPARTMENT_ID)	GROUPING(JOB_ID)	GROUPING_ID(DEPARTMENT_ID,JOB_ID)
10	AD_ASST	4400	4400	4400	0	0	0
10	-	4400	4400	4400	0	1	1
20	MK_MAN	13000	13000	13000	0	0	0
20	MK_REP	6000	6000	6000	0	0	0
20	-	13000	19000	9500	0	1	1
30	PU_CLERK	3100	13900	2780	0	0	0
30	PU_MAN	11000	11000	11000	0	0	0
30	-	11000	24900	4150	0	1	1
40	HR_REP	6500	6500	6500	0	0	0
40	-	6500	6500	6500	0	1	1
50	SH_CLERK	4200	64300	3215	0	0	0
50	ST_CLERK	3600	55700	2785	0	0	0
50	ST_MAN	8200	36400	7280	0	0	0
50	-	8200	156400	3476	0	1	1
60	IT_PROG	9000	28800	5760	0	0	0
60	-	9000	28800	5760	0	1	1
70	PR_REP	10000	10000	10000	0	0	0
70	-	10000	10000	10000	0	1	1
80	SA_MAN	14000	61000	12200	0	0	0
80	SA_REP	11500	243500	8397	0	0	0
80	-	14000	304500	8956	0	1	1
90	AD_PRES	24000	24000	24000	0	0	0
90	AD_VP	17000	34000	17000	0	0	0
90	-	24000	58000	19333	0	1	1
100	FI_ACCOUNT	9000	39600	7920	0	0	0
100	FI_MGR	12008	12008	12008	0	0	0
100	-	12008	51608	8601	0	1	1
110	AC_ACCOUNT	8300	8300	8300	0	0	0
110	AC_MGR	12008	12008	12008	0	0	0
110	-	12008	20308	10154	0	1	1
-	SA_REP	7000	7000	7000	0	0	0
-	-	7000	7000	7000	0	1	1
-	-	24000	691416	6462	1	1	3