database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 40667 게시물 읽기
No. 40667
비어있는 시간과 실제시간 합치기 문의 드립니다.
작성자
김태경(cranes)
작성일
2015-01-06 01:12
조회수
7,559

안녕하세요

 

고객이 별걸 다 요구하는군요 힘드네요

 

두개의 테이블이 있는데

 

하나는 시작시간과 종료시간만 마스타성 테이블 이구요 4시 부터 4시 반이라는 데이터 입니다.

 

하나는 시작시간 종료시간 의 초단위 상세 데이타가 있는데 4시부터 4시반사이의 데이터입니다.

 

두 테이블을 조인해서 비어 있는 시간 초단위 기준 시작 ~ 종료로 시간을 구하고 flag 를 넣어서

 

가상데이타는 0 실제 데이타는 1를 필드로 보여야 하는데 가능한지 문의드립니다.

 

감사합니다.

 

 

SELECT to_Date ('2014-11-20 04:00:00','YYYY-MM-DD HH24:MI:SS') start_time , to_date('2014-11-20 04:30:00','YYYY-MM-DD HH24:MI:SS') end_time

FROM dual

 

 

 

SELECT to_Date ('2014-11-20 04:24:53','YYYY-MM-DD HH24:MI:SS') sdt ,

to_date('2014-11-20 04:25:04','YYYY-MM-DD HH24:MI:SS') edt

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:25:05','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:25:14','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

 

