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 41010 게시물 읽기
No. 41010
구간 시작 끝 묶음 및 일련번호 부여 문의 드립니다.
작성자
김미림
작성일
2015-11-23 15:01
조회수
8,682

 안녕하세요 

초보 문의 드립니다.
테이블 구조는 다음과 같습니다.
 
 
 
 select 'C070' channel_id  , '' be_Type  ,  '03:50:00' start_Date  ,  '03:50:00' end_Date   from dual union all  
 select 'C070'   , ''   ,  '04:12:15'   ,  '04:12:15'    from dual union all  
 select 'C070'   , ''   ,  '04:20:15'   ,  '04:20:15'    from dual union all  
 select 'C070'   , ''   ,  '04:20:29'   ,  '04:20:29'    from dual union all  
 select 'C070'   , ''   ,  '04:25:03'   ,  '04:25:03'    from dual union all  
 select 'C070'   , ''   ,  '04:25:13'   ,  '04:25:13'    from dual union all  
 select 'C070'   , ''   ,  '04:30:00'   ,  '04:30:00'    from dual union all  
 select 'C070'   , 'B'   ,  '04:30:31'   ,  '04:30:31'    from dual union all  
 select 'C070'   , 'B'   ,  '04:30:47'   ,  '04:30:47'    from dual union all  
 select 'C070'   , 'B'   ,  '04:30:59'   ,  '04:30:59'    from dual union all  
 select 'C070'   , ''   ,  '04:31:11'   ,  '04:31:11'    from dual union all  
 select 'C070'   , ''   ,  '04:31:27'   ,  '04:31:27'    from dual union all  
 select 'C070'   , ''   ,  '05:16:33'   ,  '05:16:33'    from dual union all  
 select 'C070'   , ''   ,  '05:24:33'   ,  '05:24:33'    from dual union all  
 select 'C070'   , 'E'   ,  '05:24:46'   ,  '05:24:46'    from dual union all  
 select 'C070'   , 'B'   ,  '05:29:40'   ,  '05:29:40'    from dual union all  
 select 'C070'   , ''   ,  '05:29:56'   ,  '05:29:56'    from dual union all  
 select 'C070'   , ''   ,  '05:30:00'   ,  '05:30:00'    from dual union all  
 select 'C070'   , ''   ,  '05:30:08'   ,  '05:30:08'    from dual union all  
 select 'C070'   , ''   ,  '05:30:22'   ,  '05:30:22'    from dual union all  
 select 'C070'   , ''   ,  '05:31:37'   ,  '05:31:37'    from dual union all  
 select 'C070'   , ''   ,  '06:18:02'   ,  '06:18:02'    from dual union all  
 select 'C070'   , ''   ,  '06:26:02'   ,  '06:26:02'    from dual union all  
 select 'C070'   , ''   ,  '06:26:10'   ,  '06:26:10'    from dual union all  
 select 'C070'   , ''   ,  '06:28:10'   ,  '06:28:10'    from dual union all  
 select 'C070'   , ''   ,  '06:28:24'   ,  '06:28:24'    from dual union all  
 select 'C070'   , 'E'   ,  '06:30:00'   ,  '06:30:00'    from dual union all  
 select 'C070'   , 'B'   ,  '06:30:21'   ,  '06:30:21'    from dual union all  
 select 'C070'   , ''   ,  '06:30:37'   ,  '06:30:37'    from dual union all  
 select 'C070'   , ''   ,  '06:31:05'   ,  '06:31:05'    from dual union all  
 select 'C070'   , ''   ,  '06:31:30'   ,  '06:31:30'    from dual union all  
 select 'C070'   , ''   ,  '06:31:45'   ,  '06:31:45'    from dual union all  
 select 'C070'   , ''   ,  '06:59:52'   ,  '06:59:52'    from dual union all  
 select 'C070'   , ''   ,  '07:07:52'   ,  '07:07:52'    from dual union all  
 select 'C070'   , ''   ,  '07:08:17'   ,  '07:08:17'    from dual union all  
 select 'C070'   , 'E'   ,  '07:10:00'   ,  '07:10:00'    from dual union all  
 select 'C070'   , 'B'   ,  '07:10:14'   ,  '07:10:14'    from dual union all  
 select 'C070'   , ''   ,  '07:10:30'   ,  '07:10:30'    from dual union all  
 select 'C070'   , ''   ,  '07:10:42'   ,  '07:10:42'    from dual union all  
 select 'C070'   , ''   ,  '07:11:01'   ,  '07:11:01'    from dual union all  
 select 'C070'   , ''   ,  '07:12:15'   ,  '07:12:15'    from dual union all  
 select 'C070'   , ''   ,  '08:02:37'   ,  '08:02:37'    from dual union all  
 select 'C070'   , ''   ,  '08:10:37'   ,  '08:10:37'    from dual union all  
 select 'C070'   , ''   ,  '08:10:45'   ,  '08:10:45'    from dual union all  
  
 
