SQL Oracle计数群集 [英] SQL Oracle Counting Clusters
本文介绍了SQL Oracle计数群集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个基于时间戳的数据集.
I have a data set which is based on a timestamp.
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
我想查询并返回
- 关闭次数:在这种情况下,关闭次数为3,基于0为ON和1为OFF.
-
每次关闭之间的时间段
- 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
示例:
- 发件人:15年7月7日12:05:00到:15年7月7日12:15:00时长:15分钟
- 发件人:15年7月7日12:35:00到:15年7月7日12:50:00时长:20分钟
我正在使用Oracle
I am using Oracle
推荐答案
在ORACLE中使用LEAD和LAG函数,您可以构建以下查询:
Using LEAD and LAG functions in ORACLE you can built these queries:
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.每次关闭之间的时间段
2.Period Between every shut down
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
这篇关于SQL Oracle计数群集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文