데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 35481 게시물 읽기

No. 35481

빈구간 구하기
작성자	김흥수(protokhs)	작성일	2009-03-31 21:33ⓒ 2009-03-31 21:36ⓜ	조회수	4,976

지금 제가 짜고 있는 프로그램중에
아파트의 임대를 관리하는 프로그램이 있습니다.
이 프로그램에서는 아파트가 임대되지 않고 있는 기간을 산출하여야 하는 요건이 있습니다.
그래서 다음과 유사한 테이블이 있습니다.

SQL> desc 범위테이블
이름                                      널?      유형
----------------------------------------- -------- ----------------------------
시작일                                    NOT NULL DATE
종료일                                             DATE

여기서 시작일은 임대가 시작된 날짜이고요
종료일은 임대가 종료된 날짜입니다.

만약 위와 같은 테이블에 다음과 같은 자료가 있을 경우

SQL> select to_char(시작일,'yyyy-mm-dd') 시작일 , to_char(종료일,'yyyy-mm-dd') from 범위테이블;

시작일 TO_CHAR(종
---------- ----------
2008-01-01 2008-01-28
2008-01-22 2008-02-24
2008-01-31 2008-03-31
2008-04-05 2008-05-06
2008-04-03 2008-06-26
2008-07-21 9999-12-31

6 개의 행이 선택되었습니다.

임대되지 않고 있었던 기간은 2008-04-01 ~ 2008-04-02 까지 와 2008-06-27 ~ 2008-07-20 이 됩니다.
만약 이 아파트가 2001-01-01 에 처음으로 임대를 시작하였다면
실제 임대되지 않은 기간은

2001-01-01 ~ 2007-12-31 과
2008-04-01 ~ 2008-04-02,
2008-06-27 ~ 2008-07-20 의

세 구간이 됩니다.

임대 계약 기간에 따라서 임대되지 않았던 구간은 이보다 더 많아질수도 있고 아예 없을 수도 있습니다.

이럴 경우 임대되지 않고 집이 비어있었던 구간을 구하는 SQL을 알고싶습니다.

고수님들의 많은 조언 부탁드립니다.

(참고로 실제 임대계약이 중복될 수는 없습니다만 기존 테이블의 데이타에 올바르지 않은 자료들이 있어서 임대 기간이 중복되는 자료들도 있어서
위와 같은 예제를 올렸습니다.)

이 글에 대한 댓글이 총 6건 있습니다.

WITH t AS
(
SELECT TO_DATE('20080101','yyyymmdd') sdt, TO_DATE('20080128','yyyymmdd') edt FROM dual
UNION ALL SELECT TO_DATE('20080122','yyyymmdd'), TO_DATE('20080224','yyyymmdd') FROM dual
UNION ALL SELECT TO_DATE('20080131','yyyymmdd'), TO_DATE('20080331','yyyymmdd') FROM dual
UNION ALL SELECT TO_DATE('20080405','yyyymmdd'), TO_DATE('20080506','yyyymmdd') FROM dual
UNION ALL SELECT TO_DATE('20080403','yyyymmdd'), TO_DATE('20080626','yyyymmdd') FROM dual
UNION ALL SELECT TO_DATE('20080721','yyyymmdd'), TO_DATE('99991231','yyyymmdd') FROM dual
)
, copy_t AS
(
SELECT LEVEL lv
, TO_DATE('20010101','yyyymmdd') + LEVEL - 1 dt
FROM dual
CONNECT BY LEVEL <= SYSDATE - TO_DATE('20010101','yyyymmdd') + 1
)
SELECT MIN(c.dt)
, MAX(c.dt)
FROM t a, copy_t c
WHERE c.dt BETWEEN a.sdt(+) AND a.edt(+)
AND a.sdt IS NULL
GROUP BY c.lv - ROWNUM
ORDER BY 1
;

마농(manon94)님이 2009-03-31 23:26에 작성한 댓글입니다.

위에서 설명드린대로 임대아파트가 계약이되지 않고 빈 집으로 있었던 기간이

얼마가 되는지를 산출하려는 것입니다.

김흥수(protokhs)님이 2009-04-01 08:28에 작성한 댓글입니다.

역시 마농님이십니다.

어떻게 이런 생각을 하시는지 정말 놀랍습니다.

특히 GROUP BY c.lv - ROWNUM 라고 하신 부분이 감탄스럽습니다.

처음에 저도 님처럼 시도하다가 그 부분에서 막혔었는데요....

저도 처음에는 제가 원하는 바가 구간 합집합의 여집합이라는 사실에서 님처러 시도했었거든요...

그런데 그룹핑할 방법이 없어서 그 방법을 포기했었습니다.

그러면요 마농님께 한가지 더 질문드리고 싶은게 있는데요..

이건 실전 문제는 아니고요

님처럼 할 경우에 만약 구간이 실수 구간이라면(즉 날짜만이 아니라 초까지 구해야 한다거나 한다면)

님의 방법으로는 현실적으로 불가능할 것 같거든요...

이것을 해결할 묘수가 없을까요?

김흥수(protokhs)님이 2009-04-01 08:44에 작성한 댓글입니다.

해결 방법은 의외로 간단합니다.

예전에 만들어 놓은 방법인데 아래 페이지를 참고하세요.

http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=74#409

일단위인 경우에는 마농님처럼 해도 되겠고, 아래처럼 해도 됩니다.

SELECT PRE_END + 1 C_SDT, SDT - 1 C_EDT

FROM (SELECT SDT, EDT,

NVL

(MAX (EDT) OVER (ORDER BY SDT, EDT ROWS

BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),

TO_DATE ('20010101', 'YYYYMMDD') - 1

) PRE_END

FROM T)

WHERE SDT >= PRE_END + 2

C_SDT C_EDT

---------- ----------

2001-01-01 2007-12-31

2008-04-01 2008-04-02

2008-06-27 2008-07-20

초단위일 경우에는 위의 쿼리를 약간 수정해서,

+나 -를 해주는 모든 수를 24*60*60 으로 나눠주면 되겠죠.

SELECT PRE_END + 1/24/60/60 C_SDT, SDT - 1/24/60/60 C_EDT

FROM (SELECT SDT, EDT,

NVL

(MAX (EDT) OVER (ORDER BY SDT, EDT ROWS

BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),

TO_DATE ('20010101', 'YYYYMMDD') - 1/24/60/60

) PRE_END

FROM T)

WHERE SDT >= PRE_END + 2/24/60/60

결과는 확인해 보시기 바랍니다.

www.soqool.com

SoQooL(김홍선)님이 2009-04-01 16:57에 작성한 댓글입니다.

와 !!

좋은 방법이 있네요

역시 김홍선 님입니다.

좋은 의견 감사합니다.

김흥수(protokhs)님이 2009-04-01 17:28에 작성한 댓글입니다.

김흥수님 네이트 메신저 추가하였는데 아직 로그인을 안하신거 같네요 ^^

사용하시는 메일 주소좀 가르켜주시면 감사하겠습니다.

http://angel.sarang.net

보규니(정보균)님이 2009-04-07 14:29에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
35484	배치 시간에 따른 시간 절삭에 대한 질문입니다. [1]	조은제	2009-04-01	2634
35483	아카이브 full이 났습니다 [2]	윤종열	2009-04-01	5903
35482	롤업 함수 질문 입니다. [1]	홍기	2009-04-01	3394
35481	빈구간 구하기 [6]	김흥수	2009-03-31	4976
35480	오라클 백업/복구 관련한 질문입니다 [1]	서연석	2009-03-31	3612
35479	Oracle to Mssql DBlink mssql에 한글등록	길범수	2009-03-31	5284
35478	특정 수량을 제한 나머지 Record를 구하고 싶은데 .... [2]	김도형	2009-03-31	3082