오라클SQL, 숫자 및 날짜 함수(Number And Date Function), ABS, CEIL,EXP,FLOOR,MOD,ROUND,SIGN,TRUNC,WIDTH_BUCKET

오라클SQL, 숫자 및 날짜 함수(Number And Date Function), ABS, CEIL,EXP,FLOOR,MOD,ROUND,SIGN,TRUNC,WIDTH_BUCKET

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=611

 

오라클SQL, 숫자 및 날짜 함수(Number And Date Function), ABS, CEIL,EXP,FLOOR,MOD,ROUND,SIGN,TRUNC,WIDTH_BUCKET

오라클SQL, 숫자 및 날짜 함수(Number And Date Function), ABS, CEIL,EXP,FLOOR,MOD,ROUND,SIGN,TRUNC,WIDTH_BUCKET4.3 숫자 및 날짜 함수(Number And Date Function) 날짜 ,시간, 숫자를 다루는 SQL 함수들은 다양하다. 이들 모두를 외우는 것은 불가능 한 것은 아니지만 쉽…

ojc.asia

4.3 숫자 및 날짜 함수(Number And Date Function)

날짜 ,시간, 숫자를 다루는 SQL 함수들은 다양하다. 이들 모두를 외우는 것은 불가능 한 것은 아니지만 쉽게 잊어 버리게 되므로 실습을 따라 하면서 사용법을 이해하자.

[Number Function]

ABS(n) : 인수 n의 절대값을 반환

CEIL(n) : 인수 n보다 크거나 같은 최소 정수를 반환

EXP(n) : e(2.718…)의 n승을 반환

FLOOR(n) : 인수 n보다 작거나 같은 최대 정수를 반환

MOD(m, n) : 인수 m을 n으로 나눈 나머지를 반환

POWER(m, n) : 인수 m의 n 지수승을 반환

ROUND(m, n) : 인수 m을 소수이하 n 자리까지 표시하는데 반올림 한다.

SIGN( n) : n이 음수면 -1, 양수이면 1, 0이면 0을 리턴한다.

TRUNC(m, n) : 인수 m을 소수이하 n 자리까지 표시 하는데 절삭 한다.

WIDTH_BUCKET(n, min_value, max_value, num_buckets) : 인수n을 min_value, max_value 범위로 num_buckets 만큼 등급을 나누어 표시한다.

SQL> select mod(10, 3) "10을 3으로 나눈 나머지", 2 power(3, 2) "3의 제곱", 3 abs(-12) "-12의 절대값" 4 from dual; 10을 3으로 나눈 나머지 3의 제곱 -12의 절대값 ---------------------- ---------- ------------ 1 9 12 SQL> select ceil(12.7) from dual; CEIL(12.7) ---------- 13 SQL> select floor(12.7) from dual; FLOOR(12.7) ----------- 12 SQL> select round(12.567, 1) from dual; ROUND(12.567,1) --------------- 12.6 SQL> select trunc(12.567, 1) from dual; TRUNC(12.567,1) --------------- 12.5 --ROUND, TRUNC 함수의 두 번째 argument를 생략하면 default로 0 SQL> select round(45.23), round(45.23,0), trunc(45.23), trunc(45.23,0) from dual; ROUND(45.23) ROUND(45.23,0) TRUNC(45.23) TRUNC(45.23,0) ------------ -------------- ------------ -------------- 45 45 45 45 -- -의 의미는 소수이상의 의미, -3은 소수이상3자리 즉 백단위를 의미한다. SQL> SELECT sal, ROUND(sal, -3), TRUNC(sal, -3) FROM emp WHERE ROWNUM < 6; SAL ROUND(SAL,-3) TRUNC(SAL,-3) ---------- ------------- ------------- 800 1000 0 1600 2000 1000 1250 1000 1000 2975 3000 2000 1250 1000 1000 -- 아래 SIGN함수는 n이 음수면 -1, 양수이면 1, 0이면 0을 리턴한다. SQL> select sign(200-100), sign(100-200), sign(100-100) from dual; SIGN(200-100) SIGN(100-200) SIGN(100-100) ------------- ------------- ------------- 1 -1 0 -- sal값을 1부터 5000까지의 값을 기준으로 10개의 등급을 나누어서 표시 SQL> select ename, sal, width_bucket(sal, 1, 5001, 10) "sal 등급" from emp; ENAME SAL sal 등급 ---------- ---------- ---------- SMITH 800 2 ALLEN 1600 4 …… KING 5000 10 TURNER 1500 3 ADAMS 1100 3 JAMES 950 2 FORD 3000 6 MILLER 1300 3

[Date Function]

ADD_MONTHS(date, n) : date에 n달을 추가한다. date값을 반환하며 n은 정수이다.

CURRENT_TIME : 현재 세션의 date값을 반환 한다.(SYSDATE와 유사)

EXTRACT({year|month|day|hour|minute|second}) FROM (date) : 날짜 값을 가진 데이터(date)로부터 원하는 날짜 영역을 추출

