Home » SQL & PL/SQL » SQL & PL/SQL » Avarege over 5 minutes
Avarege over 5 minutes [message #287038] Tue, 11 December 2007 00:05 Go to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Hi all

I have been working on a script ( see script below ) to get an average time between to event's out of a database. I did mange to get that script working for averages over an hour. ( With some help ) But now I need the averages over 5 minutes.

What have I tried. I have tried to add minutes to my script. Then use the between statement in the over. But that gives me a rolling average. I need a true average in a time frame of 5 minutes. This means i wil get just 12 data ouputs for every hour not 60.

I looked every where yesterday to come up with an example of a real average but could not find one. So I am turning to this forum to help me.


SELECT DISTINCT AVG.day
     , AVG.month
     , AVG.year
     , AVG.h
     , AVG.AVG
FROM 
    (SELECT  AVG(nvl(AVR_T.T_D_sec,0) ) 
               OVER ( PARTITION by mod_day.year , mod_day.month , mod_day.day , mod_day.h ) AVG
           , mod_day.DAY
           , mod_day.month
           , mod_day.year
           , mod_day.h h
           , AVr_T.r_date
           , AVr_T.r_hour
           , AVr_T.r_day
           , AVr_T.r_month
           , AVr_T.r_year
     FROM 
        (select
               SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),0,10) r_date
              ,SUBSTR(REC.LOCAL_TM,10,2 ) R_hour 
              ,SUBSTR(REC.LOCAL_TM,3,2 ) R_year
              ,SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),4,3) r_month
              ,SUBSTR(REC.LOCAL_TM,7,2 ) R_day
              ,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+ 
                  (SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+ 
                  SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
         FROM
            TMS_MESSAGE_AUDIT_LOG WCA , 
            TMS_MESSAGE_AUDIT_LOG REC
         WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND 
               REC.MESSAGE_STATUS = '(Receive)' AND 
               WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and 
               WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1) 
        ) AVR_T ,
        (SELECT
             SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),0,2) DAY
            ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),4,3) month
            ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),8,2) year
            ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'hh24') h 
          FROM
             dual 
          connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 25
        ) Mod_day
  WHERE mod_day.h = R_hour (+) and
        mod_day.day = AVR_T.R_day (+) and
        mod_day.month = AVR_T.R_month (+) 
   ) AVG 
;



Result of this code is


DAY MONTH YEAR H AVG

05 DEC 07 00 0
05 DEC 07 01 0
05 DEC 07 02 0
05 DEC 07 03 0
05 DEC 07 04 0
05 DEC 07 05 0
05 DEC 07 06 0
05 DEC 07 07 0
05 DEC 07 08 0
05 DEC 07 09 0
05 DEC 07 10 4.33333333333333
05 DEC 07 11 0
05 DEC 07 12 5
05 DEC 07 13 0
05 DEC 07 14 5.75
05 DEC 07 15 6
05 DEC 07 16 0
05 DEC 07 17 0
05 DEC 07 18 0
05 DEC 07 19 0
05 DEC 07 20 0
05 DEC 07 21 0
05 DEC 07 22 0
05 DEC 07 23 0


The table creation and data is in the attached file.

Marcel Koert

[Updated on: Tue, 11 December 2007 00:47] by Moderator

Report message to a moderator

Re: Avarege over 5 minutes [message #287066 is a reply to message #287038] Tue, 11 December 2007 00:52 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Hi

I just got one step further.
I wrote the following script. This is my Model day that is part of the average's script. I found how i can get 5 minutes intervals. Still not perfect because it gives only 24 lines but it is a step.

SELECT
    SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),0,2) DAY
   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),4,3) month
   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),8,2) year
   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h 
   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
   dual 
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288


But it gives only a result of 24 lines i will need 288.

DAY MONTH YEAR H M

