CODEWITHSUNDEEP
sqloracle-databaseaverage
Peter Penzov
Peter Penzov

Reputation: 1658

Get average value for time period in Oracle

I have this table which will hold huge data. New SQL query with data will be inserted every 5 minutes. 

 EVENTID   EVENTDATE             CPU
--------- --------------------- --------
   23523   2014-10-28 20:26:51       33
 2234234   2014-10-28 20:27:05       22
     234   2014-10-28 20:27:19      234
      22   2014-10-28 20:27:29       22
     234   2014-10-28 20:30:13      234
     234   2014-10-28 20:38:18    23234
     234   2014-10-28 21:50:06      234
     234   2014-10-28 20:41:51    12312
     234   2014-09-30 23:00:34      123
     234   2014-11-28 21:18:23      234
     234   2014-10-30 21:24:28      234
     234   2014-10-29 21:27:52    23434

I would like to display the data in a chart for 30 days. But I have 2 problems.

I would like to display the average value from all data into the table in 30 values.

So first I need to get the average value for every time interval. Second I want to limit the result in 30 values. Is there a way to do this?

Upvotes: 2

Views: 1567

Answers (1)

Multisync
Multisync

Reputation: 8787

select trunc(eventdate, 'DD'), avg(cpu)
from tab where eventdate >= trunc(sysdate) - 30
group by trunc(eventdate, 'DD') 
order by 1;

trunc(eventdate, 'DD') removes time from the eventdate. When you group by this expression Oracle calculates avg ofr each day.

If you want to show exatly 30 rows you may create a join with some special query which return consequent dates:

select start_date, avg(cpu)
from (select start_date + lvl start_date, start_date + lvl + 1 - 1/24/60/60 end_date
       from (select trunc(sysdate-30) start_date, level lvl from dual connect by level <= 30))
     left join tab on eventdate between start_date and end_date   
group by start_date
order by 1;

Upvotes: 3

Related Questions