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

두 날짜 사이 해당되는 날짜 불러오기 (level, connect by)

by 마카히로 [Oracle 기초] oracle 날짜 두사이 불러오기 [2023.01.25 19:13:20]

안녕하세요

시작일과 종료일이 있는 테이블에서 시작일과 종료일 사이 날짜를 행으로 불러오는 쿼리를 찾아서 적용하였는데 잘 안되서 질문드립니다!

	시작일	종료일
1	2023-01-01	2023-01-05
2	2023-01-02	2023-01-04
~	~	~
n번째	2023-01-25	2023-01-25

WITH TEST1 AS (생략)
 
SELECT TO_CHAR(TO_DATE(시작일) +LEVEL -1, 'YYYY-MM-DD') AS DATE
 
FROM TEST1
 
CONNECT BY LEVEL <= (TO_DATE(종료일,'YYYYMMDD') - TO_DATE(시작일,'YYYYMMDD') +1)

이렇게 짤 경우, 시작일과 종료일 사이에 날짜들만 쭉 나와야하는데

생각보다 너무 많은 데이터가 나옵니다 ㅜㅜ

데이터가 왜 많이나오는지 알기 위해 CONNECT BY LEVEL <= 2 를 넣었더니

N* (N+1) 개 데이터가 나오더라구요 ㅜㅜ

고수님들 도움 간곡히 부탁드립니다!

by 마농 [2023.01.26 08:58:22]

CONNECT BY LEVEL <= n 형태의 행복제 방식은
dual 처럼 1행의 자료에만 적용하는 방법입니다.
여러행의 자료에 직접 적용하면 기하급수로 복제가 됩니다.
http://gurubee.net/article/55635

WITH test1 AS
(
SELECT 1 no, '2023-01-01' sdt, '2023-01-05' edt FROM dual
UNION ALL SELECT 2, '2023-01-02', '2023-01-04' FROM dual
UNION ALL SELECT 3, '2023-01-25', '2023-01-25' FROM dual
)
SELECT no
     , TO_CHAR(sdt + lv - 1, 'yyyy-mm-dd') dt
  FROM (SELECT no
             , TO_DATE(sdt, 'yyyy-mm-dd') sdt
             , TO_DATE(edt, 'yyyy-mm-dd') edt
          FROM test1
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
 WHERE lv <= edt - sdt + 1
 ORDER BY no, dt
;

by 마카히로 [2023.01.26 09:52:37]

감사합니다!

정말 감사합니다. CONNECT BY LEVEL 관련 다른 글에 마농님께서 단일 행에만 써야한다는 걸 본 것같은데 저런 이야기 였군요. 하나하나 자세하게 설명해주셔서 정말 감사합니다!.

by 마카히로 [2023.01.26 10:47:24]

제가 어려웠던 부분 공유드리고자합니다^^;;

sdt 2023-01-01 ,edt 2023-01-03 일때

조정값1 select : sdt + lv

조정값2: where : lv <=edt -sdt

1. 시작일,종료일을 포함하고 싶을 때

조정값1:sdt + lv -1

조정값2:lv<= edt-sdt +1

>결과: 1/1,1/2,1/3일 출력

2. 시작일 제외, 종료일을 포함하고 싶을 때

조정값1: sdt + lv

조정값2: edt-sdt

>결과: 1/2,1/3 일 출력

3.시작일 포함, 종료일 제외하고 싶을 때

조정값1: sdt + lv-1

조정값2: edt-sdt

>결과: 1/1,1/2 일 출력

by 동동동 [2023.01.26 09:28:19]

WITH TMP (NO, SDT, EDT) AS (
SELECT 1, '2023-01-01', '2023-01-05' FROM DUAL UNION ALL
SELECT 2, '2023-01-02', '2023-01-04' FROM DUAL UNION ALL
SELECT 3, '2023-01-25', '2023-01-25' FROM DUAL
)
SELECT DISTINCT NO, TO_CHAR(TO_DATE(SDT, 'YYYY-MM-DD') + LEVEL -1, 'YYYY-MM-DD') AS DT 
FROM TMP
CONNECT BY LEVEL <= (TO_DATE(EDT,'YYYY-MM-DD') - TO_DATE(SDT, 'YYYY-MM-DD') +1)
ORDER BY NO, DT;

이렇게 하면 되긴 하는데..마농님 말씀 처럼 기하급수로 복제가 되서 Row가 많을시에는 어떻게 될지 모르겠네요..

그룹별 (NO별) CONNECT BY 하는 방법도 있을것 같은데....@.@

by 마농 [2023.01.26 09:32:11]

행이 하나씩 늘때마다 수행시간은 기하급수로 늘어납니다.
절대로 이렇게 사용해서는 안됩니다.

by 동동동 [2023.01.26 09:45:30]

옙...^^

by 마카히로 [2023.01.26 09:45:49]

답변갑사합니다!!

by jkson [2023.01.26 09:51:00]

select a.*, 1 lv
from t a

union all

select a.*, 2 lv
from t a, t b

union all

select a.*, 3 lv
from t a, t b, t c

union all
..

이런 식으로 셀프 조인하면서 데이터가 늘어납니다.

그래서 테이블 자체에 connect by level 하시면 안 되는 거예요.

테이블이 크면 cpu, 메모리가..

by 마카히로 [2023.01.26 10:30:21]

아 그렇군요 설명 감사드립니다!

댓글등록

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

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

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

데이터베이스 질문과 답변