리스트 가져오는데 조건이 까다롭습니다. 0 7 1,042

by 보일듯말듯 [SQL Query] [2016.12.22 14:40:32]


아래 쿼리는 리스트를 가져오는 쿼리 입니다. (어제 문의 드린 프로시져에 있던 내용)

SELECT a.rent_mng_id, a.rent_l_cd, b.cls_dt, a.rent_start_dt, 
       decode(a.call_st , 'N', '9', cp.gubun ) AS gubun, a.client_id 
  FROM cont a, cls_etc b, client c, users i, users df, car_reg d, 
       (SELECT * 
         FROM doc_settle 
        WHERE doc_st='11'
       ) l, 
       (SELECT a.rent_mng_id, 
              a.rent_l_cd, 
              '1' AS gubun 
         FROM cont_call a 
        WHERE poll_id <> 0 
        GROUP BY a.rent_mng_id, 
              a.rent_l_cd
       ) cp, 
       (SELECT a.rent_mng_id, 
              a.rent_l_cd, 
              reg_id, 
              answer_date 
         FROM cont_call a 
        WHERE poll_id = 0 
       ) cpr 
 WHERE  
       a.car_st='4'  
       AND a.rent_start_dt IS NOT NULL 
       AND a.client_id=c.client_id 
       AND a.rent_mng_id=b.RENT_MNG_ID 
       AND a.rent_l_cd=b.rent_l_cd 
       AND a.car_mng_id = d.car_mng_id(+) 
       AND a.rent_l_cd=l.doc_id(+) 
       AND l.user_id1=i.user_id(+) 
       AND b.dft_saction_id=df.user_id(+) 
       AND a.rent_mng_id=cp.rent_mng_id(+) 
       AND a.rent_l_cd=cp.rent_l_cd(+) 
       AND a.rent_mng_id=cpr.rent_mng_id(+) 
       AND a.rent_l_cd=cpr.rent_l_cd(+) 
       AND b.cls_st='14'
        AND b.cls_dt > '20140131'  
       AND substr(b.cls_dt, 1, 4) =to_char(sysdate,'YYYY') 
       
ORDER BY decode(a.call_st , 'N', '9', cp.gubun ) desc, 
       l.doc_bit, 
       b.cls_dt desc , 
       a.rent_dt, 
       a.rent_start_dt, 
       a.rent_mng_id;
RENT_MNG_ID RENT_L_CD CLS_DT RENT_START_DT GUBUN CLIENT_ID
36543 S115HYFM00914 20161202 20151203   26175
41235 S116HHLM00244 20161202 20160603   29295
41858 S216HYFM00568 20161202 20160630   29726
42902 S116HACM00036 20161202 20160816   29681
43483 S116HHLM00419 20161202 20160909   23854
43821 I116HYFM00873 20161202 20160929   31126
44227 K316KSPM00189 20161202 20161019   31406
34661 S615HYFM00652 20161203 20150904   24892
44568 I116KK5M01127 20161203 20161104   31210
44570 S516KK5M01128 20161203 20161104   31660
43037 S616HYFM00754 20161204 20160822   30582
31131 S115HHGM00067 20161205 20150304   22635
37206 I116HYFM00008 20161205 20160106   23576
42721 G116HYFM00702 20161205 20160805   30378
43301 S116KK5M00954 20161205 20160902   30779
43346 S216KK5M00963 20161205 20160905   30810
43866 S216HYFM00881 20161205 20161004   31157
44574 S116KK7M00518 20161205 20161104   21382
44640 S116HYFM00994 20161205 20161109   31717
44734 S616HDMM00188 20161205 20161111   31590
32231 S215HYFM00301 20161206 20150430   23352
36590 I115HYFM00921 20161206 20151207   26206
41113 S116HYFM00469 20161206 20160531   19050
43960 S216HJMM00044 20161206 20161007   31222
43964 S116HYFM00899 20161206 20161007   26421
43976 K316HYFM00902 20161206 20161007   31231
44600 S216KK5M01130 20161206 20161107   31684
39823 S116KK5M00394 20161207 20160408   28348
43987 S516HYFM00904 20161209 20161010   31240

이 쿼리를 실행하면 이렇게 데이터가 나옵니다.  

근데 이 리스트중에서 b.cls_dt 기준으로 토,일,공휴일인 건은 다 나오고

그외 평일인건중에서 10% 만 리스트로 가져오려 합니다.

공휴일을 관리하는 테이블은 따로 아래처럼 있습니다.

SELECT * 
  FROM HOLIDAY 
 WHERE substr(hday, 1, 4) =to_char(sysdate,'YYYY') 
