我有一个基于时间戳的数据集.
- 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
我想查询并返回
- 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:
- 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
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
