혼코딩

함수(FUNCTION)

• 값을 전달받아 처리하여 결과값을 반환하는 기능을 제공

 

단일함수

• 하나의 값을 전달받아 처리하여 결과값을 반환하는 함수
• 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수

그룹함수 : 여러 개의 값을 전달받아 처리하여 결과값을 반환하는 함수

문자함수 : 문자형 상수를 전달받아 처리하여 결과를 반환하는 함수

 

UPPER (문자상수) : 전달받은 문자형 상수를 모두 대문자로 변환하여 반환하는 함수

LOWER (문자상수) : 전달받은 문자형 상수를 모두 소문자로 변환하여 반환하는 함수

SELECT ENAME, UPPER(ENAME), LOWER(ENAME) FROM EMP WHERE ENAME = 'SCOTT';

 

문자형 상수는 대소문자를 구분함

UPPER 함수 또는 LOWER 함수를 이용하여 대소문자 구분 없이 비교하여 검색 가능

SELECT EMPNO,ENAME,SAL FROM EMP WHERE UPPER(ENAME) = UPPER('smith');

 

INITCAP (문자상수) : 문자형 상수를 전달받아 첫문자만 대문자로 변환하고 나머지는 소문자로 변환하여 반환하는 함수

SELECT ENAME, INITCAP(ENAME) FROM EMP;

 

CONCAT (문자상수,문자상수)

• 두개의 문자형 상수를 결합하여 반환하는 함수
• || 기호와 동일한 기능 제공

SELECT ENAME, JOB, CONCAT(ENAME, JOB), ENAME || JOB FROM EMP;

 

SUBSTR(문자상수, 시작위치, 갯수) : 문자형 상수에서 시작위치(INDEX)부터 갯수만큼의 문자를 분리하여 반환하는 함수

SELECT EMPNO, ENAME, JOB, SUBSTR(JOB, 6, 3) FROM EMP WHERE EMPNO=7499;

 

LENGTH(문자상수) : 문자형 상수의 문자갯수를 반환하는 함수

SELECT EMPNO, ENAME, LENGTH(ENAME) FROM EMP WHERE EMPNO=7499;

 

INSTR(문자상수, 검색문자, 시작위치, 검색갯수)

• 문자형 상수에서 검색문자를 시작위치부터 검색하여 검색갯수의 시작위치(INDEX)를 반환하는 반환하는 함수
• 검색문자가 없는 경우 0 반환

SELECT EMPNO, ENAME, JOB, INSTR(JOB, 'A', 1, 2) FROM EMP WHERE EMPNO=7499;

 

LPAD(문자상수, 자릿수, 채울문자) : 문자형 상수를 자릿수만큼의 길이로 오른쪽 정렬하여 검색하되 왼쪽 남은 자릿수는 채울문자로 채워 반환하는 함수

RPAD(문자상수, 자릿수, 채울문자) : 문자형 상수를 자릿수만큼의 길이로 왼쪽 정렬하여 검색하되 오른쪽 남은 자릿수는 채울문자로 채워 반환하는 함수

SELECT EMPNO, ENAME, SAL, LPAD(SAL, 8, '*'), RPAD(SAL, 8, '*') FROM EMP WHERE EMPNO=7844;

 

TRIM( {LEADING | TRAILING} 제거문자 FROM 문자상수) : 문자상수의 앞(LEADING) 또는 뒤(TRAILING)에 존재하는 제거문자를 모두 없애고 반환하는 함수

SELECT EMPNO, ENAME, JOB, TRIM(LEADING 'S' FROM JOB) , TRIM(TRAILING 'N' FROM JOB) FROM EMP WHERE EMPNO=7844;

 

REPLACE(문자상수, 검색문자, 변환문자) : 문자형 상수에 검색문자를 찾아 변환문자로 변환하여 반환하는 함수

SELECT EMPNO, ENAME, JOB, REPLACE(JOB, 'MAN', 'PERSON') FROM EMP WHERE EMPNO=7844;

 

 

숫자함수 : 숫자형 상수를 전달받아 처리하여 결과값을 반환하는 함수

 

ROUND(숫자상수, 소수점자릿수)

• 숫자형 상수를 소숫점 자릿수만큼 검색되도록 반올림하여 반환하는 함수

DUAL

• 가상테이블
• 테이블 없이 SELECT 명령을 작성할 경우 사용하는 테이블

