szakwani
Reputation: 384
SQL Oracle Counting Clusters
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
I would like to query and return
- 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
I am using Oracle
Upvotes: 5
Views: 145
Answers (4)
valex
Reputation: 24134
Using LEAD and LAG functions in ORACLE you can built these queries:
1.Number of shutdowns:
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.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
Upvotes: 1
Frank Ockenfuss
Reputation: 2043
You can test my answer on sqlfiddle: http://www.sqlfiddle.com/#!4/9c6a69/16
Test Data
create table test (dttm date, onoff number);
insert into test values (to_date('07-Jul-15 12:05:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:10:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:15:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:20:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 12:25:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 12:30:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 12:35:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:40:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:45:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:50:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 12:55:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 13:00:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 13:05:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 13:10:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 13:15:00', 'DD-MM-YY HH24:MI:SS'), 1 );
insert into test values (to_date('07-Jul-15 13:20:00', 'DD-MM-YY HH24:MI:SS'), 0 );
insert into test values (to_date('07-Jul-15 13:25:00', 'DD-MM-YY HH24:MI:SS'), 0 );
First of all, remove all unnecessary columns and keep only the on/off columns:
select t.dttm, t.onoff from test t
where not exists (select 'X' from test tt
where tt.dttm =
(select max(ttt.dttm) from test ttt where ttt.dttm < t.dttm)
and tt.onoff = t.onoff)
number of shutdowns:
with data as (
select t.dttm, t.onoff from test t
where not exists (select 'X' from test tt
where tt.dttm =
(select max(ttt.dttm) from test ttt where ttt.dttm < t.dttm)
and tt.onoff = t.onoff)
)
select count(*) from data d where d.onoff=0;
ontime:
with data as (
select t.dttm, t.onoff from test t
where not exists (select 'X' from test tt
where tt.dttm =
(select max(ttt.dttm) from test ttt where ttt.dttm < t.dttm)
and tt.onoff = t.onoff)
)
select d1.dttm as ontime,
d0.dttm as offtime,
(d0.dttm - d1.dttm) * 24 * 60 as duration
from data d0, data d1
where d1.onoff=1
and d0.dttm = (select min(dd0.dttm) from data dd0 where dd0.dttm > d1.dttm);
Upvotes: 1
Tharunkumar Reddy
Reputation: 2813
Hi i have tried with below code it's displaying results as expected
with cte
as
(
select Rownum rn,a.* from table a
)
select date1 as start_time,endtime, (endtime-date1)*24*60 as period
from
(
select date1,value,case
when
value=1 then
lead(date1) over (order by date1)
else null
end
as endtime
from
(
select date1,value from cte where cte.rn=1
union all
select a.date1,a.value from cte a
join
cte b
on
a.rn=b.rn+1
and( (a.value=1 and b.value=0) or (a.value=0 and b.value=1))
) order by date1) where value=1
Upvotes: 0
Lalit Kumar B
Reputation: 49062
Your question has two parts, and both needs to be handled in two different queries. As the output is based on two different sets of rows.
Setup
SQL> CREATE TABLE t AS SELECT * FROM(WITH DATA(dt, status) AS(
2 SELECT to_date('07-Jul-15 12:05:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
3 SELECT to_date('07-Jul-15 12:10:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
4 SELECT to_date('07-Jul-15 12:15:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
5 SELECT to_date('07-Jul-15 12:20:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL
6 SELECT to_date('07-Jul-15 12:25:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL
7 SELECT to_date('07-Jul-15 12:30:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL
8 SELECT to_date('07-Jul-15 12:35:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
9 SELECT to_date('07-Jul-15 12:40:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
10 SELECT to_date('07-Jul-15 12:45:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
11 SELECT to_date('07-Jul-15 12:50:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
12 SELECT to_date('07-Jul-15 12:55:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL
13 SELECT to_date('07-Jul-15 13:00:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL
14 SELECT to_date('07-Jul-15 13:05:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
15 SELECT to_date('07-Jul-15 13:10:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
16 SELECT to_date('07-Jul-15 13:15:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 1 FROM dual UNION ALL
17 SELECT to_date('07-Jul-15 13:20:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual UNION ALL
18 SELECT to_date('07-Jul-15 13:25:00', 'dd-Mon-rr hh24:mi:ss', 'NLS_DATE_LANGUAGE=AMERICAN'), 0 FROM dual
19 ) SELECT * FROM DATA);
Table created.
Table data
SQL> SELECT * FROM t;
DT STATUS
------------------ ----------
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
17 rows selected.
- Number of shutdowns: The Number of shut down in this case is 3 based on 0 is ON and 1 is OFF.
You need to use LAG() analytic function.
SQL> SELECT TO_CHAR(dt, 'dd-Mon-rr hh24:mi:ss') dt,
2 status
3 FROM
4 (SELECT t.*,
5 CASE
6 WHEN lag(status) OVER(ORDER BY dt) <> status
7 THEN 1
8 ELSE 0
9 END chg
10 FROM t
11 )
12 WHERE status = 0
13 AND chg =1;
DT STATUS
------------------ ----------
07-Jul-15 12:20:00 0
07-Jul-15 12:55:00 0
07-Jul-15 13:20:00 0
So, there are 3 rows in the output, which means 3 times the shutdown event occurred. To get the count, you just need to use the COUNT function.
- 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
Once again, you need to use LAG() analytic function. Difference between dates returns number of days. To convert it into minutes, multiply it with 24*60.
SQL> SELECT * FROM(
2 SELECT t.*,
3 CASE
4 WHEN lag(status) OVER(ORDER BY dt) <> status
5 THEN 1
6 ELSE 0
7 END chg,
8 24*60*(dt - lag(dt) over(order by dt)) gap
9 FROM t
10 ) t
11 WHERE status =1 and chg =0;
DT STATUS CHG GAP
------------------ ---------- ---------- ----------
07-Jul-15 12:05:00 1 0
07-Jul-15 12:10:00 1 0 5
07-Jul-15 12:15:00 1 0 5
07-Jul-15 12:40:00 1 0 5
07-Jul-15 12:45:00 1 0 5
07-Jul-15 12:50:00 1 0 5
07-Jul-15 13:10:00 1 0 5
07-Jul-15 13:15:00 1 0 5
8 rows selected.
SQL>
So, based on above query, to find the total time when the system was up, use SUM.
SQL> SELECT SUM(gap) on_minutes FROM(
2 SELECT t.*,
3 CASE
4 WHEN lag(status) OVER(ORDER BY dt) <> status
5 THEN 1
6 ELSE 0
7 END chg,
8 24*60*(dt - lag(dt) over(order by dt)) gap
9 FROM t
10 ) t
11 WHERE status =1 and chg =0;
ON_MINUTES
----------
35
SQL>
So, the system was up for 35 minutes.
Upvotes: 0