05 DEC 07 00 05
05 DEC 07 00 10
05 DEC 07 00 15
05 DEC 07 00 20
05 DEC 07 00 25
05 DEC 07 00 30
05 DEC 07 00 35
05 DEC 07 00 40
05 DEC 07 00 45
05 DEC 07 00 50
05 DEC 07 00 55
05 DEC 07 01 00
05 DEC 07 01 05
05 DEC 07 01 10
05 DEC 07 01 15
05 DEC 07 01 20
05 DEC 07 01 25
05 DEC 07 01 30
05 DEC 07 01 35
05 DEC 07 01 40
05 DEC 07 01 45
05 DEC 07 01 50
05 DEC 07 01 55
06 DEC 07 02 00
06 DEC 07 02 05


Marcel Koert
Re: Avarege over 5 minutes [message #287093 is a reply to message #287066] Tue, 11 December 2007 01:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I get 287 rows, as I suspected. One tip though: you can rewrite the connect-by clause as :
connect by level < 288
Date calculations are done in units of days. you subtract two subsequent days without time information, resulting in the constant "1".

MHE

[Updated on: Tue, 11 December 2007 01:30]

Report message to a moderator

Re: Avarege over 5 minutes [message #287100 is a reply to message #287093] Tue, 11 December 2007 01:44 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Hi

The reason that i put the two date's in there is that i want to be able to run this query over a longer period then 1 day. Something like

SELECT
    SUBSTR(to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),0,2) DAY
   ,SUBSTR(to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),4,3) month
   ,SUBSTR(to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),8,2) year
   ,to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/288), 'hh24') h 
   ,to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
   dual 
connect by level < (to_date('30-NOV-07','dd-mon-rr') - to_date('01-NOV-07','dd-mon-rr')) * 288



I have tried this now in TOAD and in SQLPLUS in both i am getting different results. TOAD only gives me 24 lines SQLPLUS gives me 1 line of output. What am i missing here if you can get 288 lines of output.

Marcel Koert
Re: Avarege over 5 minutes [message #287105 is a reply to message #287100] Tue, 11 December 2007 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Behaviour changes with version, what is yours?

Regards
Michel
Re: Avarege over 5 minutes [message #287109 is a reply to message #287038] Tue, 11 December 2007 01:49 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Hi

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Dec 11 08:37:10 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
JServer Release 9.2.0.8.0 - Production

TOAD
8.0.0.47

Marcel Koert
Re: Avarege over 5 minutes [message #287111 is a reply to message #287109] Tue, 11 December 2007 01:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In SQL*Plus (might work in TOAD too): wrap the connect by query in an inner select. It's a SQL*plus feature.

MHE
Re: Avarege over 5 minutes [message #287114 is a reply to message #287109] Tue, 11 December 2007 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/93882/286728/102589/#msg_286728

Regards
Michel
Re: Avarege over 5 minutes [message #287120 is a reply to message #287038] Tue, 11 December 2007 02:27 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Thanks for that

That solved the model day. The model day now looks like

select     *
from
(SELECT
   SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),0,2) DAY
   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),4,3) month
   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),8,2) year
   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h 
   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
   dual 
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288)


Now the problem of the average's. If i use this model day that we now created the averages will be of. How do i now get the avg statment to calculated the avrages between two points in my modelday.

What i have tried is this

SELECT DISTINCT AVG.day
     , AVG.month
     , AVG.year
     , AVG.h
	 , AVG.M
     , AVG.AVG