SELECT 45.582, ROUND(45.582, 2), ROUND(45.582, 0), ROUND(45.582, -1) FROM DUAL;

 

TRUNC(숫자상수, 소수점자릿수) : 숫자형 상수를 소숫점 자릿수만큼 검색되도록 절삭하여 반환하는 함수

SELECT 45.582, TRUNC(45.582, 2), TRUNC(45.582, 0), TRUNC(45.582, -1) FROM DUAL;

 

CEIL(숫자상수) : 숫자형 상수에서 소숫점 이하 값이 존재할 경우 증가하여 정수값으로 반환하는 함수

SELECT 15.3, CEIL(15.3), CEIL(-15.3) FROM DUAL;

 

FLOOR(숫자상수) : 숫자형 상수에서 소숫점 이하 값이 존재할 경우 감소하여 정수값으로 반환하는 함수

SELECT 15.3, FLOOR(15.3), FLOOR(-15.3) FROM DUAL;

 

MOD(숫자상수, 숫자상수) : 숫자형 상수를 나누어 나머지를 반환하는 함수

SELECT 20/8, MOD(20, 8) FROM DUAL;
--정수값을 나눈 결과로 실수값 발생 가능

 

POWER(숫자상수, 숫자상수) : 숫자형 상수의 제곱근을 계산하여 반환하는 함수

SELECT 3*3*3*3*3, POWER(3, 5) FROM DUAL;

 

 

날짜함수

• 날짜형 상수를 전달받아 처리하여 반환하는 함수

SYSDATE

• 시스템의 현재 날짜와 시간정보를 제공하기 위한 키워드
• 자료형이 날짜형인 경우 표면적으로 'RR/MM/DD'로 표현되지만 내부적으로 날짜와 시간정보 저장

SELECT SYSDATE FROM DUAL;

 

ADD_MONTHS(날짜상수, 숫자상수) : 날짜형 상수에 숫자형 상수만큼의 개월수를 더한 결과를 반환하는 함수

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 5) FROM DUAL;

 

NEXT_DAY(날짜상수, 요일) : 날짜형 상수에서 다가오는 특정 요일의 날짜를 반환하는 함수

SELECT SYSDATE, NEXT_DAY(SYSDATE, '토') FROM DUAL;

 

오라클에 접속된 사용자 환경(세션 : SESSION)에 따라 날짜와 시간정보를 다르게 표현 가능

세션의 날짜에 대한 표현 언어 변경

ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'SAT') FROM DUAL;

ALTER SESSION SET NLS_LANGUAGE='KOREAN'; 
SELECT SYSDATE, NEXT_DAY(SYSDATE, '토') FROM DUAL;

 

 

TRUNC(날짜상수, 표현단위) : 날짜형 상수를 원하는 단위까지만 표현하며 나머지는 초기값으로 반환하는 함수

SELECT SYSDATE, TRUNC(SYSDATE, 'MONTH'), TRUNC(SYSDATE, 'YEAR') FROM DUAL;

 

날짜형 상수는 연산 가능

날짜상수 + 숫자상수 = 날짜상수 → 일 증가

SELECT SYSDATE, SYSDATE + 7 FROM DUAL;

 

날짜상수 + 숫자상수/24 = 날짜상수 → 시간 증가

SELECT SYSDATE, SYSDATE + 100/24 FROM DUAL;

 

날짜상수 - 숫자상수 = 날짜상수 → 일 감소

SELECT SYSDATE, SYSDATE - 7 FROM DUAL;

 

날짜상수 - 숫자상수/24 = 날짜상수 → 시간 감소

SELECT SYSDATE, SYSDATE - 100/24 FROM DUAL;

 

날짜상수 - 날짜상수 = 숫자상수 → 일(실수)

SELECT EMPNO, ENAME, HIREDATE, SYSDATE-HIREDATE FROM EMP WHERE EMPNO=7499; 
SELECT EMPNO, ENAME, HIREDATE, CEIL(SYSDATE-HIREDATE) || '일' "근속일수" FROM EMP WHERE EMPNO=7499;

 

 

 

변환함수

• 전달받은 상수를 원하는 자료형의 상수로 변환하여 반환하는 함수

TO_NUMBER(문자상수)

• 문자형 상수를 전달받아 숫자형 상수로 변환하여 반환하는 함수
• 전달받은 문자형 상수가 숫자가 아닌 경우 에러 발생

SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO=7839;

 

비교 컬럼의 자료형이 숫자형인 경우 비교값이 문자형 상수인 경우 숫자형 상수로 변환하여 비교 - 강제 형변환

SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO=TO_NUMBER('7839');

비교 컬럼의 자료형이 숫자형인 경우 비교값이 문자형 상수라면 자동으로 숫자형 상수로 변환하여 비교 - 자동 형변환

SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO='7839';

 

EMP 테이블에서 사원번호가 7839인 사원의 사원번호, 사원이름, 급여, 급여*0.9 검색

SELECT EMPNO, ENAME, SAL, SAL*0.9 FROM EMP WHERE EMPNO=7839; 
SELECT EMPNO, ENAME, SAL, SAL*TO_NUMBER('0.9') FROM EMP WHERE EMPNO=7839; 
SELECT EMPNO, ENAME, SAL, SAL*'0.9' FROM EMP WHERE EMPNO=7839;

 

문자형 상수를 계산할 경우 자동으로 숫자형 상수로 변환 

SELECT '10'+'20' FROM DUAL;

 

TO_DATE (문자상수 [,패턴기호])

• 문자형 상수를 전달받아 날짜형 상수로 변환하여 반환하는 함수
• 문자형 상수가 날짜 형식가 아닌 경우 에러 발생 - 기본적으로 'RR/MM/DD' 형식으로 표현

 

비교 컴럼의 자료형이 날짜형인 경우 비교값이 문자형 상수인 경우 날짜형 상수로 변환하여 비교 - 강제 형변환

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE('82/01/23');

비교 컴럼의 자료형이 날짜형인 경우 비교값이 문자형 상수라면 자동으로 날짜형 상수로 변환하여 비교 - 자동 형변환 

SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE='82/01/23';

 

 

'RR/MM/DD' 형식 대신 'YYYY-MM-DD' 형식으로 표현 가능 

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE='1982-01-23';

 

TO_DATE 함수의 패턴기호를 이용하여 원하는 형식의 날짜형 상수로 표현 가능

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE('01-23-1982', 'MM-DD-YYYY');

 

EMP 테이블에서 사원번호가 7844인 사원의 사원번호, 사원이름, 입사일, 입사일로부터 2000년 1월 1일까지 근무한 일자를 계산하여 검색

SELECT EMPNO, ENAME, HIREDATE, TO_DATE('2000-01-01') - HIREDATE FROM EMP WHERE EMPNO=7844;

 

TO_CHAR( {숫자상수|날짜상수}, 패턴기호)

• 전달받은 숫자형 상수 또는 날짜형 상수를 패턴기호 형식의 문자형 상수로 변환하여 반환하는 함수

날짜패턴기호 : YYYY(년), RR(년), MM(월), DD(일), HH24(시), HH12(시), AM(오전), PM(오후), MI(분), SS(초)

SELECT SYSDATE FROM DUAL; 
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; 
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

 

EMP 테이블에서 1981년도에 입사한 사원의 정보 검색

세션의 날짜형 표현방식(NLS_DATE_FORMAT)이 'RR/MM/DD' 형식일 경우에만 가능

SELECT * FROM EMP WHERE HIREDATE LIKE '81%';

날짜패턴기호를 이용하여 원하는 날짜값만 반환받아 문자형 상수로 비교

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY')='1981';

 

숫자패턴기호

• 9(숫자 또는 공백), 0(숫자), L(화폐단위), $(달러)
• 자릿수가 모자란 경우 #으로 표현

SELECT 100000000, TO_CHAR(100000000, '999,999,990' ) FROM DUAL;

 

EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여 검색

SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO=7844; 
SELECT EMPNO, ENAME, TO_CHAR(SAL,'999,990') SAL FROM EMP WHERE EMPNO=7844; 
SELECT EMPNO, ENAME, TO_CHAR(SAL,'L99,990') SAL FROM EMP WHERE EMPNO=7844; 
SELECT EMPNO, ENAME, TO_CHAR(SAL,'$99,990.00') SAL FROM EMP WHERE EMPNO=7844;

 

일반함수 : 전달값이 특정 조건일 경우 처리하여 결과를 반환하는 함수

 

NVL(전달값, 변경값) : 전달값이 NULL인 경우 변경값으로 변환하여 반환하는 함수

