농부지기

☞ 결과가 월

다음월 

select to_char(add_months(to_date('20020201', 'yyyymmdd'), 1),'yyyymm')
from   dual;

millisecond 보기

select to_char(current_timestamp, 'yyyy.mm.dd HH24:MI:SS-XFF') from dual;

select to_char(sysdate, 'yyyy.mm.dd HH:MI:SS')   from  dual;

         select to_char(sysdate, 'yyyy.mm.dd HH24:MI:SS')   from  dual;

to_date에서 (일자, 'YYYYMMDDHH24MI') 일 경우
00:00 시 ~ 23:59  까지만 가능하다. 즉, 일자에  '200409202400'  으로 하게 되면

      out_return final-->-1850,ORA-01850: hour must be between 0 and 23  오류가 발생한다.

날수/월수/년수 구하기

두날짜 사이의 시간구하기

TRUNC((to_date(종료일자,'YYYYMMDDHH24MI')
     - to_date(시작일자,'YYYYMMDDHH24MI')) * 24, 2);

두날짜 사이의 월수구하기

MONTHS_BETWEEN((TO_DATE('종료일자') + 1,
                TO_DATE('시작일자')));

년수 구하기
       

TRUNC(MONTHS_BETWEEN(TO_DATE('종료일자') + 1,                      TO_DATE('시작일자')) / 12, 0)

타입 변경

문자 타입을 --> 날짜 타입으로 변경 : TO_DATE(문자형 날짜, '형태')
                                                             (형태 예, 'YY/M/DD')

날자 타입을 --> 문자 타입으로 변환  : TO_CHAR(sysdate, 'DD/MM/YY')

☞ 일 / 월 / 년  올림  (참고, 절사일 경우는 TRUNCATE 로 하면 된다.)

12시 이후면 올림 :  SELECT ROUND(SYSDATE, 'DAY') FROM DUAL ;

15일 이후면 올림 :  SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL ;

6월 이후면 올림  :  SELECT ROUND(SYSDATE, 'YEAR') FROM DUAL ;

기타

요일 구하기  :  SELECT LAST_DAY(DATE) FROM DUAL ;

☞ 시한편

(12) 기타

메쏘드명          설명
========================
daysBetween    날짜간 일수구하기
addDays        일수 더하기
whichDay       요일구하기
monthsBetween  날짜간 월수구하기
addMonths      월수 더하기
lastDayOfMonth 그 달의 마지막날짜 구하기
addYears       년수 더하기

☞ Analytic 함수 List  

 1) AVG* 함수
 2) CORR* CORR* 함수
 3) COUNT* 함수
 4) COVAR_SAMP 함수
 5) CUME_DIST 함수
 6) DENSE_RANK 함수
 7) FIRST 함수
 8) FIRST_VALUE 함수
 9) LAG 함수
 10) LAST_VALUE 함수
 11) LEAD 함수
 12) NTILE 함수
 13) RATIO_TO_REPORT 함수
 14) ROW_NUMBER 함수

☞ LAG 함수

♧ 정의 : self join하지 않고 하나의 테이블에서 동시에 한 행(row)이상을 접근하여 자료를 가져
          올 수 있다. 즉, n개의 Record를 조회할 때 현재 Record와 이전Record의 값을 한Record로
          보여줄 수 있다.
          - 반대로 하위 행을 찾는 함수는 LEAD 함수 이다.

【Version】 ORACLE 8.1.6

【형식】LAG(value_expr [,offset] [,default] ) OVER
           (PARTITION BY [partition_clause] ORDER BY [order_by_clause] )

        . value_expr       : 이전,이후 레코드의 찾고자하는 컬럼. 반드시 컬럼 한 개만 필요
        . order_by_clause  : 이전,이후레코드를 알기 위한 정렬
        . offset           : 1 - 이전 첫 번째 레코드를 찾아옴
                             2 - 이전 두 번째 레코드를 찾아옴
                             즉, 해당 offset 숫자만큼 이전 레코드를 찾는다. 반드시 양수임
        . default          : ??
        . partition_clause : Group by 와 같은 의미 