SELECT to_Date ('2014-11-20 04:26:28','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:27:00','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:27:01','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:27:12','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:27:13','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:27:26','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:27:27','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:27:37','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:27:38','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:27:48','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:27:49','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:28:18','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:28:19','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:28:30','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:28:31','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:29:03','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:29:04','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:29:15','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:29:16','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:29:28','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:29:29','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:29:42','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:29:43','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:29:55','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:29:56','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:29:59','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:30:00','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:30:10','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:30:11','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:30:23','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:30:24','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:30:34','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:30:35','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:30:48','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:30:49','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:31:00','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:31:01','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:31:12','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

 

SELECT to_Date ('2014-11-20 03:31:38','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:37:53','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:37:54','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:38:08','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:38:09','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:38:23','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:38:24','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:38:40','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:38:41','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 03:43:50','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 03:43:51','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:06:37','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:06:38','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:06:51','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:06:52','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:07:06','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:07:07','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:07:27','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:07:28','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:07:40','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:07:41','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:08:08','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:08:09','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:08:19','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:08:20','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:08:33','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:08:34','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:08:44','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:08:45','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:08:55','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:08:56','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:09:08','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:09:09','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:09:23','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

 

SELECT to_Date ('2014-11-20 04:10:01','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:10:11','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:10:12','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:10:23','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:10:24','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:10:36','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:10:37','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:10:50','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:10:51','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:11:02','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:11:03','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:11:16','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:11:17','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:24:41','YYYY-MM-DD HH24:MI:SS')

FROM dual

UNION ALL

SELECT to_Date ('2014-11-20 04:24:42','YYYY-MM-DD HH24:MI:SS'),

to_date('2014-11-20 04:24:52','YYYY-MM-DD HH24:MI:SS')

FROM dual
이 글에 대한 댓글이 총 2건 있습니다.

WITH m AS
(
SELECT TO_DATE('2014-11-20 04:00:00', 'yyyy-mm-dd hh24:mi:ss') start_time
     , TO_DATE('2014-11-20 04:30:00', 'yyyy-mm-dd hh24:mi:ss') end_time
  FROM dual
)
, s AS
(
SELECT TO_DATE(sdt, 'yyyy-mm-dd hh24:mi:ss') sdt
     , TO_DATE(edt, 'yyyy-mm-dd hh24:mi:ss') edt
  FROM
     (SELECT '2014-11-20 04:24:53' sdt, '2014-11-20 04:25:04' edt  FROM dual
     UNION ALL SELECT '2014-11-20 04:25:05', '2014-11-20 04:25:14' FROM dual
     UNION ALL SELECT '2014-11-20 04:26:28', '2014-11-20 04:27:00' FROM dual
     UNION ALL SELECT '2014-11-20 04:27:01', '2014-11-20 04:27:12' FROM dual
     UNION ALL SELECT '2014-11-20 04:27:13', '2014-11-20 04:27:26' FROM dual
     UNION ALL SELECT '2014-11-20 04:27:27', '2014-11-20 04:27:37' FROM dual
     UNION ALL SELECT '2014-11-20 04:27:38', '2014-11-20 04:27:48' FROM dual
     UNION ALL SELECT '2014-11-20 04:27:49', '2014-11-20 04:28:18' FROM dual
     UNION ALL SELECT '2014-11-20 04:28:19', '2014-11-20 04:28:30' FROM dual
     UNION ALL SELECT '2014-11-20 04:28:31', '2014-11-20 04:29:03' FROM dual
     UNION ALL SELECT '2014-11-20 04:29:04', '2014-11-20 04:29:15' FROM dual
     UNION ALL SELECT '2014-11-20 04:29:16', '2014-11-20 04:29:28' FROM dual
     UNION ALL SELECT '2014-11-20 04:29:29', '2014-11-20 04:29:42' FROM dual
     UNION ALL SELECT '2014-11-20 04:29:43', '2014-11-20 04:29:55' FROM dual
     UNION ALL SELECT '2014-11-20 04:29:56', '2014-11-20 04:29:59' FROM dual
     UNION ALL SELECT '2014-11-20 03:30:00', '2014-11-20 03:30:10' FROM dual
     UNION ALL SELECT '2014-11-20 03:30:11', '2014-11-20 03:30:23' FROM dual
     UNION ALL SELECT '2014-11-20 03:30:24', '2014-11-20 03:30:34' FROM dual
     UNION ALL SELECT '2014-11-20 03:30:35', '2014-11-20 03:30:48' FROM dual
     UNION ALL SELECT '2014-11-20 03:30:49', '2014-11-20 03:31:00' FROM dual
     UNION ALL SELECT '2014-11-20 03:31:01', '2014-11-20 03:31:12' FROM dual
     UNION ALL SELECT '2014-11-20 03:31:38', '2014-11-20 03:37:53' FROM dual
     UNION ALL SELECT '2014-11-20 03:37:54', '2014-11-20 03:38:08' FROM dual
     UNION ALL SELECT '2014-11-20 03:38:09', '2014-11-20 03:38:23' FROM dual
     UNION ALL SELECT '2014-11-20 03:38:24', '2014-11-20 03:38:40' FROM dual
     UNION ALL SELECT '2014-11-20 03:38:41', '2014-11-20 03:43:50' FROM dual
     UNION ALL SELECT '2014-11-20 03:43:51', '2014-11-20 04:06:37' FROM dual
     UNION ALL SELECT '2014-11-20 04:06:38', '2014-11-20 04:06:51' FROM dual
     UNION ALL SELECT '2014-11-20 04:06:52', '2014-11-20 04:07:06' FROM dual
     UNION ALL SELECT '2014-11-20 04:07:07', '2014-11-20 04:07:27' FROM dual
     UNION ALL SELECT '2014-11-20 04:07:28', '2014-11-20 04:07:40' FROM dual
     UNION ALL SELECT '2014-11-20 04:07:41', '2014-11-20 04:08:08' FROM dual
     UNION ALL SELECT '2014-11-20 04:08:09', '2014-11-20 04:08:19' FROM dual
     UNION ALL SELECT '2014-11-20 04:08:20', '2014-11-20 04:08:33' FROM dual
     UNION ALL SELECT '2014-11-20 04:08:34', '2014-11-20 04:08:44' FROM dual
     UNION ALL SELECT '2014-11-20 04:08:45', '2014-11-20 04:08:55' FROM dual
     UNION ALL SELECT '2014-11-20 04:08:56', '2014-11-20 04:09:08' FROM dual
     UNION ALL SELECT '2014-11-20 04:09:09', '2014-11-20 04:09:23' FROM dual
     UNION ALL SELECT '2014-11-20 04:10:01', '2014-11-20 04:10:11' FROM dual
     UNION ALL SELECT '2014-11-20 04:10:12', '2014-11-20 04:10:23' FROM dual
     UNION ALL SELECT '2014-11-20 04:10:24', '2014-11-20 04:10:36' FROM dual
     UNION ALL SELECT '2014-11-20 04:10:37', '2014-11-20 04:10:50' FROM dual
     UNION ALL SELECT '2014-11-20 04:10:51', '2014-11-20 04:11:02' FROM dual
     UNION ALL SELECT '2014-11-20 04:11:03', '2014-11-20 04:11:16' FROM dual
     UNION ALL SELECT '2014-11-20 04:11:17', '2014-11-20 04:24:41' FROM dual
     UNION ALL SELECT '2014-11-20 04:24:42', '2014-11-20 04:24:52' FROM dual
     )
)
SELECT sdt, edt, flag
  FROM (SELECT GREATEST(start_time, DECODE(lv, 1, s1, 2, s2, 3, s3)) sdt
             , LEAST   (  end_time, DECODE(lv, 1, e1, 2, e2, 3, e3)) edt
             , DECODE(lv, 1, 1, 0) flag
          FROM (SELECT m.start_time, m.end_time
                     , s.sdt s1
                     , s.edt e1
                     , LAG (edt+1/24/60/60, 1, start_time)
                       OVER(ORDER BY sdt, edt) s2
                     , s.sdt - 1/24/60/60 e2
                     , s.edt + 1/24/60/60 s3
                     , LEAD(sdt-1/24/60/60, 1, end_time  )
                       OVER(ORDER BY sdt, edt) e3
                     , ROW_NUMBER() OVER(ORDER BY sdt DESC, edt DESC) rn
                  FROM m, s
                 WHERE s.sdt <= m.end_time
                   AND s.edt >= m.start_time
                )
             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3)
         WHERE lv <= DECODE(rn, 1, 3, 2)
        )
 WHERE sdt <= edt
 ORDER BY sdt, edt
;

마농(manon94)님이 2015-01-06 11:36에 작성한 댓글입니다.
이 댓글은 2015-01-06 11:38에 마지막으로 수정되었습니다.

마농님 우와 정말 대단하십니다. 

이런것이 가능하군요

존경합니다.

오늘 하루 정말 행복하네요

 

너무나 감사합니다.  

 

김태경(cranes)님이 2015-01-06 13:08에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
40671procedure 오류입니다 도움이나 조언 부탁드립니다. [2]
이주호
2015-01-09
7005
40670데이터 병합 방법좀 알려주세요 [6]
태영
2015-01-09
7067
40669db 일요일부터 월요일까지 시간 어떻게 추리나요? [2]
조성찬
2015-01-08
7073
40667비어있는 시간과 실제시간 합치기 문의 드립니다. [2]
김태경
2015-01-06
7559
40666DB LINK 재연결 방법에 대한 질문입니다. [1]
할마시
2015-01-04
7716
40664UTL_HTTP 권한 관련
뉴비
2014-12-31
6728
40662인덱스 리빌드가 처리된 시간을 확인할 수 있나요? [2]
전현욱
2014-12-28
6118
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는 PostgreSQL v16.2로 자료를 관리합니다