변경값은 전달값의 자료형과 동일한 자료형의 값으로 변경하지 않으면 에러 발생

 

EMP 테이블에서 모든 사원의 사원번호,사원이름,연봉((급여+성과급)*12) 검색

NVL 함수를 이용하여 성과급이 NULL인 경우 0으로 변환하여 연산

SELECT EMPNO, ENAME, (SAL+NVL(COMM, 0)) * 12 ANNUAL FROM EMP;

 

NVL2(전달값, 변경값1, 변경값2) : 전달값이 NULL이 아닌 경우 변경값1로 변환하고 NULL인 경우 변경값2로 변환하여 반환하는 함수

SELECT EMPNO, ENAME, (SAL+NVL2(COMM, COMM, 0))*12 ANNUAL FROM EMP; 
SELECT EMPNO, ENAME, NVL2(COMM, (SAL+COMM)*12, SAL*12) ANNUAL FROM EMP;

 

DECODE(전달값, 비교값1, 변경값1, 비교값2, 변경값2, ...[,기본변경값])

• 전달값을 비교값과 차례대로 비교하여 같은 경우 변경값으로 변환하여 반환하는 함수
• 같은 비교값이 없는 경우 기본 변경값으로 변환하여 반환 - 기본 변경값이 생략된 경우 NULL 반환

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 업무, 급여, 업무별 실급여 검색

업무별 실급여 : 업무별로 급여를 다시 계산하여 실제로 지불할 급여

ANALYST : 급여*1.1, CLERK : 급여*1.2, MANAGER : 급여*1.3, PRESIDENT : 급여*1.4, SALESMAN : 급여*1.5

SELECT EMPNO, ENAME, JOB, SAL, DECODE(JOB, 'ANALYST', SAL*1.1, 'CLERK', SAL*1.2 , 'MANAGER', SAL*1.3, 'PRESIDENT', SAL*1.4, 'SALESMAN', SAL*1.5, SAL) "업무별 실급여" FROM EMP;

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 업무별 급여 검색

해당 업무가 아닌 경우 NULL 검색

SELECT EMPNO, ENAME, DECODE(JOB, 'ANALYST', SAL) ANALYST , DECODE(JOB, 'CLERK', SAL) CLERK, DECODE(JOB, 'MANAGER', SAL) MANAGER , DECODE(JOB, 'PRESIDENT', SAL) PRESIDENT, DECODE(JOB, 'SALESMAN', SAL) SALESMAN FROM EMP;

 

 

그룹함수

• 값을 여러개 전달받아 처리하여 결과를 반환하는 함수

 

COUNT(전달값)

• 전달값의 갯수를 반환하는 함수 - 검색행의 갯수 반환
• 다른 검색대상과 그룹함수를 동시에 사용 불가능 : 검색행의 갯수가 다르므로 에러 발생
• 그룹함수는 NULL이 전달될 경우 값으로 인식되지 않아 미처리

SELECT COUNT(EMPNO) FROM EMP;

 

전달값으로 * 기호를 사용하여 모든 컬럼값으로 행의 갯수를 계산하여 반환

SELECT COUNT(*) FROM EMP;

 

MAX(전달값) : 전달값 중 최대값을 반환하는 함수

SELECT MAX(SAL) FROM EMP; 
SELECT MAX(ENAME) FROM EMP; 
SELECT MAX(HIREDATE) FROM EMP;

 

MIN(전달값) : 전달값 중 최소값을 반환하는 함수

SELECT MIN(SAL) FROM EMP; 
SELECT MIN(ENAME) FROM EMP; 
SELECT MIN(HIREDATE) FROM EMP;

 

SUM(전달값) : 숫자형 상수를 전달받아 전달값의 합계를 계산하여 반환하는 함수

SELECT SUM(SAL) FROM EMP;

 

AVG(전달값) : 숫자형 상수를 전달받아 전달값의 평균을 계산하여 반환하는 함수

SELECT AVG(SAL) FROM EMP; 
SELECT ROUND(AVG(SAL), 2) FROM EMP;

 

EMP 테이블에서 모든 사원의 평균 성과급 검색

성과급이 NULL이 아닌 사원의 평균 성과급 검색 - 검색오류

SELECT AVG(COMM) FROM EMP;

 

NVL 함수를 이용하여 성과급이 NULL인 경우 0으로 변환하여 평균 성과급 검색