【예제】SQL> SELECT name,salary,LAG(salary,1,0)  OVER (ORDER BY salary)
             FROM employees;

【결과】 NAME           SALARY    LAG(SALARY,1,0)OVER(ORDERBYSALARY)
         ---------- ----------    ----------------------------------
         jijoe             220                                  0
         Joe               240                                220
         Cho               250                                240
         kim               250                                250  

☞ NVL

1. NULL값을 특정한 값으로 전환
2. 사용예) SELECT  NVL('', 'NULL') FROM DUAL;
     결과) NULL    

☞ NULLIF

1. 정의 : 두값을 비교하여 같으면 NULL을 돌려주고, 다르면 첫 번째 인자값을 돌려주는 함수
2. 사용예) SELECT  NULLIF('kim', 'YONG') FROM DUAL;
     결과)  kim
   사용예) SELECT  NULLIF('kim', 'kim') FROM DUAL;
     결과)  

☞ COALESCE  

1. 정의 : NVL함수를 일반화한 것이다.
          여러 행중에서 첫 번째인자가 NULL이 아니면 첫 번째값을
                        첫 번째인자가 NULL이면      두 번째값을
                        첫 번째, 두 번째인자 모두 NULL이면 세 번째 값을 돌려주는 함수
2. 형식 : COALESCE(첫번째값, 두번째값, 세번째값)  
3.   문제) 사원급여가 존재시 사원급여를,  사원급여가 null이면 기본급을, 둘다 null이면 100만원을
           RETURN시켜라  
   사용예) SELECT  COALESCE(PAY_MON, BASE_MON, 1000000) FROM 급여_TABLE ;
     결과)  
4. 참고) COALESCE(expr1,expr2,expr3,…)
       = CASE WHEN expr1 IS NOT NULL
              THEN expr1
              ELSE COALESCE(expr2,expr3,…)
         END;

☞ DECODE

1. 정의 : 값을 =(equal)로 비교하여 해당값하고 같을 경우 해당값을 RETURN
2. 사용예) SELECT  DECODE('KIM', 'KIM', '성씨', '이름') FROM DUAL;
     결과) 성씨    

☞ CASE

1. 정의 : 값을 =(equal), LIKE, <> 등 비교 연산을 하여 치환값을 RETURN
2. 사용예) SELECT  CASE WHEN  직위  like '%부장%' THEN 기본급 * 1.5%
                        WHEN  직위  like '%차장%' THEN 기본급 * 1.3%
                        WHEN  직위  like '%과장%' THEN 기본급 * 1.2%
                        ELSE  기본급
           FROM 급여_TABLE ;
     결과) 

☞ RANK() OVER

1. 정의 : 순위를 구하는 함수
2. 형식1: RANK() OVER  (ORDER BY 컬럼1 ) 
          - 컬럼1을 기준으로 ORDER BY 후 순위가 부여된다.
   형식2: RANK() OVER  ([PARTITION BY 컬럼1] ORDER BY 컬럼2) 
          - 컬럼1을 기준으로 GROUP 하고 컬럼2로 ORDER BY 후 순위가 부여 된다.
            그래서 컬럼1별로 순위가 부여 된다.
            즉, 만약, 부서별로 급여 순위를 부여하고 싶을 경우 아래와 같다.
3. 사용예) SELECT  RANK() OVER (PARTITION BY 부서 ORDER BY 급여액 DESC)
           FROM    급여_TABLE ;
     결과)  

☞ 

1.

☞ 전각을 반각으로 변환

1. 정의 : HOST에서 사용하는 전각문자를 WIN에서 사용하는 반각문자로 변환
2. 형식 : TO_SINGLE_BYTE()

☞ 반각을 전각으로 변환

1. 정의 : HOST에서 사용하는 전각문자를 WIN에서 사용하는 반각문자로 변환
2. 형식 : TO_MULTI_BYTE()

☞

내용 입력

☞ COUNT

1. 정의 : 조건에 맞는 행의 개수를 구함
2. 사용예) SELECT  COUNT(DUMMY) FROM DUAL;
     결과) 1   

☞ AVG

