SQL Oracle计数群集

使用ORACLE中的LEAD和LAG函数，您可以构建以下查询：

1.关机次数：

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

[SQLFiddle demo](http://sqlfiddle.com/#!4/d3eac2/13)

2.期间每次关闭之间

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

[SQLFiddle demo](http://sqlfiddle.com/#!4/d3eac2/19)