LAST_DAY(date) : date를 포함하는 달의 마지막 날을 반환, date값을 반환 한다.

MONTHS_BETWEEN(date1, date2) : date1과 date2의 차이를 달 수로 표현, 정수를 반환하며 차이가 1개월 미만인 경우는 1보다 작은 소수를 반환한다. 즉 (date1 – date2)를 나타낸 것이라고 생각하면 된다.

NEXT_DAY(date, c) : 날짜 date를 포함해서 이후에 나타나는 첫 번째 c요일을 반환 한다.

NEW_DATE(date, z1, z2) : timezone z1형태를 z2형태로 날짜와 시간을 리턴한다.

ROUND(date, fmt) : date를 지정한 포맷 형식에 맞춰 표시하는데 반올림 한다.

SYSDATE : 해당 시스템의 현재 날짜 및 시간을 반환

SYSTIMESTAMP : 오라클 9i이후에서 추가 되었으며 SYSDATE와 마찬가지로 해당 시스템의 현재 날짜 및 시간을 반환 한다. TIMESTAMP는 DATETIME의 확장이며 보다 정교한 시간을 나타낼 수 있다.

TRUNC(date, fmt) : date를 지정한 포맷 형식에 맞춰 표시하는데 절삭 한다.

[ROUND, TRUNC함수의 fmt 문자열]

YYYY , YYY , YY , Y , YEAR : 년도의 첫날(7월1일부터 반올림)

Q : 분기의 첫날(분기의 두번째 달 16일부터 반올림)

MONTH , MON, MM : 월의 첫날(16일부터 반올림)

W : 월의 첫날과 같은 요일을 가진 그주의 일자를 출력

DDD, DD : 일(정오를 지나면 반올림)

DAY, DY, D : 주의 첫 요일(수요일 정오가 지나면 반올림)

HH, HH12, HH24 : 시단위

MI : 분단위

