다양한예제로쉽게배우는 오라클 SQL 과 PL/SQL 서진수저
3 장 SQL 복수행함수 ( 그룹함수 ) 를배웁니다 1
함수이름 의 미 사용예 COUNT 입력되는데이터들의건수를출력 COUNT(sal) SUM 입력되는데이터들의합계값을출력 SUM(sal) AVG 입력되는데이터들의평균값을출력 AVG(sal) MAX 입력되는데이터들중최고값을출력 MAX(sal) MIN 입력되는데이터들중최저값을출력 MIN(sal) STDDEV 입력되는데이터값들의표준편차값출력 STDDEV(sal) VARIANCE 입력되는데이터값들의분산값출력 VARIANCE(sal) ROLLUP 입력되는데이터들의소계값을자동으로계산해서출력아래예참조 CUBE 입력되는데이터들의소계및전체총계를자동계산후출력아래예참조 GROUPING 해당칼럼이그룹에사용되었는지여부를 1 또는 0 으로반환아래예참조 GROUPINGSET 한번의질의로여러개의그룹화가능아래예참조 LISTAGG PIVOT LAG LEAD RANK DENSE_RANK 누계집계하기 아래예참조 아래예참조 아래예참조 아래예참조 아래예참조 아래예참조 아래예참조 2
1) COUNT 함수 - 입력되는데이터의총건수를반환합니다. COUNT(*) 의결과는 Null 값을포함한결과이고 COUNT(hpage) 의결과는 Null 값을제외한결과입니다. 3
2) SUM 함수 - 입력된데이터들의합계값을구하는함수입니다. 4
3) AVG 함수 - 입력된값들의평균값을구해주는함수입니다. NULL 값자동제외. 틀린결과 NULL 값을 0 으로변환. 바른결과 5
4) MAX 함수 / MIN 함수 - 속도와성능부분에서문제가될수있으므로인덱스를활용하는방법을 사용할것을적극권장함 6
5) STDDEV 함수 / VARIANCE 함수 - STDDEV 함수는표준편차를구하는함수이고 VARIANCE 함수는분산을 구하는함수입니다. 7
2. 특정조건으로세부적인그룹화하기 (GROUP BY 절사용하기 ) 8
- Professor 테이블에서학과별로교수들의평균급여를출력하세요. 9
- Professor 테이블에서학과별, 직급별로교수들의평균급여를출력하세요. 10
- GROUP BY 절사용시주의사항 1. SELECT 절에사용된그룹함수이외의칼럼이나표현식은반드시 GROUP BY 절에사용되어야합니다. 그렇지않을경우아래와같은에러가발생합니다. 11
2. GROUP BY 절에사용된칼럼은 SELECT 절에사용되지않아도됩니다. 12
3. GROUP BY 절에는반드시칼럼명이사용되어야하며칼럼 Alias 는사용하면안됩니다. 13
3. 조건을주고검색하기 (HAVING 절사용하기 ) 14
- 평균급여가 450 이상인부서의부서번호와평균급여를구하세요. 15
4. 자동으로소계 / 합계를구해주는함수 16
6) ROLLUP 함수 자동으로소계값을구해주는함수 이부분들이 ROLLUP 에의해자동으로구해진소계부분입니다. 17
18
7) CUBE 함수 - ROLLUP 함수와같이각소계도출력하고전체총계까지출력합니다. 이부분이 ROLLUP 함수와다른전체총계출력부분입니다 19
20
5. 다른그룹핑관련함수들살펴보기 21
1) GROUPING 함수 그루핑작업에사용유무를확인하는함수 이예제는부서별로급여합계를구하는쿼리입니다. 이쿼리에서 deptno 컬럼이그룹핑하는데사용되었는지살펴보기위해 grouping 함수를사용했는데가장마지막합계부분만 1 로사용되지않았고나머지는모두 0 으로사용되었음을확인할수있습니다. 22
이예는두개의컬럼을그룹핑하면서각컬럼의사용유무를확인했습니다. G_DEPTNO 컬럼은모두그룹핑하는데사용되었고 G_POSITION 컬럼은각부서별소계값을구할때는그룹핑에사용되지않았음을보여줍니다. 당연히부서별소계값을구하는것이니직급컬럼은사용되지않을것입니다. 23
2) GROUPING_ID 함수 GROUPING 컬럼 BIT GROUPING 결과 의 미 A, B 0 0 0 두컬럼다 GROUPING 에사용됨 A 0 1 1 A 컬럼만 GROUPING 에사용됨 B 1 0 2 B 컬럼만 GROUPING 에사용됨 - 1 1 3 두컬럼모두사용안됨 24
위화면을보면 GDP 부분에 0 으로되어있는건두컬럼모두그룹핑에사용되었다는뜻이고 1 인컬럼은 01 비트란의미이므로 deptno 는그룹핑에사용되었으나 position 은사용되지않았다라는의미입니다. 이렇게여러개의컬럼이있을경우 GROUPING_ID 를활용하여보다간편하게조회할수있습니다. 25
3) GROUPING SETS 26
4) LISTAGG 함수 (11g 에서추가됨 ) 27
28
5) PIVOT 함수 (11g 버전에서추가된함수 ) PIVOT 기능을사용하지않고출력 29
PIVOT 절에 MAX(num_day) 절은 DECODE 문장에서사용되는함수를적으면되고 FOR 절에는화면에집계될그룹핑할칼럼을적으면됩니다. 30
3 EMP 테이블에서부서별로각직급별인원이몇명인지계산해서출력하세요 - DECODE 함수를이용하는방법 31
3 EMP 테이블에서부서별로각직급별인원이몇명인지계산해서출력하세요 - PIVOT 함수를이용하는방법 32
- PIVOT 부분에조건을여러개사용하기 33
6) UNPIVOT 함수 PIVOT 테이블생성 34
35
6. 그룹함수연습문제 1) Professor 테이블을사용하여교수중에서급여 (Pay) 와보너스 (bonus) 를합친금액이가장많은경우와가장적은경우, 평균금액을구하세요. 단보너스가없을경우는보너스를 0 으로계산하고출력금액은모두소수점첫째자리까지만나오게하세요. 36
2) Professor 테이블을사용하여교수중에서급여 (Pay) 와보너스 (bonus) 를합친금액이가장많은경우와가장적은경우, 평균금액을구하세요. 단보너스가없을경우는급여를 0 으로계산하고출력금액은모두소수점첫째자리까지만나오게하세요. 37
3) Student 테이블의 birthday 칼럼을사용하여아래화면처럼월별로태어난인원수를출력하세요. 38
4) Student 테이블의 tel 칼럼을참고하여아래와같이지역별인원수를출력하세요. 단 02 서울, 031 경기, 051 부산, 052 울산, 053 대구, 055 경남으로출력하세요 39
5) Emp 테이블을사용하여아래의화면과같이부서별로직급별로급여합계결과를출력하세요. 먼저아래의두건의데이터를입력하신후작업하세요. SQL>insert into emp (empno, deptno, ename, sal) 2 values (1000,10,' 홍길동 ',3600) ; SQL> insert into emp (empno, deptno, ename, sal) 2 values (2000,30,' 일지매 ',3000); SQL> commit; 40
6) Professor 테이블의교수번호와이름을아래의예시화면형태로출력하세요. 41
7. 그외주요그룹함수 42
1) LAG 함수 : 이전행값을가져올때사용하는함수입니다. 문법 : LAG( 출력할컬럼명, OFFSET, 기본출력값 ) OVER (Query_partition 구문, ORDER BY 정렬할컬럼 ) 43
2) LEAD 함수 LEAD 함수는 LAG 함수와반대로이후의값을가져오는함수입니다. 44
3) RANK 함수 순위출력함수 RANK( 조건값 ) WITHIN GROUP (ORDER BY 조건값컬럼명 [ASC DESC] ) - 사용예 : 이름이 송도권 인교수의순위를조회하세요. 45
3) RANK 함수 집계용 RANK ( ) (ORDER BY 조건컬럼명 [ASC DESC] ) 1 교수테이블 ( professor ) 테이블에서교수들의교수번호와이름, 급여, 급여순위를출력하세요. 46
2 Emp 테이블에서 10 번부서에속한직원들의사번과이름, 급여, 해당부서내 의급여순위를출력하세요. 47
3 emp 테이블을사용하여사번, 이름, 급여, 부서번호, 부서별급여순위를출력하세요. 48
4 emp 테이블을사용하여사번, 이름, 급여, 부서번호, 부서내 job 별로급여순위 를출력하세요 49
4) 누적합계구하기 1 panmae 테이블을사용하여 1000 번대리점의판매내역을출력하되판매일자, 제품코드, 판매량, 누적판매금액을아래와같이출력하세요. 50
2 panmae 테이블을사용하여 1000 번대리점의판매내역을판매일자별로분류 하고같은일자일경우제품코드별로한번더분류한후판매일자, 제품코드, 판매 량, 판매금액, 누적판매금액을아래와같이출력하세요. 51