with tt as
(
select 'A' panel_id , 1 flag , to_date('20130101 01:00:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:01:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:01:59','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:02:58','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:05:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:06:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:07:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:08:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:09:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:10:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:11:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 1 flag , to_date('20130101 01:12:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 1 flag , to_date('20130101 01:14:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:15:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:16:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:18:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 3 flag , to_date('20130101 01:00:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 3 flag , to_date('20130101 01:01:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 1 flag , to_date('20130101 01:10:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 1 flag , to_date('20130101 01:13:50','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 3 flag , to_date('20130101 01:14:50','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 3 flag , to_date('20130101 01:15:50','yyyymmdd hh24:mi:ss') time from dual
)
select * from tt
time 컬럼으로 나래비 되 있는 상태에서 flag 값이 1을 기점으로 바로 각 로우의 time과의 차이가 60초 이하인 모든 데이터를 가져오는 방법입니다.
도중 flag 값이 3이면서 60초 이상인 로우가나오면 다음의 flag 값 1을 만나기까지 그냥 무시를 해야됩니다.
결과값은 아래와 같이 나와야 하는데 어떻게 해야 할까요?
with tt as
(
select 'A' panel_id , 1 flag , to_date('20130101 01:00:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:01:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:01:59','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:02:58','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 1 flag , to_date('20130101 01:12:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 1 flag , to_date('20130101 01:14:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:15:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'A' panel_id , 3 flag , to_date('20130101 01:16:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 1 flag , to_date('20130101 01:10:00','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 1 flag , to_date('20130101 01:13:50','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 3 flag , to_date('20130101 01:14:50','yyyymmdd hh24:mi:ss') time from dual union all
select 'B' panel_id , 3 flag , to_date('20130101 01:15:50','yyyymmdd hh24:mi:ss') time from dual
)
select * from tt
해결 할 수 있도록 도와주시면 감사하겠습니다.ㅠ