FROM 
    (SELECT  AVG(nvl(AVR_T.T_D_sec,0) ) OVER ( PARTITION by mod_day.year , mod_day.month , mod_day.day , mod_day.h , mod_day.M) AVG
           , mod_day.DAY
           , mod_day.month
           , mod_day.year
           , mod_day.h h
           , mod_day.M M
		   , AVr_T.r_date
           , AVr_T.r_hour
           , AVr_T.r_day
           , AVr_T.r_month
           , AVr_T.r_year
     FROM 
        (select
               SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),0,10) r_date
              ,SUBSTR(REC.LOCAL_TM,10,2 ) R_hour
			  ,SUBSTR(REC.LOCAL_TM,12,2 ) R_min 
              ,SUBSTR(REC.LOCAL_TM,3,2 ) R_year
              ,SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),4,3) r_month
              ,SUBSTR(REC.LOCAL_TM,7,2 ) R_day
			  ,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+ 
                  (SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+ 
                  SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
         FROM
            TMS_MESSAGE_AUDIT_LOG WCA , 
            TMS_MESSAGE_AUDIT_LOG REC
         WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND 
               REC.MESSAGE_STATUS = '(Receive)' AND 
               WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and 
               WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1) 
        ) AVR_T ,
        (SELECT
  			    SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),0,2) DAY
			   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),4,3) month
			   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),8,2) year
   			   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h 
   			   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
		FROM
   			dual 
		connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288
        ) Mod_day
  WHERE mod_day.h = R_hour (+) and
        mod_day.day = AVR_T.R_day (+) and
        mod_day.month = AVR_T.R_month (+) 
   ) AVG 
;


But then it still calculates the averages over the hour and just display's them 12 times. I know this would not work because it is just a normal over statment i need something that does a averages between a point in the model day and the next point in the model day. And then takes the next point.

Marcel Koert
Re: Avarege over 5 minutes [message #287130 is a reply to message #287038] Tue, 11 December 2007 02:50 Go to previous messageGo to next message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Hi all

I just need a confirmation.
I think i just solved it but i want to be sure.
This is the code

SELECT DISTINCT AVG.day
     , AVG.month
     , AVG.year
     , AVG.h
     , AVG.M
     , AVG.AVG
FROM 
    (SELECT  AVG(nvl(AVR_T.T_D_sec,0) ) 
	          OVER ( PARTITION by mod_day.year , mod_day.month , mod_day.day , mod_day.h , mod_day.M ) AVG
           , mod_day.DAY
           , mod_day.month
           , mod_day.year
           , mod_day.h h
           , mod_day.M M
	   , AVr_T.r_date
	   , AVr_T.R_min 
           , AVr_T.r_hour
           , AVr_T.r_day
           , AVr_T.r_month
           , AVr_T.r_year
     FROM 
        (select
               SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),0,10) r_date
              ,SUBSTR(REC.LOCAL_TM,10,2 ) R_hour
			  ,SUBSTR(REC.LOCAL_TM,12,2 ) R_min 
              ,SUBSTR(REC.LOCAL_TM,3,2 ) R_year
              ,SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),4,3) r_month
              ,SUBSTR(REC.LOCAL_TM,7,2 ) R_day
			  ,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+ 
                  (SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+ 
                  SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
         FROM
            TMS_MESSAGE_AUDIT_LOG WCA , 
            TMS_MESSAGE_AUDIT_LOG REC
         WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND 
               REC.MESSAGE_STATUS = '(Receive)' AND 
               WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and 
               WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1) 
        ) AVR_T ,
        (SELECT
  			    SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),0,2) DAY
			   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),4,3) month
			   ,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),8,2) year
   			   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h 
   			   ,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
		FROM
   			dual 
		connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288
        ) Mod_day
  WHERE mod_day.h = R_hour (+) and
        mod_day.day = AVR_T.R_day (+) and
        mod_day.month = AVR_T.R_month (+) and
	mod_day.m = AVR_T.R_min(+)
   ) AVG 
;



Does this real calculate the averages between every 5 minuuts.
If so it was simpler then i thought.

