ABOUT ME

-

Today
-
Yesterday
-
Total
-

  • [오라클/SQL] 날짜함수(SYSDATE, ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN) , 변환함수(TO_CHAR, NVL), DB속도 튜닝
    개발자 레퍼런스 2009. 11. 6. 21:10
    반응형

    날짜함수


    * SYSDATE


    SYSDATE
    --현재 시스템의 날짜 데이터를 반환

    SQL> SELECT TO_CHAR(sysdate, 'yyyymmddhh24mmss') FROM dual
    결과 : 20091106201101


    * ADD_MONTHS

    ADD_MONTHS(date, integer)
    지정한 날짜에서 해당월에 일정한 정수(혹은 마이너스 정수)값을  더한 날짜를 반환

    SQL> SELECT TO_CHAR(ADD_MONTHS(sysdate,-2), 'YYYY-MM-DD') FROM dual
    결과 : 2009-09-06

    * LAST_DAY

    LAST_DAY(date)
    해당월의 마지막 날짜를 반환한다.
    SQL> SELECT LAST_DAY(sysdate) FROM dual 
    결과: 2009/11/30 20:57:43  (현재일자는 2009/11/06)

    * MONTHS_BETWEEN

    MONTHS_BETWEEN(Date_1, Date_2)
    Date_1 과 Date_2 사이의 기간을 월로 나타내 준다.
    (한달 이내일 경우나 정확한 달로 맞아떨어지지 않을 경우는 소수점으로 표시)

    SQL> SELECT MONTHS_BETWEEN('20091101','20090801') FROM dual
    결과 : 3
    SQL> SELECT MONTHS_BETWEEN('20091115','20090830') FROM dual

    결과 : 2.51612903225806451612903225806451612903

    변환함수

    * TO_CHAR 함수


    TO_CHAR(NUMBER, "Format")
    -- NUMBER형 데이타 타입을 문자열로 변환

    SQL> SELECT TO_CHAR(233900, '$999,999') FROM dual
    결과: $233,900
    SQL> SELECT TO_CHAR(to_date(20090112), 'YYYY-MM-DD') FROM dual
    결과: 2009-01-12

    * NVL 함수

    NVL(ex1, ex2)
    -- ex1 값이 NULL이 아니라면 ex1의 값, NULL이라면 ex2의 값을 반환

    SELECT NVL( 20, 0 ) tup_c FROM dual
    결과: 20
    SELECT NVL( '', 0 ) tup_c FROM dual
    결과: 0


    레벨업 쿼리문 1

    ** 날짜함수 및 변환함수 사용 예제
    (입력값으로 YYYYMMDD를 받아 YYYY(MM-1)25 ~ YYYY(MM-1)31 일자의 범위내의 해당 데이터를 읽어오는 쿼리문)

    SELECT 
              count(apv_tr_rqr_am) AS io_un_byng_cn
        ,sum(apv_tr_rqr_am) AS io_un_byng_am
     FROM   tb_cs_az_cm_dom_crd_az
     WHERE  cd_no IN (
                                   SELECT cd_no
                                   FROM tb_cs_ae_cm_gencd_bsc
                                   WHERE cd_natv_no =
                                                                  ( SELECT cd_natv_no
                                                                    FROM tb_cs_ae_cm_gencd_bsc
                                                                    WHERE cd_no = :cd_no
                                                                  )
                                 )
              -- 전월의 25~31 일 범위                                   
     AND tr_dt BETWEEN TO_CHAR(ADD_MONTHS(to_date(substr(:bas_dt,1,6),'yyyymm'), -1 ),'YYYYMM') ||'25'  AND TO_CHAR(ADD_MONTHS(to_date(substr(:bas_dt,1,6),'yyyymm'), -1 ),'YYYYMM')  ||'31'
     


    레벨업 쿼리문 2

    ** DBIO 속도튜닝 예제
    (일정데이터 기준일자 이후의 모든건 및 최신건을 가져오며 서로 다른 테이블에서 각각 명을 참조하는 쿼리문)
    DB의 풀 조회 횟수를 최대한 줄이고 가능한 조건내에서 선택적으로 값을 가져옴으로서 속도면에서 최적화

    SELECT
           x1.mcht_grp_dsc
         , x1.mcht_mbdc
         , CASE 
              WHEN x1.mcht_mbdc_length < 11 THEN x2.mcht_mtalnm 
              ELSE x3.mcht_mtalnm
          END AS mcht_mtalnm

         , x1.apl_st_dt
         , x1.apl_ed_dt
         , x1.wkr_mbdy_no || ' ' || x4.empnm AS io_wkr_nm
         , x1.wk_dtm
    FROM (
           SELECT t2.mcht_grp_dsc
                , t2.mcht_mbdc
                , t2.apl_st_dt
                , t2.apl_ed_dt
                , t2.wkr_mbdy_no
                , t2.wk_dtm
                , length(t2.mcht_mbdc) mcht_mbdc_length
           FROM ( SELECT   *     
                  FROM(  -- 기준일자 적용건
                        SELECT mcht_grp_dsc
                             , mcht_mbdc
                             , apl_st_dt
                        FROM  tb_cs_jh_ch_mchtgrp
                        WHERE mcht_grp_no = :mcht_grp_no
                        AND   mcht_grp_dsc = :mcht_grp_dsc
                        AND apl_ed_dt >= :apl_st_dt      /* 기준일자 :apl_st_dt */
                       
                        UNION
                       
                        -- 최신건
                        SELECT mcht_grp_dsc
                             , mcht_mbdc
                             , max(apl_st_dt) apl_st_dt
                        FROM  tb_cs_jh_ch_mchtgrp
                        WHERE mcht_grp_no = :mcht_grp_no
                        AND   mcht_grp_dsc = :mcht_grp_dsc
                        GROUP BY  mcht_grp_dsc, mcht_mbdc
                      )
                  ORDER BY mcht_mbdc
                ) t1,
                tb_cs_jh_ch_mchtgrp t2
           WHERE t1.mcht_grp_dsc = t2.mcht_grp_dsc
             AND t1.mcht_mbdc    = t2.mcht_mbdc
             AND t1.apl_st_dt    = t2.apl_st_dt
             AND ((t1.mcht_mbdc > :mcht_mbdc)
                     OR (t1.mcht_mbdc = :mcht_mbdc AND t1.apl_st_dt >= :io_nx_apl_st_dt))

           GROUP BY t2.mcht_grp_dsc, t2.mcht_mbdc, t2.apl_st_dt, t2.apl_ed_dt
                           , t2.wkr_mbdy_no, t2.wk_dtm
           ORDER BY t2.mcht_grp_dsc, t2.mcht_mbdc       
           ) x1
           , tb_cs_mc_cm_bsc x2
           , tb_cs_mc_cm_tup_bsc x3
           , tb_cs_ui_cm_emp x4  -- 공통_직원정보

    WHERE x1.mcht_mbdc = x2.mcht_no(+)
    AND   x1.mcht_mbdc = x3.aflcn_mcht_no(+)
    AND   x1.wkr_mbdy_no = x4.eno(+)
    반응형

    댓글

Designed by Tistory.