1. 정의 : 조건에 맞는 행값의 평균을 구함
2. 사용예) SELECT  AVG(PAY_MON) FROM EMP_PAY;
     결과)   

☞ SUM

1. 정의 : 조건에 맞는 행값의 합계를 구함
2. 사용예) SELECT  SUM(PAY_MON) FROM EMP_PAY;
     결과)  

☞ MIN

1. 정의 : 조건에 맞는 행값들 중 최소값을 구함
2. 사용예) SELECT  MIN(PAY_MON) FROM EMP_PAY;
     결과)     

☞ MAX  

1. 정의 : 조건에 맞는 행값들 중 최대값을 구함
2. 사용예) SELECT  MIN(PAY_MON) FROM EMP_PAY;
     결과)     

☞ STDDEV  

1. 정의 : 표준편차를 구함
2. 사용예)
     결과) 

☞ VARIANCE

1. 정의 : 분산을 구함
2. 사용예)
     결과) 

3-14) TO_CHAR

1. 정의 : 숫자나 날짜를 문자열로 변환

2. 형식 : TO_CHAR(d, n) 
          - d : 숫자나 날짜타입 데이터
          - n : 변환_형식 

3. 사용예) SELECT  TO_CHAR(3980000, '$9,999,999') FROM DUAL;
     결과)  $3,980,000   (단, 숫자 이므로 $앞에 공백한칸이 붙게 된다)
   사용예) SELECT  TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
     결과) 2008-03-17 

4. 숫자용 포맷
  --------------------------------
   형식          설명
  --------------------------------
    9            숫자의 출력
    0            숫자열 앞에 0 표시
    .(소수점)    소수점 자리 표시
    ,(콤마)      지정한 위치에 콤마 표시
    $            달러 표시
  --------------------------------
    L            국가별 화폐단위 표시
    MI           오른쪽에 마이너스 부호
    EEEE         과학적 표기
  --------------------------------

5. 날짜를 문자변경 시 포맷
  ------------------------------------------------------------------------------
      형식                    설명
  ------------------------------------------------------------------------------
     YYYY  또는 SYYY          연도,S를 지정하면 기원전 연도에 -가 붙음.
     YYY  YY  또는 Y          년의 아래 3자리, 2자리, 1자리
     SYEAR 또는 YEAR          년을 철자로
     Q                        분기
     MM                       월
     MONTH 또는 MON           월의 명칭 또는 3문자의 단축형
     DDD  DD  또는 D          년,월,주의 몇 번째 날인가를 보여줌
     DAY  또는 DY             요일 또는 3문자의 단축형
     AM 또는 PM               오전과 오후 표시
     A.M. 또는 P.M.           오전과 오후 표시
     HH  또는 HH12            시간 (1-12)
     HH24                     시간(0-23)
     MI                       분
     SS                       초
  ------------------------------------------------------------------------------
     TH                       서수(DDPH라고 지정하면 31TH와 같이 나옴)
     SP                       숫자를 철자로 보여준다. (DDSP -> FOUR)
     SPTH  또는 THSP          서수를 철자로 보여준다. (DDSPTH => FOURTH)
  ------------------------------------------------------------------------------

☞ TO_NUMBER   

1. 정의 : 문자타입을 숫자타입으로 변환  
2. 사용예) SELECT  TO_NUMBER('10')) + 20 FROM DUAL;
     결과) 30

☞ TO_DATE  

1. 정의 : 문자타입을 날짜타입으로 변환
2. 형식 : TO_DATE(날짜형_문자값, 변환_형식) 
2. 사용예) 
     결과)  

☞  ADD_MONTHS 

1. 정의 : 지정한 날짜로부터 특정 개월을 더한 날짜를 구함 
2. 사용예) SELECT  ADD_MONTHS(SYSDATE, 25) FROM DUAL;
     결과) 2010/04/17 17:04:36   
3. 참고 : -(minus)값일 경우  월을 차감한다.

1. 정의 : 현재 시스템의 날짜 및 시간을 구함
2. 사용예) SELECT  SYSDATE FROM DUAL;
     결과) 2008/03/17 16:52:16   

