[SQLD/P] 기준일자(당월) 입력후 표기일자를 도출하는 과정

1. 수정전

기준일자를 입력하였을때, 해당 기준일자(당월)를 기준으로 한달전 날짜들을 도출하여 일자를 표기한다.

SELECT ADD_MONTHS(TO_DATE(:base_yrmn || LPAD(LEVEL, 2, '0'), 'YYYYMMDD'), -1) AS BASE_DATE
FROM DUAL
CONNECT BY ADD_MONTHs(TO_DATE(:base_yrmn||'01', 'YYYYMMDD'), -1) + LEVEL -1 <= LAST_DAY(ADD_MONTHS(TO_DATE(:base_yrmn||'01', 'YYYYMMDD'), -1))

해당 쿼리의 문제점은

• 예를 들어 9월을 입력하였을 경우, 한달 전인 8월은 31일까지 존재하므로 조건절 상 31번 반복을 시행한다.
• 일자를 도출하는 조건상 기준일자에서 한달을 빼는(ADD_MONTHS(sysdate, -1)) 방식으로 표기를 하는데 이 과정에서 날짜 부적합 오류가 발생한다.
• 9월 입력시 이전 날짜(8월)는 31일까지 존재하므로 31번 반복을 시행하지만, 날짜 도출 과정이 당월(입력기준일자)을 기준으로 한달을 빼면서 입력받으므로 마지막 반복인 31일째에서 9월 31은 존재하지 않으므로 날짜 부적합 오류 발생.

2. 수정후

본 쿼리는 기준일자가 31일까지 존재하는 달에 대해서만 적용이 가능하는 등의 몇가지 개선사항이 존재한다.

쿼리를 수정해가면서 날짜를 도출하는 방법을 다르게 하여 진행한다.

2-1. 전월/당월/익월을 모두 도출한후 일부에 대해서만 출력할 경우

본 쿼리는 기준일자를 입력한 후 전월/당월/익월을 모두 도출하므로, 속도면에서 불리할 수 있다.

관련한 모든 일자를 불러온후, 주차수(WKLY_SRNO)를 기준으로 출력할 일자를 도출한다.

SELECT TO_DATE(:base_yrmn -1 || LPAD(LEVEL, 2, '0'), 'YYYYMMDD') AS BASE_DATE
FROM DUAL
CONNECT BY ADD_MONTHS(TO_DATE(:base_yrmn || '01', 'YYYYMMDD'), -1) + LEVEL - 1 <= LAST_DAY(ADD_MONTHS(TO_DATE(:base_yrmn||'01', 'YYYYMMDD'), -1))
UNION ALL
SELECT TO_DATE(:base_yrmn | LPAD(LEVEL, 2, '0'), 'YYYYMMDD') AS BASE_DATE
FROM DUAL
CONNECT BY TO_DATE(:base_yrmn || '01', 'YYYYMMDD') + LEVEL - 1 <= LAST_DAY(TO_DATE(:base_yrmn || '01', 'YYYYMMDD'))
UNION ALL
SELECT TO_DATE(:base_yrmn +1 || LPAD(LEVEL, 2, '0'), 'YYYYMMDD') AS BASE_DATE
FROM DUAL
CONNECT BY ADD_MONTHS(TO_DATE(:base_yrmn || '01', 'YYYYMMDD'), -1) + LEVEL - 1 <= LAST_DAY(ADD_MONTHS(TO_DATE(:base_yrmn||'01', 'YYYYMMDD'), +1))

• 기존 기준일자에서 한달을 뺀 일자들을 출력하는 것이 아닌, 날짜를 도출하는 일자를 전월 자체로 구성하여 날짜입력오류가 발생하지 않도록 구성한다.
• 날짜오류를 발생하지 않는 것에만 초점을 둔 쿼리이다.
• 기준일자(base_yrmn)가 문자열이지만, +1/-1을 적용할 수 있다.
• 이것 또한 한계가 있다(12월에서 다음년도로 넘어갈 경우 날짜오류 또다시 발생 → base_yrmn+1에서 202213은 존재하지 않는 날짜이다.)

2-2. 전년도, 다음년도로 넘어가는 경우 모든 일자를 도출하는 쿼리

본 쿼리는 도출할 표기일자에 대한 범위를 먼저 나타내주는 쿼리이다.

년도가 넘어갈때의 예외처리를 위한 임시방편이고, 쿼리가 길어질뿐더러 주차수를 계산할때 더 복잡해지는 쿼리이므로 다른 효율적인 방안을 고민해보도록 한다.

올해 -> 전년도일 경우

