sql query

[malay_biswal]

Hi,

I've a table it has data for each 5 minutes.so table looks like this.
BEGINDATE AVGCPU
----------------------- -----------
11/6/2005 12:05:00 AM 10
11/6/2005 12:10:00 AM 12
11/6/2005 12:15:00 AM 15
11/6/2005 12:20:00 AM 08
so on.....

My requirement is to average the AVGCPU for each 15 minutes starting from 11/6/2005 12:05:00 AM.

Any expert advice.

Thanks in advance.

[tamilselvan]

Can you post the output you want?

Tamil

[malay_biswal]

output like

BEGINDATE AVGCPU
----------------------- -----------
11/6/2005 12:05:00 AM 12.3
11/6/2005 12:20:00 AM 08

[tamilselvan]

Thanks for posting an interesting query.
Here I say .....

SQL> select * from r1 ;

RDATE CPU
------------------- ----------
09-11-2005 12:05:00 10
09-11-2005 12:10:00 12
09-11-2005 12:15:00 15
09-11-2005 12:20:00 8

SQL> get 3rows_avg.sql
1 select *
2 from (
3 select rdate,
4 cpu,
5 avg(cpu) over ( order by rdate
6 rows between 0 preceding
and 2 following) rows_3_avg
7 from r1
8 )
9* where to_char(rdate,'MI') in ( '05','20')
SQL> /

RDATE CPU ROWS_3_AVG
------------------- ---------- ----------
09-11-2005 12:05:00 10 12.33
09-11-2005 12:20:00 8 8.00



Note: you can add relevant Minutes in the WHERE clause.

Tamil

[malay_biswal]

Thanks a lot. It worked.