Thread: SQL Question: Averages of intervals.
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.) Daniel T. Staal
On Fri, Jan 23, 2009 at 3:39 PM, Daniel Staal <DStaal@usa.net> wrote: > Is there any way to do this in straight SQL? Please provide: - DDL. - Sample data. - Expected output. Regards.
--As of January 24, 2009 11:16:31 AM -0500, Rodrigo E. De León Plicet is alleged to have said: > On Fri, Jan 23, 2009 at 3:39 PM, Daniel Staal <DStaal@usa.net> wrote: >> Is there any way to do this in straight SQL? > > Please provide: > > - DDL. > - Sample data. > - Expected output. > > Regards. --As for the rest, it is mine. If I can't get one of the other suggestions to work when I get back to the office, I will. (Sorry about the weekend wait: There was a misconfig in my email which meant I couldn't see replies until I got home. Fixed.) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
On 2009-01-23, Daniel Staal <DStaal@usa.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";
On Sat, January 24, 2009 8:05 pm, Jasen Betts wrote:
> 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";
This worked perfectly. Thank you.
Daniel T. Staal