--현재 시스템의 시간 및 날짜를 확인 SQL> column sysdate format a10 SQL> column systimestamp format a35 SQL> select sysdate, systimestamp from dual; SYSDATE SYSTIMESTAMP ---------- ----------------------------------- 14/09/08 14/09/08 11:22:50.593000 +09:00 -- 현재 세션의 NLS_DATE_FORMAT을 확인 SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'; PARAMETER VALUE ------------------------------------------------------------ NLS_DATE_FORMAT RR/MM/DD SQL> select current_date from dual; CURRENT_ -------- 14/09/08 SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------- +09:00 SQL> alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss'; 세션이 변경되었습니다. SQL> select current_date from dual; CURRENT_DATE ------------------- 2014.09.08 12:45:46 SQL> SELECT EXTRACT(YEAR FROM DATE '2014-01-01'), 2 EXTRACT(MONTH FROM DATE '2014-01-01') 3 FROM DUAL; EXTRACT(YEARFROMDATE'2014-01-01') EXTRACT(MONTHFROMDATE'2014-01-01') --------------------------------- ---------------------------------- 2014 1 ---- EMP 테이블에서 입사년도가 1981년보다 큰 사원의 사번, 이름 추출 SQL> select empno, ename from emp 2 where extract(year from hiredate) > 1981 3 order by hiredate; EMPNO ENAME ---------- ---------- 7934 MILLER 7788 SCOTT 7876 ADAMS --오늘부터 100일 후는? SQL> select sysdate+100 from dual; SYSDATE+ -------- 14/12/17 -- 현재월의 마지막 날을 반환 하는 예문 이다. SQL> select last_day(sysdate) from dual; LAST_DAY -------- 14/09/30 -- EMP 테이블에서 사원들의 근속월수, 첫째 자리에서 절삭, months_between은 앞에서 뒤날자를 뺀다. SQL> select ename, hiredate, sysdate, trunc(months_between(sysdate, hiredate)) from emp; ENAME HIREDATE SYSDATE TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) ---------- -------- ---------- --------------------------------------- SMITH 80/12/17 14/09/08 404 ALLEN 81/02/20 14/09/08 402 WARD 81/02/22 14/09/08 402 JONES 81/04/02 14/09/08 401 MARTIN 81/09/28 14/09/08 395 …… -- 현재일짜와 2010년1월1일과의 개월 차이(소수첫째 자리 반올림) SQL> select sysdate, round(months_between(sysdate,'10/01/01')) from dual; SYSDATE ROUND(MONTHS_BETWEEN(SYSDATE,'10/01/01')) ---------- ----------------------------------------- 14/09/08 56 -- 다음 월요일을 출력(현재는 2014년9월8일 월요일) SQL> select sysdate, next_day(sysdate,'월요일') from dual; SYSDATE NEXT_DAY ---------- -------- 14/09/08 14/09/15 -- 월을 단축형으로 SQL> select sysdate, next_day(sysdate,'월') from dual; SYSDATE NEXT_DAY ---------- -------- 14/09/08 14/09/15 --다음 화요일 SQL> select sysdate, next_day(sysdate,'화') from dual; SYSDATE NEXT_DAY ---------- -------- 14/09/08 14/09/09 -- NEW_DATE함수는 오라클 9i 이후 사용가능, 한국시각은 GMT+9 SQL> SELECT TO_CHAR(sysdate,'HH24:MI:SS') as "한국시각", 2 TO_CHAR(sysdate-9/24,'HH24:MI:SS') as "그리니치표준시각", 3 TO_CHAR(NEW_TIME(TO_DATE(sysdate-9/24,'HH24:MI:SS'), 'GMT','EST'),'HH24:MI:SS') as "Eastern Time Zone", 4 TO_CHAR(NEW_TIME(TO_DATE(sysdate-9/24,'HH24:MI:SS'), 'GMT','BST'),'HH24:MI:SS') as "British Summer Time" 5 FROM DUAL; 한국시각 그리니치 Eastern British -------- -------- -------- -------- 12:38:50 03:38:50 09:09:08 03:09:08 -- SYSDATE를 그냥 출력하면 날짜만 나오게 된다. 만약 시간도 확인을 하기 위해서는 to_char 와 같은 변환 함수를 이용하여 문자로 변환을 시켜 줘야 한다. SQL> select sysdate, to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') from dual; SYSDATE TO_CHAR(SYSDATE,'YY ---------- ------------------- 14/09/08 2014.09.08 11:34:23 --MONTH는 월의 첫날을 출력하는데 8일 이므로 월은 반올림 되지 않고 9월1일이 출력된다. 또한 DAY의 경우 주의 첫 요일을 출력하는데 9월8일 월요일 이므로 반올림 대상이 아니다. 즉 주의 첫 일요일인 9월7일이 출력 되는 것이다. SQL> select sysdate, round(sysdate, 'MONTH'), round(sysdate, 'DAY') from dual; SYSDATE ROUND(SY ROUND(SY ---------- -------- -------- 14/09/08 14/09/01 14/09/07 --MONTH는 월의 첫날을 출력하는데 16일 이므로 월은 반올림 되어 10월1일이 출력된다. 또한 DAY의 경우 주의 첫 요일을 출력하는데 9월11 목요일 이므로 반올림 대상이다. 즉 주의 첫 일요일인 9월17일(일요일)이 출력 된다. SQL> select sysdate, round(to_date('14/09/16'), 'MONTH'), round(to_date('14/09/11'), 'DAY') from dual; SYSDATE ROUND(TO ROUND(TO ---------- -------- -------- 14/09/08 14/10/01 14/09/14 -- EMP 테이블에서 “SMITH” 사원의 오늘을 기준으로 입사한지 몇 개월째 인지 파악 하려고 한다.(16일 부터는 1개월로 반올림하는 예문이다.) SQL> select ename, hiredate "입사일", sysdate "기준일", 2 round(months_between(sysdate, hiredate), 0) "입사개월수" 3 from emp 4 where ename = 'SMITH'; ENAME 입사일 기준일 입사개월수 -------------------- -------- -------- ---------- SMITH 80/12/17 14/09/08 405 --아래는 trunc, round를 사용하는 예문이다. 9월30일은 화요일로써 DAY형태로는 반올림 되지 않고 (수요일 정오가 지나야 반올림) 그 주 시작 일요일인 9월28일이 출력된다. SQL> select round(to_date('20140930'), 'MONTH'), round(to_date('20140930'), 'DAY'), 2 trunc(to_date('20140930'), 'MONTH'), trunc(to_date('20140930'), 'DAY') 3 from dual; ROUND(TO ROUND(TO TRUNC(TO TRUNC(TO ----------------------------------------------------------------- 14/10/01 14/09/28 14/09/01 14/09/28 --일단위로 보면 정오를 지났으므로 일자가 반올림 된다. DD포맷은 월에서 오늘이 며칠인지 리턴하는데 정오를 기준으로 일자를 반올림 한다. SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss'), round(sysdate,'DD') from dual; TO_CHAR(SYSDATE,'YY ROUND(SY ------------------- -------- 2014.09.08 11:47:51 14/09/08 SQL> select sysdate, round(to_date('14/09/26'),'DD') from dual; SYSDATE ROUND(TO ---------- -------- 14/09/08 14/09/26

[날짜 산술 연산]

date + number à date

date – number à date

date – date à 정수

date + number/24 à date

SQL> select systimestamp from dual; SYSTIMESTAMP ----------------------------------- 14/09/08 11:48:57.656000 +09:00 SQL> select sysdate +60 "오늘부터 60일 후" from dual; 오늘부터 -------- 14/11/07 아래 예문은 1970년 1월1일부터 오늘까지의 일수차이를 반환하는 예문이다. SQL> select trunc(sysdate - to_date('19700101','yyyymmdd')) from dual; TRUNC(SYSDATE-TO_DATE('19700101','YYYYMMDD')) --------------------------------------------- 16321