생 능 _ 오라클 SQL _ 03 장 . SQL 복 수행 함수 – Lecture Note 
 생 능 _ 오라클 SQL _ 03 장 . SQL 복 수행 함수 
 GROUP 함수 
 emp 테이블 의 내용 
 COUNT ( ) , SUM ( ) , AVG ( ) 함수 
 COUNT ( COMM ) SUM ( COMM ) AVG ( COMM ) 
 4 2200 550 
 MAX ( ) , MIN ( ) 함수 
 87 / 04 / 19 80 / 12 / 17 
 STDDEV ( ) , VARIANCE ( ) 함수 
 STDEV VAR 
 1195 . 75907 1429839 . 74 
 GROUP BY ~ HAVING 절 사용 
 DEPTNO AVG 
 20 2443 . 75 
 DEPTNO JOB AVG 
 20 CLERK 800 
 30 SALESMAN 1400 
 SELECT 절 에 사용 된 그룹 함수 이외 의 컬럼 이나 표현식 은 반드시 GROUP BY 절 에 사용 되 어야 함 , 그렇 지 않 을 경우 에러 발생 
 GROUP BY 절 에 는 반드시 컬럼 명 이 사용 되 어야 하 며 컬럼 Alias 는 사용 하 면 안 됨 
 그룹 핑 한 조건 으로 검색 하 기 ( HAVING 절 사용 하 기 ) 
 ROLLUP ( ) 함수 : 엑셀 의 부 분합 , deptno , job 그룹 , 전체 그룹 
 DEPTNO JOB AVG CNT 
 10 CLERK 1300 1 
 10 MANAGER 2450 1 
 10 PRESIDENT 5000 1 
 10 2916 . 66667 3 
 20 CLERK 800 1 
 20 ANALYST 3000 2 
 20 MANAGER 2975 1 
 20 2443 . 75 4 
 30 CLERK 950 1 
 30 MANAGER 2850 1 
 30 SALESMAN 1400 4 
 30 1566 . 66667 6 
 2148 . 07692 13 
 GROUPING SETS ( ) 함수 : 여러 개 의 GROUP BY 를 동시 에 실행 
 GRADE DEPTNO 1 COUNT ( *) 
 GRADE DEPTNO 1 COUNT ( *) AVG ( HEIGHT ) AVG ( WEIGHT ) 
 102 4 170 . 75 64 . 25 
 201 6 172 . 833333 67 
 301 2 172 60 
 101 4 172 60 
 202 2 179 . 5 62 . 5 
 103 2 165 . 5 51 . 5 
 1 5 170 . 4 62 . 4 
 2 5 175 . 6 67 . 4 
 4 5 175 . 8 68 . 2 
 3 5 166 . 6 51 . 4 
 LISTAGG ( ) 함수 
 10 CLARK -> KING -> MILLER 
 20 SMITH -> JONES -> FORD -> SCOTT 
 30 ALLEN -> WARD -> BLAKE -> TURNER -> MARTIN -> JAMES 
 PIVOT ( ) 함수 : 엑셀 의 피벗 테이블 
 SQL > SELECT deptno , COUNT ( DECODE ( job , ' CLERK ', ' 0 ')) AS clerk 
 2 , COUNT ( DECODE ( job , ' MANAGER ', ' 0 ')) AS manager 
 3 , COUNT ( DECODE ( job , ' PRESIDENT ', ' 0 ')) AS president 
 4 , COUNT ( DECODE ( job , ' ANALYST ', ' 0 ')) AS analyst 
 5 , COUNT ( DECODE ( job , ' SALESMAN ', ' 0 ')) AS salesman 
 7 GROUP BY deptno 
 DEPTNO CLERK MANAGER PRESIDENT ANALYST SALESMAN 
 30 1 1 0 0 4 
 SQL > SELECT deptno , COUNT ( DECODE ( job , ' CLERK ', ' 1 ')) AS clerk 
 2 , COUNT ( DECODE ( job , ' MANAGER ', ' 1 ')) AS manager 
 3 , COUNT ( DECODE ( job , ' PRESIDENT ', ' 1 ')) AS president 
 4 , COUNT ( DECODE ( job , ' ANALYST ', ' 1 ')) AS analyst 
 5 , COUNT ( DECODE ( job , ' SALESMAN ', ' 1 ')) AS salesman 
 SQL > SELECT deptno , SUM ( DECODE ( job , ' CLERK ', ' 1 ')) AS clerk 
 2 , SUM ( DECODE ( job , ' MANAGER ', ' 1 ')) AS manager 
 3 , SUM ( DECODE ( job , ' PRESIDENT ', ' 1 ')) AS president 
 4 , SUM ( DECODE ( job , ' ANALYST ', ' 1 ')) AS analyst 
 5 , SUM ( DECODE ( job , ' SALESMAN ', ' 1 ')) AS salesman 
 UNPIVOT ( ) 함수 
 SQL > CREATE TABLE upivot AS 
 2 SELECT deptno , COUNT ( DECODE ( job , ' CLERK ', ' 0 ')) AS clerk 
 3 , COUNT ( DECODE ( job , ' MANAGER ', ' 0 ')) AS manager 
 4 , COUNT ( DECODE ( job , ' PRESIDENT ', ' 0 ')) AS president 
 5 , COUNT ( DECODE ( job , ' ANALYST ', ' 0 ')) AS analyst 
 6 , COUNT ( DECODE ( job , ' SALESMAN ', ' 0 ')) AS salesman 
 8 GROUP BY deptno 
 DEPTNO JOB EMPNO 
 10 PRESIDENT 1 
 10 ANALYST 0 
 10 SALESMAN 0 
 20 CLERK 1 
 20 PRESIDENT 0 
 20 SALESMAN 0 
 30 PRESIDENT 0 
 30 ANALYST 0 
 LAG ( 컬럼 , offset , 기본 값 ) OVER ( ORDER BY 정렬 할 컬럼 ) 함수 : 앞 ROW 값 사용 
 ENAME HIREDATE SAL LAG 
 SMITH 80 / 12 / 17 800 0 
 ALLEN 81 / 02 / 20 1600 800 
 WARD 81 / 02 / 22 1250 1600 
 JONES 81 / 04 / 02 2975 1250 
 BLAKE 81 / 05 / 01 2850 2975 
 CLARK 81 / 06 / 09 2450 2850 
 TURNER 81 / 09 / 08 1500 2450 
 MARTIN 81 / 09 / 28 1250 1500 
 KING 81 / 11 / 17 5000 1250 
 JAMES 81 / 12 / 03 950 5000 
 FORD 81 / 12 / 03 3000 950 
 MILLER 82 / 01 / 23 1300 3000 
 SCOTT 87 / 04 / 19 3000 1300 
 ALLEN 81 / 02 / 20 1600 0 
 WARD 81 / 02 / 22 1250 800 
 JONES 81 / 04 / 02 2975 1600 
 BLAKE 81 / 05 / 01 2850 1250 
 CLARK 81 / 06 / 09 2450 2975 
 TURNER 81 / 09 / 08 1500 2850 
 MARTIN 81 / 09 / 28 1250 2450 
 KING 81 / 11 / 17 5000 1500 
 JAMES 81 / 12 / 03 950 1250 
 FORD 81 / 12 / 03 3000 5000 
 MILLER 82 / 01 / 23 1300 950 
 SCOTT 87 / 04 / 19 3000 3000 
 LEAD ( 컬럼 , offset , 기본 값 ) OVER ( ORDER BY 정렬 할 컬럼 ) 함수 : LAG 의 반대 , 뒤 ROW 값 사용 
 ENAME HIREDATE SAL LEAD 
 SMITH 80 / 12 / 17 800 1600 
 ALLEN 81 / 02 / 20 1600 1250 
 WARD 81 / 02 / 22 1250 2975 
 JONES 81 / 04 / 02 2975 2850 
 BLAKE 81 / 05 / 01 2850 2450 
 CLARK 81 / 06 / 09 2450 1500 
 TURNER 81 / 09 / 08 1500 1250 
 MARTIN 81 / 09 / 28 1250 5000 
 KING 81 / 11 / 17 5000 950 
 JAMES 81 / 12 / 03 950 3000 
 FORD 81 / 12 / 03 3000 1300 
 SCOTT 87 / 04 / 19 3000 0 
 RANK ( ) OVER ( ORDER BY 정렬 컬럼 ) 
 EMPNO ENAME SAL RANK _ ASC RANK _ DESC 
 7839 KING 5000 13 1 
 7788 SCOTT 3000 11 2 
 7902 FORD 3000 11 2 
 7566 JONES 2975 10 4 
 7698 BLAKE 2850 9 5 
 7782 CLARK 2450 8 6 
 7499 ALLEN 1600 7 7 
 7844 TURNER 1500 6 8 
 7934 MILLER 1300 5 9 
 7654 MARTIN 1250 3 10 
 7521 WARD 1250 3 10 
 7900 JAMES 950 2 12 
 7369 SMITH 800 1 13 
 DENSE _ RANK ( ) OVER ( ORDER BY 정렬 컬럼 ) 
 EMPNO ENAME SAL RANK _ ASC DENSE _ RANK 
 7369 SMITH 800 1 1 
 7900 JAMES 950 2 2 
 7521 WARD 1250 3 3 
 7654 MARTIN 1250 3 3 
 7934 MILLER 1300 5 4 
 7844 TURNER 1500 6 5 
 7499 ALLEN 1600 7 6 
 7782 CLARK 2450 8 7 
 7698 BLAKE 2850 9 8 
 7566 JONES 2975 10 9 
 7902 FORD 3000 11 10 
 7788 SCOTT 3000 11 10 
 7839 KING 5000 13 11 
 ROW _ NUMBER ( ) OVER ( ORDER BY 정렬 컬럼 ) 
 EMPNO ENAME SAL RANK _ ASC DENSE _ RANK ROW _ NUM 
 7369 SMITH 800 1 1 1 
 7900 JAMES 950 2 2 2 
 7521 WARD 1250 3 3 3 
 7654 MARTIN 1250 3 3 4 
 7934 MILLER 1300 5 4 5 
 7844 TURNER 1500 6 5 6 
 7499 ALLEN 1600 7 6 7 
 7782 CLARK 2450 8 7 8 
 7698 BLAKE 2850 9 8 9 
 7566 JONES 2975 10 9 10 
 7902 FORD 3000 11 10 11 
 7788 SCOTT 3000 11 10 12 
 7839 KING 5000 13 11 13 
 SUM ( 컬럼 ) OVER ( ORDER BY 정렬 컬럼 ) : 누적 합계 
 EMPNO ENAME SAL TOTAL 
 7369 SMITH 800 800 
 7499 ALLEN 1600 2400 
 7521 WARD 1250 3650 
 7566 JONES 2975 6625 
 7654 MARTIN 1250 7875 
 7698 BLAKE 2850 10725 
 7782 CLARK 2450 13175 
 7788 SCOTT 3000 16175 
 7839 KING 5000 21175 
 7844 TURNER 1500 22675 
 7900 JAMES 950 23625 
 7902 FORD 3000 26625 
 7934 MILLER 1300 27925 
 LAG 를 이용 하 여 앞 ROW 값 과 의 차이 구하 기 
 ENAME HIREDATE SAL LAG DIFF 
 SMITH 80 / 12 / 17 800 0 800 
 ALLEN 81 / 02 / 20 1600 800 800 
 WARD 81 / 02 / 22 1250 1600 - 350 
 JONES 81 / 04 / 02 2975 1250 1725 
 BLAKE 81 / 05 / 01 2850 2975 - 125 
 CLARK 81 / 06 / 09 2450 2850 - 400 
 TURNER 81 / 09 / 08 1500 2450 - 950 
 MARTIN 81 / 09 / 28 1250 1500 - 250 
 KING 81 / 11 / 17 5000 1250 3750 
 JAMES 81 / 12 / 03 950 5000 - 4050 
 FORD 81 / 12 / 03 3000 950 2050 
 MILLER 82 / 01 / 23 1300 3000 - 1700 
 SCOTT 87 / 04 / 19 3000 1300 1700 
 Previous Previous post : 생 능 _ ORACLE 배움터 _ 10 장 . 보안 과 권한 관리 
 Next Next post : 생 능 _ 오라클 SQL _ 04 장 . JOIN