SQL Oracle计数集群

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

我正在使用Oracle

解决方法

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

2.每次关闭之间的时间

WITH IntTable AS
( SELECT * FROM
  (
   SELECT dt b_date,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!