SELECT AVG(NVL(COMM, 0)) FROM EMP; 
SELECT CEIL(AVG(NVL(COMM, 0))) "평균 성과급" FROM EMP;

 

GROUP BY

• 그룹함수를 사용할 경우 컬럼값으로 그룹을 세분화하여 검색하기 위해 사용
• 컬럼값이 같은 경우 같은 그룹으로 처리되어 검색하는 구문
• 형식) SELECT 그룹함수[,검색대상],... FROM 테이블명,... [WHERE 조건식] GROUP BY {컬럼명|연산식|함수},... [ORDER BY {컬럼명|연산식,함수,위치값,별칭} {ASC|DESC},... ]

 

GROUP BY 구문에서 사용된 그룹 지정 표현식은 검색대상으로 사용 가능 - 권장

SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO;

컬럼의 별칭을 GROUP BY 구분에서 사용할 경우 에러 발생

SELECT DEPTNO DNO, COUNT(*) FROM EMP GROUP BY DNO;

 

EMP 테이블에서 모든 사원의 업무별 평균 급여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB;

 

EMP 테이블에서 업무가 PRESIDENT인 사원의 제외한 사원들의 업무별 평균 급여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB;

 

EMP 테이블에서 업무가 PRESIDENT인 사원의 제외한 사원들의 업무별 평균 급여를 평균 급여로 내림차순 정렬하여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB ORDER BY AVG_SAL DESC;

 

SUM : EMP 테이블에서 모든 사원의 부서별 급여 합계 검색

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO;

 

HAVING

• GROUP BY 구문으로 세분화되어 검색된 결과의 그룹 조건을 부여하여 검색
• 형식) SELECT 그룹함수[,검색대상],... FROM 테이블명,... [WHERE 조건식] GROUP BY {컬럼명|연산식|함수},... HAVING 그룹조건식 [ORDER BY {컬럼명|연산식,함수,위치값,별칭} {ASC|DESC},... ]

 

EMP 테이블에서 모든 사원의 부서별 급여 합계 중 부서별 급여 합계가 9000 이상인 경우에만 검색

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>=9000;

 

EMP 테이블에서 업무가 PRESIDENT인 사원의 제외한 사원들의 업무별 평균 급여 검색

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB; 
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB HAVING JOB<>'PRESIDENT';

 

분석함수

• 윈도우함수(그룹함수, 순서함수, 순위함수 등)에 의해 발생된 값을 제공받아 처리하여 결과를 반환하는 함수
• 형식) SELECT 윈도우함수 OVER ([PARTITION BY 컬럼명] [ORDER BY 컬럼명] [WINDOWING]), 검색대상, ... FROM 테이블명

 

MAX( )

EMP 테이블에서 모든 사원의 급여 중 가장 많은 급여 검색

SELECT MAX(SAL) FROM EMP;

 

EMP 테이블에서 모든 사원 중 가장 많은 급여를 받는 사원의 사원번호, 사원이름, 급여 검색

그룹함수를 사용할 경우 다른 검색대상을 작성할 경우 에러 발생

SELECT EMPNO, ENAME, MAX(SAL) FROM EMP;

 

SELECT 명령을 여러 번 사용하여 가장 많은 급여를 받는 사원의 사원번호, 사원이름, 급여 검색 가능

SELECT MAX(SAL) FROM EMP; --검색결과 : 5000
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL=5000;

 

OVER( )

그룹함수를 분석함수와 같이 사용할 경우 다른 검색대상 사용 가능

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 최대급여 검색

SELECT EMPNO, ENAME, SAL, MAX(SAL) OVER() FROM EMP;

 

AVG( )

EMP 테이블에서 모든 사원의 부서별 평균 급여 검색

SELECT DEPTNO, CEIL(AVG(SAL)) "부서별 평균 급여" FROM EMP GROUP BY DEPTNO;

# CEIL() 함수는 이전 포스팅 글에 나와있음

 

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서번호, 부서별 평균 급여 검색

분석함수를 사용할 경우 GROUP BY 구문 사용 불가능

SELECT EMPNO, ENAME, SAL, DEPTNO, CEIL(AVG(SAL) OVER()) "부서별 평균 급여" FROM EMP GROUP BY DEPTNO;

 

분석함수에서는 GROUP BY 구문 대신 PARTITION BY 기능을 제공

