특정일자가 속하는 구간 구하기 0 8 3,295

by 마늘장 sql [2014.05.27 13:16:35]


 아래의 형태와 같이 lookup성 코드마스타가 주어져있고

임의의 Date형 parameter (2014/06/02)를 받았을 경우 그에 해당 qt를 뽑으려면 쿼리를 어떤식으로 계산해야 할지요?

위의 예에서는 2Q가 선택되어야 하겠으며, 각각 쿼터의 종료는 다음 쿼터 이전일까지가 되겠습니다.

WITH tmp AS

(SELECT '1Q' qt, 1 seq, '1201' sdt FROM dual UNION ALL

SELECT '2Q' qt, 2 seq, '0315' sdt FROM dual UNION ALL

SELECT '3Q' qt, 3 seq, '0615' sdt FROM dual UNION ALL

SELECT '4Q' qt, 4 seq, '0915' sdt FROM dual) SELECT * FROM tmp

by 마농 [2014.05.27 14:54:40]
WITH tmp AS
(
SELECT '1Q' qt, 1 seq, '1201' sdt FROM dual 
UNION ALL SELECT '2Q', 2, '0315' FROM dual 
UNION ALL SELECT '3Q', 3, '0615' FROM dual 
UNION ALL SELECT '4Q', 4, '0915' FROM dual
)
SELECT qt
  FROM (SELECT qt, seq, sdt
             , NVL( LEAD(sdt) OVER(ORDER BY seq)
                  , FIRST_VALUE(sdt) OVER(ORDER BY seq) ) edt
          FROM tmp
        )
     , (SELECT dt
             , SUBSTR(dt, 1, 4) yy
             , SUBSTR(dt, 1, 4)-1 yy_pre
             , SUBSTR(dt, 1, 4)+1 yy_next
          FROM (SELECT '20140602' dt FROM dual)
        )
 WHERE (sdt < edt AND yy    ||sdt <= dt AND yy     ||edt > dt) 
    OR (sdt > edt AND yy_pre||sdt <= dt AND yy     ||edt > dt) 
    OR (sdt > edt AND yy    ||sdt <= dt AND yy_next||edt > dt) 
;
-- 검색용 코드테이블이라면? 저라면 이렇게 설계할래요...
WITH tmp AS
(
SELECT '1Q' qt, 1 seq, 1 seq2, '1201' sdt, '1231' edt FROM dual 
UNION ALL SELECT '1Q', 1, 2, '0101', '0314' FROM dual 
UNION ALL SELECT '2Q', 2, 1, '0315', '0614' FROM dual 
UNION ALL SELECT '3Q', 3, 1, '0615', '0914' FROM dual 
UNION ALL SELECT '4Q', 4, 1, '0915', '1130' FROM dual
)
SELECT qt 
  FROM tmp
 WHERE SUBSTR('20140602', 5, 4) BETWEEN sdt AND edt
;

 


by 마늘장 [2014.05.27 15:44:33]

일단, qt로 적은 컬럼은 lookup_code PK값으로 중복 허락이 안됩니다.

말씀과 같이 1Q를 seq하게 넣을수는 없구요...

저도 각각의 쿼터별로 시작과 종료일로

sdt와 edt로 가려고 했는데 2월달이 걸리더군요...

2/29일이 있는 해의 경우 29로 설정을 하게되면 date 변환시 오류가 발생하는 해가 존재하고

28로 설정하면 29일이 있는해에 누락이 발생하는 문제가 있네요...

====================

1Q 2013-12-01  2014-02-28
 

따라서, 시작일만 기준값으로 가져가고 종료일은 다음쿼터의 시작 하루전일자로 계산하여 구간을 구하고 싶었습니다.

그럴경우 4Q를 1Q의 시작일자에 물려서 계산이 되도록 하려면 Mod(3)을 써서 서로 self join하는

형태가 되면 되지 않을까 싶습니다...


by 마농 [2014.05.27 16:05:02]

1. 종료일에 2/29 이 문제가 된다면?
   - 시작일도 마찬가지 문제가 발생되지 말란 법은 없을 듯 합니다.
   - 저의 경우엔 날짜 함수를 사용하지 않았습니다.
2. 2/28 로 설정하면 누락되는 문제는?
   - 3/1 로 설정하고 등호를 빼고 비교하면 되지요