Marcel Koert
Re: Avarege over 5 minutes [message #287136 is a reply to message #287130] Tue, 11 December 2007 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This may be of some help:
SQL> create table t (dt date, val integer);

Table created.

SQL> insert into t 
  2  select sysdate-dbms_random.value(0,2000)/86400, 
  3         round(dbms_random.value(1,100),2)
  4  from dual 
  5  connect by level <= 30
  6  /

30 rows created.

SQL> select * from t order by dt;
DT                         VAL
------------------- ----------
11/12/2007 09:42:19         46
11/12/2007 09:42:45         61
11/12/2007 09:43:03          6
11/12/2007 09:43:08         99
11/12/2007 09:43:42         61
11/12/2007 09:44:18         11
11/12/2007 09:45:16         13
11/12/2007 09:46:49         79
11/12/2007 09:49:30         72
11/12/2007 09:50:04         50
11/12/2007 09:50:57         88
11/12/2007 09:53:05          3
11/12/2007 09:53:56         95
11/12/2007 09:55:23         98
11/12/2007 09:56:46          2
11/12/2007 09:58:08         94
11/12/2007 10:01:26         44
11/12/2007 10:02:49         90
11/12/2007 10:04:41         72
11/12/2007 10:08:03         10
11/12/2007 10:10:24         78
11/12/2007 10:11:14         65
11/12/2007 10:12:08         39
11/12/2007 10:12:43         28
11/12/2007 10:13:08          2
11/12/2007 10:13:22         20
11/12/2007 10:13:41         68
11/12/2007 10:13:56         96
11/12/2007 10:14:40         63
11/12/2007 10:15:20         55

30 rows selected.

SQL> with 
  2    times as (select trunc(sysdate-1/24,'HH')+5*level/1440 tim from dual connect by level <= 18)
  3  select to_char(tim,'DD/MM/YYYY HH24:MI') "Time", nvl(round(avg(val),2),0) "Avg"
  4  from times, t
  5  where t.dt (+) between times.tim and times.tim+5/1440-1/86400
  6  group by times.tim
  7  order by times.tim
  8  /
Time                    Avg
---------------- ----------
11/12/2007 09:05          0
11/12/2007 09:10          0
11/12/2007 09:15          0
11/12/2007 09:20          0
11/12/2007 09:25          0
11/12/2007 09:30          0
11/12/2007 09:35          0
11/12/2007 09:40      47.33
11/12/2007 09:45      54.67
11/12/2007 09:50         59
11/12/2007 09:55      64.67
11/12/2007 10:00      68.67
11/12/2007 10:05         10
11/12/2007 10:10         51
11/12/2007 10:15         55
11/12/2007 10:20          0
11/12/2007 10:25          0
11/12/2007 10:30          0

18 rows selected.

Regards
Michel
Re: Avarege over 5 minutes [message #287171 is a reply to message #287038] Tue, 11 December 2007 05:26 Go to previous message
mklynx
Messages: 17
Registered: December 2007
Junior Member
Thanks everybody.

Micheal from you post i have created the latest script that does what it should do.

   select to_char(times.tim,'DD/MM/YYYY HH24:MI') "Time"
          ,nvl(round(avg(T_D_sec),2),0) "Avg"
	from 
         (select
                TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3') r_date
		  ,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+ 
                  (SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+ 
                  SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
         FROM
            TMS_MESSAGE_AUDIT_LOG WCA , 
            TMS_MESSAGE_AUDIT_LOG REC
         WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND 
               REC.MESSAGE_STATUS = '(Receive)' AND 
               WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and 
               WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1) 
        ) AVR_T ,
		
		(select 
		  trunc((to_timestamp('03/12/2007 01:00:00','dd/mm/yyyy hh24:mi:ss'))-1/24,'HH')+5*level/1440 tim 
		 from 
		   dual 
		 connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('03-DEC-07','dd-mon-rr')) * 288 ) times
		
where AVR_T.R_date (+) between times.tim and times.tim+5/1440-1/86400
 group by to_char(times.tim,'DD/MM/YYYY HH24:MI')
 order by to_char(times.tim,'DD/MM/YYYY HH24:MI')
;


And this works like a charm.
Previous Topic: problem while selecting from multiple table
Next Topic: Filter in hierarchical query
Goto Forum:
  


Current Time: Thu Dec 08 16:26:56 CST 2016

Total time taken to generate the page: 0.11718 seconds