SELECT EMPNO, ENAME, SAL, DEPTNO, CEIL(AVG(SAL) OVER(PARTITION BY DEPTNO)) "부서별 평균 급여" FROM EMP;

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여합계를 급여로 내림차순 정렬하여 검색

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER() "급여합계" FROM EMP ORDER BY SAL DESC;

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 순차적 누적 급여합계를 급여로 내림차순 정렬하여 검색

• OVER 함수에서 ORDER BY 기능을 이용하면 정렬하면서 윈도우 함수의 결과를 차례대로 반환
• 정렬값이 동일한 경우 윈도우 함수의 동시 처리 결과값 반환

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC) "순차적 누적 급여합계" FROM EMP;

 

정렬값이 동일한 경우 다른 정렬값을 제공하면 동시 처리 결과값 미반환

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC, ENAME) "순차적 누적 급여합계" FROM EMP;

 

OVER 함수에 WINDOWING 기능을 이용하면 정렬값이 동일한 경우에도 동시 처리 결과값 반환 방지

• WINDOWING : 순차적인 검색행을 기준으로 이전 또는 이후에 존재하는 행(범위)을 표현하는 기능
• ROWS UNBOUNDED PRECEDING : 검색행을 기준으로 이전에 존재하는 모든 행만을 검색대상으로 설정

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) "순차적 누적 급여합계" FROM EMP;

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 검색사원의 급여와 검색사원의 직전 사원 급여와 검색사원의 직후 사원 급여의 합계를 급여로 오름차순 정렬하여 검색

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER
    (ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "부분합계" FROM EMP;

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 검색사원의 급여보다 100 작거나 200 큰 사원의 인원수를 급여로 오름차순 정렬하여 검색

SELECT EMPNO, ENAME, SAL, COUNT(*) OVER
    (ORDER BY SAL RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING)-1 "인원수" FROM EMP;

 

 

 

순위함수

• RANK, DENSE_RANK, ROW_NUMBER
• 분석함수와 같이 사용해야 되는 함수

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여순위를 급여로 내림차순 정렬하여 검색

RANK 함수 : 정렬값이 동일한 경우 같은 순위로 지정하며 다음 순위를 건너뛰고 검색하는 함수

SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) 급여순위 FROM EMP;

 

DENSE_RANK 함수 : 정렬값이 동일한 경우 같은 순위로 지정하며 다음 순위로 검색하는 함수

SELECT EMPNO, ENAME, SAL, DENSE_RANK() OVER(ORDER BY SAL DESC) 급여순위 FROM EMP;    

 

ROW_NUMBER 함수 : 정렬값이 동일한 경우 다른 순위로 지정하여 검색하는 함수 - 행번호 지정

SELECT EMPNO, ENAME, SAL, ROW_NUMBER() OVER(ORDER BY SAL DESC) 급여순위 FROM EMP;   

 

 

순서함수

• FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 분석함수와 같이 사용해야 되는 함수

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 급여를 가장 많이 받는 사원의 사원이름, 급여를 가장 적게 받는 사원이름을 급여로 내림차순 정렬하여 검색

• FIRST_VALUE 함수 : 첫번째 검색행의 컬럼값을 반환하는 함수
• LAST_VALUE 함수 : 마지막 검색행의 컬럼값을 반환하는 함수

SELECT EMPNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER(ORDER BY SAL DESC) MAX_ENAME
    ,LAST_VALUE(ENAME) OVER(ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW 
    AND UNBOUNDED FOLLOWING) MIN_ENAME FROM EMP;

 

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 검색사원 직전 사원의 급여, 검색사원 직후 사원의 급여를 급여로 내림차순 정렬하여 검색

• LAG 함수 : 검색행 이전 행의 컬럼값을 반환하는 함수
  • 형식) LAG(컬럼명, 이전행위치, 변경값) : 이전 위치의 행에 컬럼값을 반환하되 이전 행이 없으면 변경값 반환
• LEAD 함수 : 검색행 이후 행의 컬럼값을 반환하는 함수
  • 형식) LEAD(컬럼명, 이전행위치, 변경값) : 이후 위치의 행에 컬럼값을 반환하되 이후 행이 없으면 변경값 반환

SELECT EMPNO, ENAME, SAL, LAG(SAL,1,0) OVER(ORDER BY SAL DESC) BEFORE_SAL
    ,LEAD(SAL,1,0) OVER(ORDER BY SAL DESC) AFTER_SAL FROM EMP;