☞ LAST_DAY

1. 정의 : 지정한 날짜의 해당 월의 말일을 구함
2. 사용예) SELECT  LAST_DAY(SYSDATE) FROM DUAL;
     결과) 2008/03/31 16:52:16

1. 정의 : 지정한 날짜로부터 가장 가깝게 다가올 해당 요일의 날짜를 구함
2. 기능 : 요일구분 : 1 - 일요일
                     2 - 월요일 ....
                     7 - 토요일
3. 사용예) SELECT  NEXT_DAY(TO_DATE('20080317'), 1) FROM DUAL;
     결과) 2008/03/22 00:00:00 

1. 정의 : 두 날짜 상이의 개월 수를 구함
2. 사용예) SELECT  MONTHS_BETWEEN(TO_DATE('20100505'), SYSDATE) FROM DUAL;
     결과) 25.5900....   

1. 정의 : 날짜에 대한 요일 구하기 
2. 사용예) SELECT  TO_CHAR(SYSDATE, 'DY') FROM DUAL;
     결과) 환경에 따라서 : 월,화,수,~~    일  (한글)
                           MON,THE,WED~~, SUN  (영문)
     * 항상 한글로 원할 경우
           (Oracle속성을 바꿀 수도 있지만 아래와 같이 한 sql에서만 적용시킬수도 있다)
           SELECT  TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=KOREAN') FROM DUAL;

   사용예2) SELECT  TO_CHAR(SYSDATE, 'D') FROM DUAL;
       결과) 1,2 ~~ 7;  --숫자로 나옴 (1:일, 2:월, ~~  7:토요일)

☞ 월별 주차 구하기

1. 정의 : 해당 월별로 주차(1~6주)를 구한다.
2. 1주를 -> 일 ~ 토 까지 기준일 경우 SQL
   SELECT '20101205'
     , CEIL((substrb('20101205', -2, 2) + 7 - TO_CHAR(TO_DATE('20101205', 'yyyymmdd'), 'D')) / 7) as "주차"
  FROM DUAL;

3. 1주를 -> 월 ~ 일 까지 기준일 경우 SQL - 종류1 (만약, 매월1일이  일요일이면   02일을 1주로 본다.)
   SELECT  DECODE(WC, 0, 1, WC)
   FROM  (
           SELECT '20081231' DT
                , CEIL((substrb('20081231', -2, 2) + 7 - DECODE(TO_CHAR(TO_DATE('20081231', 'yyyymmdd'), 'D'), 1, 1, 0)
                        - DECODE(TO_CHAR(TO_DATE('20081231','yyyymmdd'),'D'), 1, 7, (TO_CHAR(TO_DATE('20081231','yyyymmdd'),'D')))
                       ) / 7) as WC
             FROM DUAL
         )
    ;

3. 1주를 -> 월 ~ 일 까지 기준일 경우 SQL - 종류2 (만약, 매월1일이  일요일이면   02일을 1주로 본다.)
                                         - 위 종류1 SQL문장을 좀더 보기 좋게  종류2 SQL 문으로 단순 변화만 시켰음.
  SELECT CEIL((DD + 7 - SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR('20090302', -2, 2)                                                                                   AS DD
             , DECODE(SUBSTR('20090302', -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE('20090302', 'yyyymmdd'), 'D'), 1, 1, 0))  AS SUN
             , TO_CHAR(TO_DATE('20090302', 'yyyymmdd'), 'D')                                                               AS YO
        FROM   DUAL
       ) ;

3. 1주를 -> 월 ~ 일 까지 기준일 경우 SQL - 종류3  (만약, 매월1일이  일요일이면   02일을 2주로 본다.)
  SELECT CEIL((DD + 7 + SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR('20090301', -2, 2)                                                                                    AS DD
             , DECODE(SUBSTR('20090301', -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE('20090301', 'yyyymmdd'), 'D'), 1, -1, 1))  AS SUN
             , TO_CHAR(TO_DATE('20090301', 'yyyymmdd'), 'D')                                                                AS YO
        FROM   DUAL
       ) ;

