SQL Oracle count de clústeres

Tengo un set de datos que se basa en una 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 

Me gustaría consultar y regresar

  1. Número de paradas: El número de apagado en este caso es 3 en function de que 0 está activado y 1 está desactivado.
  2. Período entre cada cierre

    Ejemplo:

    1. Desde: 07-jul-15 12:05:00 Hasta: 07-jul-15 12:15:00 Duración: 15 minutos
    2. Desde: 07-jul-15 12:35:00 Hasta: 07-jul-15 12:50:00 Duración: 20 minutos

Estoy usando Oracle

Usando las funciones LEAD y LAG en ORACLE puedes build estas consultas:

1. Número de paradas:

 WITH IntTable AS ( SELECT * FROM ( SELECT dt b_date,value,LEAD(dt) OVER (ORDER BY dt) e_date FROM ( select "Date" dt,"Value" value, LAG("Value") OVER (ORDER BY "Date") pvalue, LEAD("Value") OVER (ORDER BY "Date") nvalue from T ) T1 WHERE pvalue is NULL or value<>pvalue or nvalue is NULL ) WHERE E_DATE is NOT NULL ) SELECT COUNT(*) FROM IntTable where value = 0 

Demostración de SQLFiddle

2. Período entre cada cierre

 WITH IntTable AS ( SELECT * FROM ( SELECT dt b_date,value,LEAD(dt) OVER (ORDER BY dt) e_date FROM ( select "Date" dt,"Value" value, LAG("Value") OVER (ORDER BY "Date") pvalue, LEAD("Value") OVER (ORDER BY "Date") nvalue from T ) T1 WHERE pvalue is NULL or value<>pvalue or nvalue is NULL ) WHERE E_DATE is NOT NULL ) SELECT b_date,e_date, (e_date-b_date) * 60 * 24 FROM IntTable where value = 1 

Demostración de SQLFiddle

Puede probar mi respuesta en sqlfiddle: http://www.sqlfiddle.com/#!4/9c6a69/16

Datos de testing

 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 ); 

En primer lugar, elimine todas las columnas innecesarias y conserve solo las columnas on / off:

 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) 

número de paradas:

 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; 

a time:

 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); 

Su pregunta tiene dos partes, y ambas deben manejarse en dos consultas diferentes. Como la salida se basa en dos sets diferentes de filas.

Preparar

 SQL> CREATE TABLE t AS SELECT * FROM(WITH DATA(dt, status) AS( 2 SELECT to_date('07-Jul-15 12:05:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 3 SELECT to_date('07-Jul-15 12:10:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 4 SELECT to_date('07-Jul-15 12:15:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 5 SELECT to_date('07-Jul-15 12:20:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL 6 SELECT to_date('07-Jul-15 12:25:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL 7 SELECT to_date('07-Jul-15 12:30:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL 8 SELECT to_date('07-Jul-15 12:35:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 9 SELECT to_date('07-Jul-15 12:40:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 10 SELECT to_date('07-Jul-15 12:45:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 11 SELECT to_date('07-Jul-15 12:50:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 12 SELECT to_date('07-Jul-15 12:55:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL 13 SELECT to_date('07-Jul-15 13:00:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL 14 SELECT to_date('07-Jul-15 13:05:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 15 SELECT to_date('07-Jul-15 13:10:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 16 SELECT to_date('07-Jul-15 13:15:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL 17 SELECT to_date('07-Jul-15 13:20:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL 18 SELECT to_date('07-Jul-15 13:25:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual 19 ) SELECT * FROM DATA); Table created. 

Datos de tabla

 SQL> SELECT * FROM t; DT STATUS ------------------ ---------- 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 17 rows selected. 
  1. Número de paradas: El número de apagado en este caso es 3 en function de que 0 está activado y 1 está desactivado.

Necesita usar la function analítica LAG () .

 SQL> SELECT TO_CHAR(dt, 'dd-Mon-rr hh24:mi:ss') dt, 2 status 3 FROM 4 (SELECT t.*, 5 CASE 6 WHEN lag(status) OVER(ORDER BY dt) <> status 7 THEN 1 8 ELSE 0 9 END chg 10 FROM t 11 ) 12 WHERE status = 0 13 AND chg =1; DT STATUS ------------------ ---------- 07-Jul-15 12:20:00 0 07-Jul-15 12:55:00 0 07-Jul-15 13:20:00 0 

Entonces, hay 3 filas en la salida, lo que significa 3 veces que ocurrió el evento de apagado. Para get el conteo, solo necesita usar la function COUNT .

  1. Período entre cada cierre

Ejemplo:

  1. Desde: 07-jul-15 12:05:00 Hasta: 07-jul-15 12:15:00 Duración: 15 minutos
  2. Desde: 07-jul-15 12:35:00 Hasta: 07-jul-15 12:50:00 Duración: 20 minutos

Una vez más, debe usar la function analítica LAG () . La diferencia entre las dates devuelve el número de días . Para convertirlo en minutos, multiplíquelo con 24 * 60 .

 SQL> SELECT * FROM( 2 SELECT t.*, 3 CASE 4 WHEN lag(status) OVER(ORDER BY dt) <> status 5 THEN 1 6 ELSE 0 7 END chg, 8 24*60*(dt - lag(dt) over(order by dt)) gap 9 FROM t 10 ) t 11 WHERE status =1 and chg =0; DT STATUS CHG GAP ------------------ ---------- ---------- ---------- 07-Jul-15 12:05:00 1 0 07-Jul-15 12:10:00 1 0 5 07-Jul-15 12:15:00 1 0 5 07-Jul-15 12:40:00 1 0 5 07-Jul-15 12:45:00 1 0 5 07-Jul-15 12:50:00 1 0 5 07-Jul-15 13:10:00 1 0 5 07-Jul-15 13:15:00 1 0 5 8 rows selected. SQL> 

Entonces, basado en la consulta anterior, para encontrar el time total cuando el sistema estaba activo, use SUMA .

 SQL> SELECT SUM(gap) on_minutes FROM( 2 SELECT t.*, 3 CASE 4 WHEN lag(status) OVER(ORDER BY dt) <> status 5 THEN 1 6 ELSE 0 7 END chg, 8 24*60*(dt - lag(dt) over(order by dt)) gap 9 FROM t 10 ) t 11 WHERE status =1 and chg =0; ON_MINUTES ---------- 35 SQL> 

Entonces, el sistema estuvo listo durante 35 minutes .

Hola, he intentado con el código a continuación, está mostrando resultados como se esperaba

 with cte as ( select Rownum rn,a.* from table a ) select date1 as start_time,endtime, (endtime-date1)*24*60 as period from ( select date1,value,case when value=1 then lead(date1) over (order by date1) else null end as endtime from ( select date1,value from cte where cte.rn=1 union all select a.date1,a.value from cte a join cte b on a.rn=b.rn+1 and( (a.value=1 and b.value=0) or (a.value=0 and b.value=1)) ) order by date1) where value=1