我有一个基于时间戳的数据集.
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
