SQL Oracle Counting Clusters -

i have data set based on timestamp.

     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  

i query , return

1. number of shutdowns: number of shut down in case 3 based on 0 on , 1 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

i using oracle

using lead , lag functions in oracle can built these queries:

1.number of shutdowns:

with inttable ( select *   (    select dt b_date,value,lead(dt) on (order dt) e_date       (         select "date" dt,"value" value,                lag("value") on (order "date") pvalue,                lead("value") on (order "date") nvalue         t      ) t1      pvalue null or value<>pvalue or nvalue null     ) e_date not null ) select count(*) inttable value = 0  

sqlfiddle demo

2.period between every shut down

with inttable ( select *   (    select dt b_date,value,lead(dt) on (order dt) e_date       (         select "date" dt,"value" value,                lag("value") on (order "date") pvalue,                lead("value") on (order "date") nvalue         t      ) t1      pvalue null or value<>pvalue or nvalue null     ) e_date not null ) select b_date,e_date, (e_date-b_date) * 60 * 24 inttable value = 1  

sqlfiddle demo