데이터베이스 질문과 답변
현재 데이터베이스를 공부하고 있는 학생인데 궁금한 것이 있습니다.!!
날짜 테이블이 있고 공휴일 테이블이 따로 있습니다.
날짜 테이블에서 해당 날짜에서 하루를 더하여 토요일일 경우 월요일로 만들고 일요일일 경우 월요일로 만드는 것은
DATE_ADD, DAYOFWEEK를 사용하여 만들었습니다.
다만 해당 날짜가 공휴일일 경우 공휴일 테이블에서 확인하여 +1일을 하여 평일로 만들어서 보여줘야하는데 SELECT로 어찌 날짜를 넘겨야하는지 모르겠습니다!ㅠㅠ
아니면 프로시저에서 CURSOR LOOP를 사용해야하나요?
- 날짜테이블
| 연도(CHAR4) | 월(CHAR2) | 일(CHAR2) |
| 2022 | 04 | 04 |
| 2022 | 04 | 15 |
| 2022 | 05 | 05 |
| 2022 | 09 | 09 |
- 공휴일 테이블
| 연도(CHAR4) | 월(CHAR2) | 일(CHAR2) | 이름(VARCHAR20) |
| 2022 | 05 | 05 | 어린이날 |
| 2022 | 09 | 09 | 추석 |
| 2022 | 09 | 10 | 추석 |
| 2022 | 09 | 11 | 추석 |
| 2022 | 09 | 12 | 대체공휴일 |
2022-04-04(월) -> 2022-04-05(화) :1일을 더한 날짜가 평일
2022-04-15(금) -> 2022-04-18(월): 1일을 더한 날짜가 주말이기 때문에 +2일 하여 월요일 평일로 만듬
2022-05-05(목) -> 2022-05-06(금) : 1일을 더한 날짜가 평일
2022-09-09(금) -> 2022-09-13(화) -> 이렇게 하는 방법을 모르겠습니다!ㅠㅠ
1. 날짜 테이블에 모든 날짜가 다 들어 있나요?
- 만약 그렇다면? 굳이 휴일 테이블을 따로 둘 필요가 있는지?
- 날짜 테이블에 휴일여부 구분을 두면 되는데요.
2. 년,월,일 을 따로 관리하네요.
- 하나로 묶어서 관리하는게 여러모로 편리합니다.
답변 감사드립니다!
날짜 테이블에는 예를 들어 정산예정 연,월,일이 계속 INSERT가 된다고 보시면됩니다!
그렇기 때문에 지급일은 일시 + 1을 하여 실제지급일로 표시하려고합니다.
휴일 테이블에는 공휴일의 연,월,일만 있는 상태라고 보시면되고 해당 실제지급일이 공휴일에 해당하면 평일로 만들어줘야하는 상태라고 보시면됩니다.!
-- MySQL --
WITH RECURSIVE t3 AS
(
WITH t1 AS
(
SELECT '2022' y, '04' m, '04' d
UNION ALL SELECT '2022', '04', '15'
UNION ALL SELECT '2022', '05', '05'
UNION ALL SELECT '2022', '09', '09'
)
, t2 AS
(
SELECT '2022' y, '05' m, '05' d, '어린이날' nm
UNION ALL SELECT '2022', '09', '09', '추석'
UNION ALL SELECT '2022', '09', '10', '추석'
UNION ALL SELECT '2022', '09', '11', '추석'
UNION ALL SELECT '2022', '09', '12', '대체공휴일'
)
SELECT CONCAT_WS('-', y, m, d) + INTERVAL 0 DAY dt
, CONCAT_WS('-', y, m, d) + INTERVAL 1 DAY dt_next
FROM t1
UNION ALL
SELECT a.dt
, a.dt_next + INTERVAL 1 DAY dt_next
FROM t3 a
LEFT OUTER JOIN t2 b
ON DATE_FORMAT(a.dt_next, '%Y') = b.y
AND DATE_FORMAT(a.dt_next, '%m') = b.m
AND DATE_FORMAT(a.dt_next, '%d') = b.d
WHERE b.y IS NOT NULL
OR DAYOFWEEK(a.dt_next) IN (1, 7)
)
SELECT dt
, MAX(dt_next) dt_next
FROM t3
GROUP BY dt
;
마농님 염치 없지만 혹시 오라클용으로도 가능 할까요?
너무나 유용한 쿼리일것 같네요..
-- Oracle --
WITH t1 AS
(
SELECT '2022' y, '04' m, '04' d FROM dual
UNION ALL SELECT '2022', '04', '15' FROM dual
UNION ALL SELECT '2022', '05', '05' FROM dual
UNION ALL SELECT '2022', '09', '09' FROM dual
)
, t2 AS
(
SELECT '2022' y, '05' m, '05' d, '어린이날' nm FROM dual
UNION ALL SELECT '2022', '09', '09', '추석' FROM dual
UNION ALL SELECT '2022', '09', '10', '추석' FROM dual
UNION ALL SELECT '2022', '09', '11', '추석' FROM dual
UNION ALL SELECT '2022', '09', '12', '대체공휴일' FROM dual
)
, t3(dt, lv) AS
(
SELECT y||m||d dt
, 1 lv
FROM t1
UNION ALL
SELECT a.dt
, lv + 1
FROM t3 a
LEFT OUTER JOIN t2 b
ON TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + lv, 'yyyy') = b.y
AND TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + lv, 'mm') = b.m
AND TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + lv, 'dd') = b.d
WHERE b.y IS NOT NULL
OR TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + lv - 1, 'd') IN (1, 7)
)
SELECT dt
, TO_DATE(dt, 'yyyymmdd') + MAX(lv) dt_next
FROM t3
GROUP BY dt
ORDER BY dt
;
-- Oracle 에서 함수만 바꿔 실행해 봤는데.
-- Oracle 의 Recursive 쿼리의 경우 date type 사용시 버그가 있네요.
-- 버그 발생 피하려고 코드를 바꿨습니다.
정말 감사드립니다..ㅠㅠ..꾸벅..
마농님 4월15일이 4월 18일로 나와야 하는데 16일로 나오고 있어 수정해 봤습니다.
OR TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + lv - 1, 'd') IN (1, 7) => OR TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + lv, 'd') IN (1, 7)
) 버튼을 클릭하여 작성 하시면 됩니다.