how to aggregate data over time | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

how to aggregate data over time

Ok, I have simple table of data consisting of a primary key, a datetime, and a measurement. The measurement is taken every minute 24/7. I want to take averages of the data over time, say 1 hour intervals. I wrote a query to do that: select date = cast (
CONVERT(CHAR(4),DATEPART(yyyy,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(mm,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(dd,snap_time)) + ‘ ‘ +
CONVERT(CHAR(2),DATEPART(hh,snap_time)) + ‘:’ +
+ ’00:00.000′
AS DATETIME),
avg(metric)
FROM AG_PRD_SLAVE
WHERE master_key = 11
GROUP BY
cast (
CONVERT(CHAR(4),DATEPART(yyyy,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(mm,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(dd,snap_time)) + ‘ ‘ +
CONVERT(CHAR(2),DATEPART(hh,snap_time)) + ‘:’ +
+ ’00:00.000′
AS DATETIME)
ORDER BY date;
Is there a better way to do the above? Also, what if I wanted to do a different time interval, say 10 minutes? For 10 minutes, I tried doing this: select date = cast (
CONVERT(CHAR(4),DATEPART(yyyy,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(mm,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(dd,snap_time)) + ‘ ‘ +
CONVERT(CHAR(2),DATEPART(hh,snap_time)) + ‘:’ +
CASE
WHEN DATEPART(mm,snap_time) = 0 THEN ’00’
WHEN DATEPART(mm,snap_time) BETWEEN 1 AND 9 THEN ’10’
WHEN DATEPART(mm,snap_time) BETWEEN 11 AND 19 THEN ’20’
WHEN DATEPART(mm,snap_time) BETWEEN 21 AND 29 THEN ’30’
WHEN DATEPART(mm,snap_time) BETWEEN 31 AND 39 THEN ’40’
WHEN DATEPART(mm,snap_time) BETWEEN 41 AND 49 THEN ’50’
WHEN DATEPART(mm,snap_time) BETWEEN 51 AND 59 THEN ’00’
END
+ ‘:00.000’
AS DATETIME),
avg(metric)
FROM AG_PRD_SLAVE
WHERE master_key = 11
GROUP BY
cast (
CONVERT(CHAR(4),DATEPART(yyyy,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(mm,snap_time)) + ‘-‘ +
CONVERT(CHAR(2),DATEPART(dd,snap_time)) + ‘ ‘ +
CONVERT(CHAR(2),DATEPART(hh,snap_time)) + ‘:’ +
CASE
WHEN DATEPART(mm,snap_time) = 0 THEN ’00’
WHEN DATEPART(mm,snap_time) BETWEEN 1 AND 9 THEN ’10’
WHEN DATEPART(mm,snap_time) BETWEEN 11 AND 19 THEN ’20’
WHEN DATEPART(mm,snap_time) BETWEEN 21 AND 29 THEN ’30’
WHEN DATEPART(mm,snap_time) BETWEEN 31 AND 39 THEN ’40’
WHEN DATEPART(mm,snap_time) BETWEEN 41 AND 49 THEN ’50’
WHEN DATEPART(mm,snap_time) BETWEEN 51 AND 59 THEN ’00’
END
+ ‘:00.000’
AS DATETIME)
ORDER BY date;
and got this: 2006-01-17 20:10:00.0008.5858585834503174
2006-01-17 21:10:00.0009.2974303603172306
2006-01-17 22:10:00.0004.0343113210466175
2006-01-17 23:10:00.0006.0254126191139221
2006-01-18 00:10:00.0003.147817839836252
2006-01-18 01:10:00.0005.9826927083603882
2006-01-18 02:10:00.0004.1671750585238136
2006-01-18 03:10:00.0003.2651182810465493
2006-01-18 04:10:00.0003.2124956309795381
2006-01-18 05:10:00.0003.9338898559411368
Thanks in advance for anyone’s help.
You can do something like this<br /><pre id="code"><font face="courier" size="2" id="code"><br />GROUP BY DATEADD(minute, DATEDIFF(minute,0,&lt; your_column &gt<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /> / &lt; some_value &gt;* &lt; some_value &gt;,0)<br /></font id="code"></pre id="code"><br />Where some_value can be replaced with your interval.<br /><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
]]>