SELECT TO_DATE(DECODE(SUBSTR(:base_yrmn -1, 5, 2), 00, :base_yrmn - 89, :base_yrmn -1) || LPAD(LEVEL, 2, '0'), 'YYYYMMDD') AS BASE_DATE
FROM DUAL
CONNECT BY ADD_MONTHS(TO_DATE(:base_yrmn || '01', 'YYYYMMDD'), -1) + LEVEL -1 <= LAST_DAY(ADD_MONTHS(TO_DATE(:base_yrmn || '01', 'YYYYMMDD'), -1))

올해 -> 내년일 경우

SELECT TO_DATE(DECODE(SUBSTR(:base_yrmn + 1, 5, 2), 13, :base_yrmn + 89, :base_yrmn + 89, :base_yrmn +1) || LPAD(LEVEL, 2, '0'), 'YYYYMMDD'))
FROM DUAL
CONNECT BY ADD_MONTHS(TO_DATE(:base_yrmn || '01', 'YYYYMMDD'), 1) + LEVEL -1 <= LAST_DAY(ADD_MONTHS(TO_DATE(:base_yrmn || '01', 'YYYYMMDD'), 1))

이 부분까지 도출해내면 전월/당월/익월 모든 범위를 표기할 수 있지만, 주차수대로 선별하는 과정에서 예외처리를 상당히 복잡하게 진행해야 한다.

즉 이전년도, 다음년도에 해당하는 일자들이 같이 표기될 경우 일자도출이 불가능하며 다른 도출 방안을 찾아내야 한다.

2-3. 출력대상이 되는 달(범위선별 이후)을 주차수가 아닌 다른 방법으로 도출

본 쿼리는 출력대상(표기일자)을 주차수가 아닌 다른 방안을 통해 도출해내는 방법이다(위 방식은 기준일자/전월/익월 모든 일정을 가져온 후에 주차수를 계산하여 표기할 일자들을 선별하였으나 예외처리(년도 바뀔때)가 너무 많고 복잡하다).

기존 방식의 경우 이전/다음년도가 나타나는 상황에서는 사용할 수 없다(주차수 계산이 복잡해짐). 개선한 쿼리에서는 주차수가 아닌 다른 방법으로 표기일자를 도출하며, 속도나 성능 측면에서 보았을때 더 유리할 수 있다.

※ 연초(1월)에 대한 주차수만을 고려하여 제한조건으로 설정할 경우, 연말에 해당 제한조건이 겹칠 수 있기 때문에 정상출력을 진행할 수 없다.

기준일자의 1일(첫번째 요일)을 기준으로, 전월 및 익월까지 넉넉한 범위의 요일을 가져온다(7주씩, 7일로 하여 총 49일을 가져온다)

SELECT 
BASE_DATE AS BASE_DATE AS BASE_DATE
,DENSE_RANK() OVER(ORDER BY BASE_DATE) AS SEQ
,TO_CHAR(BASE_DATE 'D') AS DYWK_DTCD
FROM
	(
	SELECT TO_DATE(:base_yrmn || '01') - TO_NUMBER(TO_CHAR(TO_DATE(:base_yrmn||'01'), 'D')) + 6) + LEVEL AS BASE_DATE
    FROM DUAL
    CONNECT BY LEVEL <= 7 * 7
	)
)

• 표기일자를 선별하기위해 1차적으로 넉넉히 전월/익월을 포함한 전체적인 요일범위를 도출해낸다(아래 쿼리를 고려하여 너무 많은 요일을 가져오면 표기일자 도출이 비정상 진행될 수 있음).

요일의 범위를 가져왔다면, 이를 바탕으로 주차수 등을 계산하여 최종적인 표기일자를 가져온다.

*단, 년도가 넘어갈때의 예외상황을 고려하여 요일 그자체(주차수를 고려하는 것이 아닌, 년도를 포함한 일자로 고려(ORDER BY ASC하면 정렬된 요일들은 반드시 작은 순서부터 고정 출력)하는 것을 의미)로 표기일자를 도출해내도록 한다.

SELECT A.BASE_DATE AS BASE_DATE
,A.DYWK_DTCD AS DYWK_DTCD
,STRN_DATE AS STRN_DATE
,END_DATE AS END_DATE
,CEIL(DENSE_RANK() OVER(ORDER BY BASE_DATE) / 7) AS WKLY_SRNO
FROM WITH_TABLE A
,
(
	SELECT MIN(DECODE(DYWK_DTCD, '1', BASE_DATE, '')) AS STRN_DATE
    ,MAX(DECODE(DYWK_DTCD, '7', BASE_DATE, '')) AS END_DATE
    ,FROM WITH_TABLE
    WHERE DYWK_DTCD IN ('1', '7')
) B
WHERE A.BASE_DATE BETWEEN B.STRN_DATE AND B.END_DATE

3. 참고자료

sysdate를 TO_CHAR하는 다양한 문법들 - https://goddaehee.tistory.com/242
TO_DATE 문법들 - https://hello-nanam.tistory.com/280