시작 'B' 끝은 'E' 입니다 이 기준으로 
이전 값은 버리고 그룹핑한다음에  일련번호를 부여 하고 싶습니다.
B로 시작해서  E로 끝난 데이타만 추출하고자 하는데 잘 않되네요 
 
제가 원하는 데이타는 다음과 같습니다. 
 
1 2015-10-28 오전 4:30:31 2015-10-28 오전 5:24:46
2 2015-10-28 오전 5:29:40 2015-10-28 오전 6:30:00
3 2015-10-28 오전 6:30:21 2015-10-28 오전 7:10:00
 
 
 
 
고수분들의 많은 도움 부탁드립니다. 감사합니다. 
 
 
이 글에 대한 댓글이 총 2건 있습니다.

WITH t AS
(
SELECT 'C070' channel_id, '' be_Type, '03:50:00' start_Date, '03:50:00' end_Date FROM dual
UNION ALL SELECT 'C070', '' , '04:12:15', '04:12:15' FROM dual
UNION ALL SELECT 'C070', '' , '04:20:15', '04:20:15' FROM dual
UNION ALL SELECT 'C070', '' , '04:20:29', '04:20:29' FROM dual
UNION ALL SELECT 'C070', '' , '04:25:03', '04:25:03' FROM dual
UNION ALL SELECT 'C070', '' , '04:25:13', '04:25:13' FROM dual
UNION ALL SELECT 'C070', '' , '04:30:00', '04:30:00' FROM dual
UNION ALL SELECT 'C070', 'B', '04:30:31', '04:30:31' FROM dual
UNION ALL SELECT 'C070', 'B', '04:30:47', '04:30:47' FROM dual
UNION ALL SELECT 'C070', 'B', '04:30:59', '04:30:59' FROM dual
UNION ALL SELECT 'C070', '' , '04:31:11', '04:31:11' FROM dual
UNION ALL SELECT 'C070', '' , '04:31:27', '04:31:27' FROM dual
UNION ALL SELECT 'C070', '' , '05:16:33', '05:16:33' FROM dual
UNION ALL SELECT 'C070', '' , '05:24:33', '05:24:33' FROM dual
UNION ALL SELECT 'C070', 'E', '05:24:46', '05:24:46' FROM dual
UNION ALL SELECT 'C070', 'B', '05:29:40', '05:29:40' FROM dual
UNION ALL SELECT 'C070', '' , '05:29:56', '05:29:56' FROM dual
UNION ALL SELECT 'C070', '' , '05:30:00', '05:30:00' FROM dual
UNION ALL SELECT 'C070', '' , '05:30:08', '05:30:08' FROM dual
UNION ALL SELECT 'C070', '' , '05:30:22', '05:30:22' FROM dual
UNION ALL SELECT 'C070', '' , '05:31:37', '05:31:37' FROM dual
UNION ALL SELECT 'C070', '' , '06:18:02', '06:18:02' FROM dual
UNION ALL SELECT 'C070', '' , '06:26:02', '06:26:02' FROM dual
UNION ALL SELECT 'C070', '' , '06:26:10', '06:26:10' FROM dual
UNION ALL SELECT 'C070', '' , '06:28:10', '06:28:10' FROM dual
UNION ALL SELECT 'C070', '' , '06:28:24', '06:28:24' FROM dual
UNION ALL SELECT 'C070', 'E', '06:30:00', '06:30:00' FROM dual
UNION ALL SELECT 'C070', 'B', '06:30:21', '06:30:21' FROM dual
UNION ALL SELECT 'C070', '' , '06:30:37', '06:30:37' FROM dual
UNION ALL SELECT 'C070', '' , '06:31:05', '06:31:05' FROM dual
UNION ALL SELECT 'C070', '' , '06:31:30', '06:31:30' FROM dual
UNION ALL SELECT 'C070', '' , '06:31:45', '06:31:45' FROM dual
UNION ALL SELECT 'C070', '' , '06:59:52', '06:59:52' FROM dual
UNION ALL SELECT 'C070', '' , '07:07:52', '07:07:52' FROM dual
UNION ALL SELECT 'C070', '' , '07:08:17', '07:08:17' FROM dual
UNION ALL SELECT 'C070', 'E', '07:10:00', '07:10:00' FROM dual
UNION ALL SELECT 'C070', 'B', '07:10:14', '07:10:14' FROM dual
UNION ALL SELECT 'C070', '' , '07:10:30', '07:10:30' FROM dual
UNION ALL SELECT 'C070', '' , '07:10:42', '07:10:42' FROM dual
UNION ALL SELECT 'C070', '' , '07:11:01', '07:11:01' FROM dual
UNION ALL SELECT 'C070', '' , '07:12:15', '07:12:15' FROM dual
UNION ALL SELECT 'C070', '' , '08:02:37', '08:02:37' FROM dual
UNION ALL SELECT 'C070', '' , '08:10:37', '08:10:37' FROM dual
UNION ALL SELECT 'C070', '' , '08:10:45', '08:10:45' FROM dual

)
SELECT channel_id
     , ROW_NUMBER() OVER(
       PARTITION BY channel_id ORDER BY MIN(start_Date)) rn
     , MIN(DECODE(be_Type, 'B', start_Date)) start_Date
     , MAX(DECODE(be_Type, 'E',   end_Date))   end_Date
  FROM (SELECT channel_id, be_Type, start_Date, end_Date
             , COUNT(DECODE(be_Type, 'E', 1))
               OVER(PARTITION BY channel_id ORDER BY start_Date DESC) gb
          FROM t
         WHERE be_type IS NOT NULL
        )
 GROUP BY channel_id, gb
HAVING MAX(DECODE(be_Type, 'E',   end_Date)) IS NOT NULL
 ORDER BY channel_id, start_Date
;

마농(manon94)님이 2015-11-23 17:24에 작성한 댓글입니다.

우와!!!!!!!!!!!!!!

마농님 너무 너무 감사합니다. 

항상 감사합니다. ^^;

 

 

김미림님이 2015-11-23 21:10에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41014GROUP BY 부분과 ROW의 내용을 컬럼으로 올리는 부분을 조언 부탁드립니다. [4]
이상영
2015-11-25
9571
41013global index 를 조회하는 방법이 있을까요? [4]
지나가는
2015-11-25
9422
41011오라클에서 다른 owner의 트리거 소스 볼 수 있는 권한 부여 방법 [1]
김광표
2015-11-24
8870
41010구간 시작 끝 묶음 및 일련번호 부여 문의 드립니다. [2]
김미림
2015-11-23
8682
41009번호 질문.. [3]
김하늘
2015-11-23
8550
41008쿼리 속도 질문좀 드릴게요... (도와주십시오 ㅜㅜ) [4]
검콤이
2015-11-20
9004
41006쿼리좀 도와주세여.. 부탁드립니다. [2]
초보
2015-11-19
8741
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.020초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다