ORACLE 주차 구하기 'IW' & 'WW'

--쿼리 6번문제
--배치정보(A11.A1AT_BATCH_JOB_INF)와 배치결과(A11.A1AT_BATCH_JOB_RSLT)를 이용하여
-- 2023년 5월 1달동안의 배치 정보를 요일별로 출력

--사용테이블
-- - 배치정보 : A11.A1AT_BATCH_JOB_INF
-- - 배치결과 : A11.A1AT_BATCH_JOB_RSLT
--회사관리일자 : A11.A1AT_CALENDAR (COM_ID = '10' and SYSTEM_ID = '01')

--입력변수
-- 배치일련번호

--출력정보
-- - 배치일련번호, 업무파트, 배치한글명, 주수, 월요일결과, 화요일결과, 수요일결과, 목요일결과, 금요일결과, 토요일결과, 일요일결과

-- 지금 작업중인 쿼리 ----
-- 문제가 되는 부분 : 1월에 52주 이상, 12월에 01 주가 생기는 일이 발생.

SQL 문제 6

배치정보(A11.A1AT_BATCH_JOB_INF)와 배치결과(A11.A1AT_BATCH_JOB_RSLT)를 이용하여
2023년 5월 1달동안의 배치 정보를 요일별로 출력

사용테이블

• 배치정보 : A11.A1AT_BATCH_JOB_INF
• 배치결과 : A11.A1AT_BATCH_JOB_RSLT
• 회사관리일자 : A11.A1AT_CALENDAR (COM_ID = '10' and SYSTEM_ID = '01')
  
  입력변수
• 배치일련번호
  
  출력정보
• 배치일련번호, 업무파트, 배치한글명, 주수, 월요일결과, 화요일결과, 수요일결과, 목요일결과, 금요일결과, 토요일결과, 일요일결과

SELECT I.BATCH_NUM AS "배치일련번호"
    , I.PART AS "업무파트"
    , I.BATCH_KORNM AS "배치한글명"
    , C.MNG_WEEK AS "주수"
    , MAX(CASE WHEN C.MNG_DAY = '월' THEN R.RSLT END) AS "월"
    , MAX(CASE WHEN C.MNG_DAY = '화' THEN R.RSLT END) AS "화"
    , MAX(CASE WHEN C.MNG_DAY = '수' THEN R.RSLT END) AS "수"
    , MAX(CASE WHEN C.MNG_DAY = '목' THEN R.RSLT END) AS "목"
    , MAX(CASE WHEN C.MNG_DAY = '금' THEN R.RSLT END) AS "금"
    , MAX(CASE WHEN C.MNG_DAY = '토' THEN R.RSLT END) AS "토"
    , MAX(CASE WHEN C.MNG_DAY = '일' THEN R.RSLT END) AS "일" 
FROM (SELECT MNG_DATE
    , TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'DY') AS MNG_DAY
    , TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'IW') AS MNG_WEEK
    FROM A11.A1AT_CALENDAR
    WHERE MNG_DATE BETWEEN :YYYYMM || '01' and :YYYYMM || '31'
    ORDER BY MNG_DATE) C 
    , A11.A1AT_BATCH_JOB_INF I
    , A11.A1AT_BATCH_JOB_RSLT R
WHERE R.BATCH_NUM = I.BATCH_NUM
    AND I.BATCH_NUM = :BATCH_NUM
    AND C.MNG_DATE = R.RUN_YMD
GROUP BY C.MNG_WEEK, I.BATCH_NUM, I.BATCH_KORNM, I.PART
ORDER BY MNG_WEEK;

2022년 5월을 기준으로 결과를 확인하니 전혀 문제가 없어보였다. 테스트를 위해 여러건의 데이터를 삽입해 봤더니 아래와 같은 결과가 나왔다.

왜 주수가 52주가 나왔을까? CALENDAR 테이블만 실행시켜보니 2022년1월1일~2일이 2019년도의 연장선에서 52주차로 계산되어 있었다. 주차를 계산하기 위해 'IW' 함수를 사용했기 때문에, IW에 대해 찾아보았다.

주차(week)는 날짜에 따라 달라질 수 있으며, 주차 계산은 특정 국가 또는 시스템의 규칙에 따라 달라집니다. 'IW' 포맷을 사용하여 주차를 계산할 때 주의해야 합니다.

문제가 발생하는 이유는 'IW' 포맷은 ISO 주차(ISO week)를 계산하기 때문입니다. ISO 주차는 일정 규칙에 따라 매년 1월 4일을 기준으로 주차를 계산합니다. 이 규칙에 따르면 1월 1일이 금요일 이전인 경우, 그 해의 첫 주는 전년도의 주차로 취급됩니다.

따라서 2022년 1월 1일은 토요일이므로 2022년의 첫 주는 전년도로 취급되어 2021년의 주차로 계산됩니다.만약 월간 데이터를 사용하여 주차를 계산하고자 한다면, 'IW' 포맷 대신 'WW' 포맷을 사용하는 것이 더 적합할 수 있습니다. 'WW' 포맷은 월간 주차를 계산합니다.

오케이 이제 ww를 사용하면 해결되겠지?하고 생각했으나 아니었다.

2022년은 일요일 시작 달력 기준으로 1월1일인 토요일까지만 1주차고 일요일부터 2주차로 계산되어야 하는데, 'WW'는 1일부터 일주일을 1주차로 계산했다. 즉 토요일(1일)부터 금요일(7일)까지가 1주차가 된다.

SQL 대표 문제인 달력 출력 쿼리를 작성한 블로그들 거의 전부 이런 문제점을 간과하고 있다.

이 문제를 어떻게 해결할 수 있을까??

조건문 사용하여 값 변화시키기

SELECT MNG_DATE
       ,TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'DY') AS MNG_DAY ,
       CASE
         WHEN SUBSTR(MNG_DATE, 5, 2) = '01' AND (TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'IW') = '52' OR TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'IW') = '53') THEN '00'
         WHEN SUBSTR(MNG_DATE, 5, 2) = '12' AND TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'IW') = '01' THEN TO_CHAR(TO_NUMBER(TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'IW')) + 52)
         ELSE TO_CHAR(TO_DATE(MNG_DATE, 'YYYYMMDD'), 'IW')
       END AS MNG_WEEK
FROM A11.A1AT_CALENDAR
WHERE MNG_DATE BETWEEN :YYYYMM || '01' AND :YYYYMM || '31'
ORDER BY MNG_DATE;

1. SUBSTR으로 MNG_DATE('YYYYMM')에서 'MM'을 구한다.
2. MM이 01(1월)이고 'IW' 즉 주차가 '52' 또는 '53'이면 '00'으로 값을 바꾼다.
3. MM이 12(12월)이고 주차가 '01'이면 +52를 한다.

이렇게 하면 특정 한 달 데이터를 뽑아낼 때 원하는 결과물을 얻을 수 있다.

BUT

• 값을 임의로 변경한다.
• 12월과 1월을 같이 뽑아낼 때 12월의 마지막 주와 1월의 첫째주가 서로 분리되어 출력된다.

위와 같은 큰 문제점이 존재한다.

주차가 아닌 DATE로 정렬하기

-- 기존 코드와 동일 
ORDER BY MIN(MNG_DATE);

지금까지는 계속 주차에 매달려 주차로 ORDER BY를 했다. 코드는 같은 주차끼리 GROUP BY되어 있기 때문에 각 그룹(한 주)의 시작일로 ORDER BY 정렬해주면 앞서 말한 문제점 없이 원하는 결과를 출력할 수 있다.

각 2019년 12월, 2022년 1월의 결과다.