A. 정의 : 월별  몇주차까지 존재 하는지 조회
   예문1 : 201012   월에 대해서   몇주차까지 있는지 조회

  SELECT CEIL((DD + 7 + SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR(WORKDT, -2, 2)                                                                                AS DD
             , DECODE(SUBSTR(WORKDT, -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D'), 1, -1, 1))  AS SUN
             , TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D')                                                            AS YO
        FROM   (SELECT TO_CHAR(ADD_MONTHS('201012' || '01', 1) - 1, 'YYYYMMDD') WORKDT FROM DUAL)
       ) ;
  결과 : 5

   예문2 : 2010년도 1~12월까지  각월별 주차 구하기

  SELECT CEIL((DD + 7 + SUN- DECODE( YO, 1, 7, YO)) / 7) as WC
  FROM (
        SELECT SUBSTR(WORKDT, -2, 2)                                                                                AS DD
             , DECODE(SUBSTR(WORKDT, -2, 2), '01', 0, DECODE(TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D'), 1, -1, 1))  AS SUN
             , TO_CHAR(TO_DATE(WORKDT, 'yyyymmdd'), 'D')                                                            AS YO
        FROM   (SELECT TO_CHAR(ADD_MONTHS('201012' || '01', 1) - 1, 'YYYYMMDD') WORKDT
                FROM DUAL, (SELECT to_char(rownum, '00') rn FROM dual connect by level <= 12)
                )
       ) ;

☞ 년간 주차 구하기

1. 정의 : 년간 주차 (1~52주)를 보여준다.

2. 한 날짜에 대한 년간 주차 구하기
  SELECT '20090115' as "날짜"
       , CEIL(  (TO_CHAR(TO_DATE('20090115','yyyymmdd'),'ddd')
              + TO_CHAR(TO_DATE(SUBSTR('20090115',1,4)||'0101','yyyymmdd'),'d') - 1) / 7)   DDATE
   FROM dual

3. 2010-12-10 부터 2011-01-15 까지 두 날짜 사이의 년간주차를 구하기

   SELECT TO_CHAR(TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW') + 5, 'YYYY')  YYYY
        , TO_CHAR(TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW'), 'IW') WW
        , TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW') - 1  BEGIN_DAY
        , TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD') + ((LEVEL-1)*7), 'IW') + 5  END_DAY
    FROM DUAL
   CONNECT BY LEVEL <= (  (TRUNC(TO_DATE('2011-01-15','YYYY-MM-DD'), 'IW')+5) -- 검색종료일
                        - (TRUNC(TO_DATE('2010-12-10','YYYY-MM-DD'), 'IW')-1) -- 검색시작일
                        + 1
                       ) / 7 + 1 ;

☞ 시간 더하기

WITH T(DT) AS
(
               SELECT SYSDATE - (1/24) * 2    FROM DUAL  --현재시각에서 2시간 전
     UNION ALL SELECT SYSDATE - (1/24) * 1    FROM DUAL  --현재시각에서 1시간 전
     UNION ALL SELECT SYSDATE + (1/24) * 0.5  FROM DUAL  --현재시각에서 30분 후
     UNION ALL SELECT SYSDATE + (1/24) * 1    FROM DUAL  --현재시각에서 1시간
     UNION ALL SELECT SYSDATE + (1/24) * 2    FROM DUAL  --현재시각에서 2시간 후
     UNION ALL SELECT SYSDATE + (1/24) * 2.25 FROM DUAL  --현재시각에서 2시간 20분 후
)
SELECT DT
  FROM T ;
 

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24MISS'               ) C_TIME,   -- 현재시간
       TO_CHAR(SYSDATE + 1/24,'YYYYMMDD HH24MISS'         ) TIME,     -- 1시간 후
       TO_CHAR(SYSDATE + 40/(24*60),'YYYYMMDD HH24MISS'   ) MIN,      -- 40분 후
       TO_CHAR(SYSDATE + 10/(24*60*60),'YYYYMMDD HH24MISS') SEC       -- 10초 후
  FROM DUAL;

1. 정의 : 날짜에 대한 반올림처리

1. 정의 : 날짜에 대한 버림처리