Re: SQL Question: Averages of intervals.

On 2009-01-23, Daniel Staal <DStaal(at)usa(dot)net> wrote:
>
> I have the following table:
>
> LoadTable:
> MachineName - String
> Date - Date
> Time - Time
> IOW - Real
> SYS - Real
> USR - Real
> ... - Real
>
> I am trying to get information out for using in a graph. Unfortunately,
> we are logging several times a minute, so the amounts of data for any
> reasonable timeframe (say, one day) are just too large for any one graph
> to handle well.
>
> So, what I'd like to do is average the IOW (or whichever I'm graphing at
> the moment) over a timeframe. (Where I would probably scale the timeframe
> depending on the total length of time I'm pulling.) So, the result would
> contain one row per interval, with the IOW/whatever being the average
> value over the interval.
>
> Is there any way to do this in straight SQL? (Let's use one day (Date =
> '$date') and 10 minutes as examples: I'll probably be creating an
> interface to generate these ad-hock, over random timeframes.)

10 minutes is 600 seconds

SELECT AVG("IOW"),AVG("SYS"),AVG("USR"),MIN("Time"),"MachineName"
FROM "LoadTable"
WHERE "Date"='today'::date
GROUP BY FLOOR( EXTRACT(EPOCH FROM "Time") / 600 ),"MachineName";