SQL Oracle计数集群

前端之家收集整理的这篇文章主要介绍了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

我想查询并返回

@H_403_7@
  • Number of shutdowns: The Number of shut down in this case is 3 based on 0 is ON and 1 is OFF.
  • Period Between every shut down

    Example:

    @H_403_7@
  • From: 07-Jul-15 12:05:00 To: 07-Jul-15 12:15:00 Duration : 15 Mins
  • From: 07-Jul-15 12:35:00 To: 07-Jul-15 12:50:00 Duration : 20 Mins
  • 我正在使用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

    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

    猜你在找的MsSQL相关文章