SQL Oracle 计数集群

Question

我有一个基于时间戳的数据集。

     Date                 Value
07-Jul-15 12:05:00          1  
07-Jul-15 12:10:00          1 
07-Jul-15 12:15:00          1 
07-Jul-15 12:20:00          0 
07-Jul-15 12:25:00          0 
07-Jul-15 12:30:00          0 
07-Jul-15 12:35:00          1 
07-Jul-15 12:40:00          1 
07-Jul-15 12:45:00          1 
07-Jul-15 12:50:00          1 
07-Jul-15 12:55:00          0 
07-Jul-15 13:00:00          0 
07-Jul-15 13:05:00          1 
07-Jul-15 13:10:00          1 
07-Jul-15 13:15:00          1 
07-Jul-15 13:20:00          0 
07-Jul-15 13:25:00          0 

我要查询并返回

1. Number of shutdowns: The Number of shut down in this case is 3 based on 0 is ON and 1 is OFF.

2. Period Between every shut down

   Example:

   1. From: 07-Jul-15 12:05:00 To: 07-Jul-15 12:15:00 Duration : 15 Mins
   2. From: 07-Jul-15 12:35:00 To: 07-Jul-15 12:50:00 Duration : 20 Mins

我正在使用甲骨文

Answer 1

您可以在 sqlfiddle 上测试我的答案:http://www.sqlfiddle.com/#!4/9c6a69/16

Test Data

create table test (dttm date, onoff number);

insert into test values (to_date('07-Jul-15 12:05:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:10:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:15:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:20:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 12:25:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 12:30:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 12:35:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:40:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:45:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:50:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:55:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 13:00:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 13:05:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 13:10:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 13:15:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 13:20:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 13:25:00', 'DD-MM-YY HH24:MI:SS'), 0 );

首先，删除所有不必要的列，只保留开/关列:

select t.dttm, t.onoff from test t
where not exists (select 'X' from test tt 
                  where tt.dttm =
                    (select max(ttt.dttm) from test ttt where ttt.dttm < t.dttm)
             and tt.onoff = t.onoff)

number of shutdowns:

with data as (
select t.dttm, t.onoff from test t
where not exists (select 'X' from test tt 
                 where tt.dttm =
                    (select max(ttt.dttm) from test ttt where ttt.dttm < t.dttm)
                 and tt.onoff = t.onoff)
)
select count(*) from data d where d.onoff=0;                                

ontime:

with data as (
select t.dttm, t.onoff from test t
where not exists (select 'X' from test tt 
                 where tt.dttm =
                    (select max(ttt.dttm) from test ttt where ttt.dttm < t.dttm)
                 and tt.onoff = t.onoff)
)
select d1.dttm as ontime, 
d0.dttm as offtime, 
(d0.dttm - d1.dttm) * 24 * 60 as duration 
from data d0, data d1 
where d1.onoff=1 
and d0.dttm = (select min(dd0.dttm) from data dd0 where dd0.dttm > d1.dttm);