꿈꾸는 개발자, DBA 커뮤니티 구루비

주차에 해당하는 데이터 만들기

by 트집이 [SQL Query] oracle plsql 주차 [2023.03.23 10:12:17]

안녕하세요. 주차 관련해서 데이터를 생성하려고 하는데 도움 부탁드립니다.

아래와 같은 오라클 데이터가 있다고 가정을 하고

데이터 타입은 YEAR, MONTH, COST 모두 Number형입니다.

YEAR	MONTH	COST
2022	12	199
2023	1	195
2023	3	100

위 데이터를 이용해서 아래와 같은 데이터를 만들고 싶은데 방법이 있을까요?

예를 들면 2023년 1월에 195이라는 데이터가 있다면 1월에 해당하는 주차를 만들고 각 주차별로 해당월의 데이터를 넣어주고 싶어요.

주차의 시작은 일요일을 기준으로 합니다.

YEAR	MONTH	WEEK	COST
2022	12	49	199
2022	12	50	199
2022	12	51	199
2022	12	52	199
2023	1	1	195
2023	1	2	195
2023	1	3	195
2023	1	4	195
2023	1	5	195
2023	3	10	100
2023	3	11	100
2023	3	12	100
2023	3	13	100

감사합니다.

by 마농 [2023.03.23 10:42:06]

ISO 기준 주차인 IW 는 월요일을 기준으로 합니다.
오라클에서 IW, IYYY 변환 지원하구요.
일요일 기준이라면? 별도로 기준을 잡아 처리해야 하는데요.
연, 월 등이 겹치는 주차에 대한 명확한 기준 정립이 필요합니다.
결과표에 연, 월, 주차 에 대한 시작일과 종료일을 표시해 주세요.

by 마농 [2023.03.23 11:31:41]

WITH t AS
(
SELECT 2022 year, 12 month, 199 cost FROM dual
UNION ALL SELECT 2023,  1, 195 FROM dual
UNION ALL SELECT 2023,  3, 100 FROM dual
)
SELECT year, month, cost
     , w
     , (s - y) / 7 + w week
     , TO_CHAR(s + w*7 - 6, 'yyyymmdd') sdt
     , TO_CHAR(s + w*7    , 'yyyymmdd') edt
  FROM (SELECT year, month, cost
             , NEXT_DAY(TO_DATE(year||'01' , 'yyyymm') - 1, 1) y -- 연 첫번째 일요일(연 1주차 시작일)
             , NEXT_DAY(TO_DATE(year||month, 'yyyymm') - 1, 1) s -- 월 첫번째 일요일(월 1주차 시작일)
             , LAST_DAY(TO_DATE(year||month, 'yyyymm')) e        -- 월 마지막 일자
          FROM t
        )
     , (SELECT LEVEL w FROM dual CONNECT BY LEVEL <= 5)
 WHERE w <= (e - s) / 7 + 1
 ORDER BY year, month, w
;

by 트집이 [2023.03.28 19:36:01]

추가 질문인데요. 2월은 원본 테이블에도 데이터가 없습니다.

이럴경우 결과에는 2월을 NULL 값으로 데이터를 만들 수가 있을까요?

아래와 같이...

2022   12   199   1   49   20221204   20221210
2022   12   199   2   50   20221211   20221217
2022   12   199   3   51   20221218   20221224
2022   12   199   4   52   20221225   20221231
2023   1   195   1   1   20230101   20230107
2023   1   195   2   2   20230108   20230114
2023   1   195   3   3   20230115   20230121
2023   1   195   4   4   20230122   20230128
2023   1   195   5   5   20230129   20230204

2023 2 NULL 1 6 20230205 20230211

2023 2 NULL 2 7 20230212 20230218

2023 2 NULL 3 8 20230219 20230225

2023 2 NULL 4 9 20230226 20230304
2023   3   100   1   10   20230305   20230311
2023   3   100   2   11   20230312   20230318
2023   3   100   3   12   20230319   20230325
2023   3   100   4   13   20230326   20230401

감사합니다.

by 마농 [2023.03.29 17:55:56]

요구하신 사항은
- 테이블로부터 바로 뽑는게 아니라
- 시작, 종료 검색조건을 입력받아서 뽑아야 하는 것입니다.

by 트집이 [2023.03.28 17:43:26]

WITH t AS
(
SELECT 2022 year, 12 month, 199 cost FROM dual
UNION ALL SELECT 2023,  1, 195 FROM dual
UNION ALL SELECT 2023,  3, 100 FROM dual
)
SELECT year, month, cost
     , w
     , (s - y) / 7 + w week
