Geek Answers Handbook

SQL Oracle Cluster Counts

I have a dataset based on 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 request and return

  • Number of outputs: the number of shutdowns in this case is 3 based on 0, and 1 is turned off.

  • The period between each shutdown

    Example:

    • From: 07-Jul-15 12:05:00 To: 07-Jul-15 12:15:00 Duration: 15 minutes
    • From: 07-Jul-15 12:35:00 To: 07-Jul-15 12:50:00 Duration: 20 minutes

I am using oracle

+5
source share
4 answers

Using the LEAD and LAG functions in ORACLE, you can build these queries:

1. Number of stops:

 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

2. The period between each stop

 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

+1
source

You can check 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, delete all unnecessary columns and save only 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 outputs:

 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);
+1
source

Your question has two parts, and both should be handled in two different requests. Because the output is based on two different sets of strings.

Customization

 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 outputs: the number of shutdowns in this case is 3 based on 0, and 1 is turned off.

You need to use the analytic function LAG () .

 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, the output has 3 lines, which means 3 times the shutdown event. To get an account, you just need to use the COUNT function.

  1. The period between each shutdown

Example:

  • From: 07-Jul-15 12:05:00 To: 07-Jul-15 12:15:00 Duration: 15 minutes
  • From: 07-Jul-15 12:35:00 To: 07-Jul-15 12:50:00 Duration: 20 minutes

Once again you need to use the analytic function LAG () . The difference between dates returns the number of days . To convert it to minutes, multiply it by 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 the above query, to find the total time when the system was ready, 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 went up by 35 minutes .

0
source

Hi, I tried with the code below showing the 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
0
source

More articles:


All Articles