T-SQL Time Averaging

Question

I have a table in SQL Server that stores statistics for a piece of hardware, rows in the table represent data for a given second. It contains for example, these columns:

timestamp (DateTime)
value (int)

What I want to do is select the data from the table for a given date/time range but return it in such a way that it averages for a given time period (such as 1 minute, 5 minute, 1 day etc) between the given range. So for an hour I'd have 60 rows of 1 minute averages.

Where do I start with this? Anybody any points or ideas?

Answer 1

You can do a select and group by on a DatePart of your timestamp.

For Example:

SELECT
 DATEPART(hh, [timestamp]),
 DATEPART(mi, [timestamp]),
 AVG([value])
FROM
 YourTable
WHERE
 [timestamp] BETWEEN '2009-01-01 00:00:00.000' AND '2009-02-01 00:00:00.000'
GROUP BY
 DATEPART(hh, [timestamp]),
 DATEPART(mi, [timestamp])

EDIT: For your more complex time spans like 5 mins, you can do a divide on the datepart as follows.

DATEPART(mi, [timestamp]) / 5 * 5

Answer 2

WITH    cal(m) AS
        (
        SELECT  1
        UNION ALL
        SELECT  m + 1
        FROM    cal
        WHERE   m < 60
        )
SELECT  DATEADD(minute, m, @start), AVG(value)
FROM    cal
LEFT JOIN
        timestamp
ON      timestamp > DATEADD(minute, m, @start)
        AND timestamp <= DATEADD(minute, m + 1, @start)
GROUP BY
        m

This will select averages for all minutes inside a given hour, even those for which there are no records.

Answer 3

In addition to the post by Robin Day, you can group by 5 minute intervals like:

GROUP BY
    DATEPART(hh, [timestamp]),
    DATEPART(mi, [timestamp]) / 5

And if you'd like to span several days, group on dy, for day of year:

GROUP BY
    DATEPART(dy, [timestamp]),
    DATEPART(hh, [timestamp]),
    DATEPART(mi, [timestamp]) / 5

Answer 4

If you are going to have a high read/write ratio for this data you might want to consider an indexed view. I have used this approach all over the place to aggregate by buckets of time. I just got around to blogging the example, here is the code:

create table timeSeries (
    timeSeriesId int identity primary key clustered
    ,updateDate datetime not null
    ,payload float not null
)

insert timeSeries values ('2009-06-16 12:00:00', rand())
insert timeSeries values ('2009-06-16 12:00:59', rand())
insert timeSeries values ('2009-06-16 12:01:00', rand())
insert timeSeries values ('2009-06-16 12:59:00', rand())
insert timeSeries values ('2009-06-16 01:00:00', rand())
insert timeSeries values ('2009-06-16 1:30:00', rand())
insert timeSeries values ('2009-06-16 23:59:00', rand())
insert timeSeries values ('2009-06-17 00:01:00', rand())
insert timeSeries values ('2009-06-17 00:01:30', rand())


create view timeSeriesByMinute_IV with schemabinding as
select
    dayBucket = datediff(day, 0, updateDate)
    ,minuteBucket = datediff(minute, 0, (updateDate - datediff(day, 0, updateDate)))
    ,payloadSum = sum(payLoad)
    ,numRows = count_big(*) 
from dbo.timeSeries
group by 
    datediff(day, 0, updateDate)
    ,datediff(minute, 0, (updateDate - datediff(day, 0, updateDate)))
go

create unique clustered index CU_timeSeriesByMinute_IV on timeSeriesByMinute_IV (dayBucket, minuteBucket)
go


create view timeSeriesByMinute as
select
    dayBucket
    ,minuteBucket
    ,payloadSum
    ,numRows
    ,payloadAvg = payloadSum / numRows
from dbo.timeSeriesByMinute_IV with (noexpand)
go

declare @timeLookup datetime, @dayBucket int, @minuteBucket int
select 
    @timeLookup = '2009-06-16 12:00:00'
    ,@dayBucket = datediff(day, 0, @timeLookup)
    ,@minuteBucket = datediff(minute, 0, (@timeLookup - datediff(day, 0, @timeLookup)))

select * from timeSeriesByMinute where dayBucket = @dayBucket and minuteBucket = @minuteBucket

You can see the example lookup at the end of the code block. Clearly you can define ranges to query across instead of just seeking to a particular dayBucket/minuteBucket pair.

Answer 5

I could not get Quassnoi's answer to work without the following changes:

WITH    cal(m) AS
    (
    SELECT  1
    UNION ALL
    SELECT  m + 1
    FROM    cal
    WHERE   m < 60
    )
SELECT  DATEADD(minute, m, @start) m, AVG(value)
FROM    cal
LEFT JOIN
    YourTable
ON      timestamp > DATEADD(minute, m, @start)
    AND timestamp <= DATEADD(minute, m + 1, @start)
GROUP BY
    m