<span style="color:#ff0000">     , TO_CHAR(s + w*7 - 7, 'yyyymmdd') sdt
     , TO_CHAR(s + w*7 - 1, 'yyyymmdd') edt</span>
  FROM (SELECT year, month, cost
             , NEXT_DAY(TO_DATE(year||'01' , 'yyyymm') - 1, 1) y -- 연 첫번째 일요일(연 1주차 시작일)
             , NEXT_DAY(TO_DATE(year||month, 'yyyymm') - 1, 1) s -- 월 첫번째 일요일(월 1주차 시작일)
             , LAST_DAY(TO_DATE(year||month, 'yyyymm')) e        -- 월 마지막 일자
          FROM t
        )
     , (SELECT LEVEL w FROM dual CONNECT BY LEVEL <= 5)
 WHERE w <= (e - s) / 7 + 1
 ORDER BY year, month, w
;

sdt와 edt 날짜를 하루씩 더 빼줬어요.

by 트집이 [2023.03.30 09:47:33]

WITH t AS
(
    SELECT 2022 year, 12 month, 199 cost FROM dual
    UNION ALL SELECT 2023,  1, 195 FROM dual
    UNION ALL SELECT 2023,  3, 100 FROM dual
), t2 AS (
    SELECT  TO_CHAR(DT, 'yyyymmdd') SDT
          , TO_CHAR(DT, 'WW') WEEK
    FROM    ( 
            SELECT  TO_DATE('20221211','YYYYMMDD') + LEVEL - 1 DT
                  , NEXT_DAY(SYSDATE-7, 1) THISDATE
            FROM    DUAL 
            CONNECT BY LEVEL <= TO_DATE('20230326','YYYYMMDD') - TO_DATE('20221211' ,'YYYYMMDD') + 1
            )
    WHERE   TO_CHAR(DT,'D') = '1'
), t3 AS (
SELECT year, month, cost
     , w
     , (s - y) / 7 + w week
     , TO_CHAR(s + w*7 - 7, 'yyyymmdd') sdt
     , TO_CHAR(s + w*7 - 1, 'yyyymmdd') edt
  FROM (SELECT year, month, cost
             , NEXT_DAY(TO_DATE(year||'01' , 'yyyymm') - 1, 1) y -- 연 첫번째 일요일(연 1주차 시작일)
             , NEXT_DAY(TO_DATE(year||month, 'yyyymm') - 1, 1) s -- 월 첫번째 일요일(월 1주차 시작일)
             , LAST_DAY(TO_DATE(year||month, 'yyyymm')) e        -- 월 마지막 일자
          FROM t
        )
     , (SELECT LEVEL w FROM dual CONNECT BY LEVEL <= 5)
 WHERE w <= (e - s) / 7 + 1
 ORDER BY year, month, w
 )
SELECT  t2.WEEK
      , t3.cost
FROM    t2, t3
WHERE   t2.WEEK = t3.WEEK(+)
ORDER BY
        t2.week
;

이런식으로 날짜를 기준으로 임시 테이블을 만들고 outer join으로 쿼리를 만들었어요.

이렇게 원하는 데이터는 나오는데...

원시 테이블에 데이터가 많아지면 검색 속도가 느려진다는 문제가 있네요. ㅠㅠ

by 마농 [2023.03.30 10:09:11]

WITH t AS
(
SELECT 2022 year, 12 month, 199 cost FROM dual
UNION ALL SELECT 2023,  1, 195 FROM dual
UNION ALL SELECT 2023,  3, 100 FROM dual
)
SELECT a.year
     , a.month
     , a.week
     , a.sdt
     , a.edt
     , b.cost
  FROM (SELECT TO_CHAR(sdt, 'yyyy') year
             , TO_CHAR(sdt, 'mm') month
             , TO_CHAR(sdt, 'ww') week
             , TO_CHAR(sdt, 'yyyymmdd') sdt
             , TO_CHAR(edt, 'yyyymmdd') edt
          FROM (SELECT sdt + LEVEL*7 - 7 sdt
                     , sdt + LEVEL*7 - 1 edt
                  FROM (SELECT TRUNC(TO_DATE('20221211', 'yyyymmdd'), 'd') sdt
                             , TRUNC(TO_DATE('20230326', 'yyyymmdd'), 'd') edt
                          FROM dual
                        )
                 CONNECT BY LEVEL <= (edt - sdt) / 7 + 1
                )
        ) a
  LEFT OUTER JOIN t b
    ON a.year  = b.year
   AND a.month = b.month
 ORDER BY year, month, week
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변