一尘不染

SQL Oracle计数群集

sql

我有一个基于时间戳的数据集。

     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. 关闭次数:在这种情况下,关闭次数为3,基于0为ON和1为OFF。
  2. 每次关闭之间的时间段

例子:

1. 从:15年7月7日12:05:00到:15年7月7日12:15:00时长:15分钟
2. 从:15/07/07 12:35:00到:15/07/07 12:50:00持续时间:20分钟

我正在使用Oracle


阅读 194

收藏
2021-03-10

共1个答案

一尘不染

使用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)

2021-03-10