HDAY HDAY_NM SEQ
20160101 신정 176
20160208 설날 177
20160209 설날 178
20160301 삼일절 179
20160505 어린이날 180
20160606 현충일 181
20160815 광복절 182
20160914 추석 183
20160915 추석 184
20160916 추석 185
20161003 개천절 186
20161009 한글날 242
20160210 설날 250
20160506 임시공휴일 319
20160413 국회의원선거

251

 날짜 기준으로 주말인지 평일인지 구분이 되나요 ? 주말및 공휴일은 전부 하고 평일은 10%만 추출한다면

주말및 공휴일을 계산하는 쿼리를 작성하고 union all 써서 평일의 10% 추출하는 쿼리를 조인해주어서 처리해야 할까요?

by jkson [2016.12.22 15:07:36]

날짜 기준으로는 to_char(sysdate,'d')가 '1'이면 일요일 '7'이면 토요일입니다. 참고하셔서 작성하시면 되겠네요.


by 보일듯말듯 [2016.12.22 15:39:47]

감사합니다.


by 마농 [2016.12.22 15:14:04]

쓸데없이 사용된 테이블이 많아 보입니다.
a, b, c 는 Inner Join 이고
i, df, d, l, cp, cpr 은 Outer Join 이네요.
그런데 정작 사용되는 항목은 a, b, cp, l 뿐이네요?
Outer Join 되는 나머지 테이블들(i, df, d, cpr)은 필요 없어 보이구요.
Inner Join 되는 c 테이블이 필요한지도 검토 대상입니다.
Order By 절에만 사용되는 l 테이블도 꼭 필요한지 의문이네요?


by 보일듯말듯 [2016.12.22 15:40:45]

다른곳에서 사용하던 쿼리를 그대로 가져와서 그런것 같습니다.

정리해야지 하는 생각은 미쳐 못했습니다.


by 마농 [2016.12.22 15:43:05]
SELECT rent_mng_id
     , rent_l_cd
     , cls_dt
     , rent_start_dt
     , gubun
     , client_id
  FROM (SELECT rent_mng_id
             , rent_l_cd
             , cls_dt
             , rent_start_dt
             , gubun
             , client_id
             , h_gb
             , COUNT(*) OVER(PARTITION BY h_gb) cnt
             , ROW_NUMBER() OVER(PARTITION BY h_gb ORDER BY dbms_random.value) rn
          FROM (SELECT a.rent_mng_id
                     , a.rent_l_cd
                     , b.cls_dt
                     , a.rent_start_dt
                     , DECODE(a.call_st, 'N', '9', cp.gubun) AS gubun
                     , a.client_id
                     , CASE WHEN h.hday IS NOT NULL
                              OR TO_CHAR(TO_DATE(b.cls_dt, 'yyyymmdd'), 'd') IN ('1','7')
                            THEN 1 ELSE 0 END h_gb
                  FROM cont    a
                     , cls_etc b
                     , holiday h
                     , (SELECT rent_mng_id
                             , rent_l_cd
                             , '1' AS gubun
                          FROM cont_call
                         WHERE poll_id <> 0
                         GROUP BY rent_mng_id, rent_l_cd
                        ) cp
                 WHERE 1=1
                   AND a.rent_mng_id    = b.RENT_MNG_ID
                   AND a.rent_l_cd      = b.rent_l_cd
                   AND b.cls_dt         = h.hday(+)
                   AND a.rent_mng_id    = cp.rent_mng_id(+)
                   AND a.rent_l_cd      = cp.rent_l_cd(+)
                   AND a.car_st         = '4'
                   AND a.rent_start_dt IS NOT NULL
                   AND b.cls_st         = '14'
                   AND b.cls_dt         > '20140131'
                   AND b.cls_dt         >= TO_CHAR(sysdate, 'yyyymmdd')
                )
        )
 WHERE h_gb = 1
    OR rn  <= cnt * 0.1
;

 


by 보일듯말듯 [2016.12.22 17:29:02]

감사합니다.

아래부분 괄호2개중 1개 오타인거죠? 

CASE WHEN h.hday IS NOT NULL
        OR TO_CHAR(TO_DATE(b.cls_dt, 'yyyymmdd'), 'd') )IN ('1','7')   << 여기 IN 전에 괄호 2개
THEN 1 ELSE 0 END h_gb

그리고 이 부분 설명 부탁드려도 될까요?

 ROW_NUMBER() OVER(PARTITION BY h_gb ORDER BY dbms_random.value) rn

다시 한번 감사드립니다.


by 마농 [2016.12.22 17:42:04]

1. 오타 수정했구요.
2. 랜덤으로 10% 가져오기 위해 순번을 부여하는 구문입니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입