3. 중복이 행이 허락되지 않는다면? 컬럼을 나누는 방안도 있겠지요.
   - 기간1, 기간2
4. 실제 입력 자료가 어떤지 궁금하네요?
   - 예시처럼 기간이 들쭉 날쭉 한지? 변경이 자주 일어나는지?
   - 일정한 규칙을 가지고 간다면 간편해지리라 생각됩니다.
5. self join 하셔도 됩니다.
   - 저는 self join 대신 분석함수를 이용했구요
   - 시작일과 종료일이 해가 바뀌는 부분에 대한 처리 때문에 복잡해질수밖에 없지요.


by 마늘장 [2014.05.27 16:27:53]

기간이 들쭉날쭉한것 같이 보이지만,

사실 기준은 명확합니다.

06.01 ~ 08.31 : 하절기

12.01 ~ 02.28 : 동절기

나머지는 ETC 입니다. 어떻게 보면 기준이 3가지라고 볼수도 있는데

날짜를 기준으로 계산을 하려보니 어려워 4가지 Qauter로 잡았습니다.

아무튼, 날짜가 하나 던져지면, 하절기, 동절기, 기타중 한가지로 구분을 해야하는것이고,

날짜 구간은 다소 유동적으로 변경 될수 있습니다.

해당 기준을 처리하기 위해 별도 테이블 구성없이 오라클의 lookup_code, description이라는 두개의 컬럼값으로 처리를 해야하며 lookup_code는 PK입니다.

 

 


by 마농 [2014.05.27 17:13:38]

제시하신 기간을 보면 시작일은 1일이고 종료일은 3개월 뒤네요.

맞나요? 맞다면 심플할 듯 하구요...

다소 유동적? 이라는 표현이 맘에 걸리긴하네요...

저는 이걸 염두에 두고 이걸 두고 들쭉 날쭉이란 표현을 쓴거네요.

from, to 를 하나의 행에 관리해야 쉽게 조회될 듯 합니다.

다음 행으로 부터 to 를 가져오려면 복잡해 지겠지요...

안그래도 해가 걸치는 부분때문에 복잡해 지는데요.


by 마늘장 [2014.05.27 17:58:15]

네, 날씨가 점점 아열대로 바뀌죠...

하절기가 해가 갈수록 길어질수 있겠네요 ^^

아래와 같이 단순 무식하게 Lookup table을 구현해봤습니다. 급한마음에...

WITH tmp AS
(
SELECT '1Q' qt, 1 seq, '1201' sdt FROM dual 
UNION ALL SELECT '2Q', 2, '0315' FROM dual 
UNION ALL SELECT '3Q', 3, '0615' FROM dual 
UNION ALL SELECT '4Q', 4, '0915' FROM dual
)
SELECT  qt
       ,DECODE(qt, '1Q', ADD_MONTHS(to_date(to_char(SYSDATE, 'YYYY')||sdt, 'YYYYMMDD'), -12)
                       , to_date(to_char(SYSDATE, 'YYYY')||sdt, 'YYYYMMDD')) start_date
       ,(SELECT  to_date(sdt, 'MMDD') - 1
           FROM  tmp b
          WHERE  b.seq = MOD(a.seq, 4) + 1) end_date
FROM tmp a
UNION ALL
SELECT  qt
       ,to_date(to_char(SYSDATE, 'YYYY')||sdt, 'YYYYMMDD')
       ,(SELECT  ADD_MONTHS(to_date(to_char(SYSDATE, 'YYYY')||sdt, 'YYYYMMDD'), +12) - 1
           FROM  tmp b
          WHERE  b.seq = MOD(a.seq, 4) + 1)
FROM tmp a
WHERE qt = '1Q'


by 마농 [2014.05.27 18:26:55]

원하시는게

특정일의 쿼터를 구하는게 아니었나보네요?

특정일의 쿼터를 구하기 위한 현재년도기준 5개의 기준 레코드가 필요했던 건가보네요?


by 마농 [2014.05.28 11:06:29]

"월일"만으로 매년 처리하기보다는 "년월일"을 함께 관리하는것을 권해드리고 싶네요.

기간은 매년 달라질 수 있을 것으로 예상되네요.

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