Killcycle Killcycle - 3 months ago 23
SQL Question

(SQL) SELECT the average value with interval

Sample data:

enter image description here

I want to select the average value every 5 minutes, for example.

The result should be a list like this:

3
8


The functions for this should be:
AVG(VALUE), DATEDIFF(datepart, startdate, enddate) with datepart=minute and/or DATEADD (datepart, number, date)


I didn't find an interval function (Transact-SQL)

Update

Example of what I really want to do here:

sample

I want the average values between points to plot a trendline I have to change the "cluster" means the resolution to calculate it. In this example my cluster is 1. 2 would mean, that I'll get the average from the first 3 value points. I hope i did explained it well!

Answer

If you want your intervals to start on the hour then this is a pretty simple solution.

Sample Data

IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
CREATE TABLE #SampleData (ID int identity(1,1), Date datetime, Value int)
INSERT INTO #SampleData (Date, Value)
VALUES
 ('2016-01-01 08:31:00.000',1.00000)
,('2016-01-01 08:32:00.000',2.00000)
,('2016-01-01 08:33:00.000',3.00000)
,('2016-01-01 08:34:00.000',4.00000)
,('2016-01-01 08:35:00.000',5.00000)
,('2016-01-01 08:36:00.000',6.00000)
,('2016-01-01 08:37:00.000',7.00000)
,('2016-01-01 08:38:00.000',8.00000)
,('2016-01-01 08:39:00.000',9.00000)
,('2016-01-01 08:40:00.000',10.00000)

Query

SELECT
dateadd(minute, datediff(minute,0,sd.Date) / 5 * 5, 0) Interval
,AVG(sd.Value) AvgResult
FROM #SampleData sd
GROUP BY dateadd(minute, datediff(minute,0,sd.Date) / 5 * 5, 0)

Result

Interval                    AvgResult
2016-01-01 08:30:00.000     2
2016-01-01 08:35:00.000     7
2016-01-01 08:40:00.000     10

If you want the periods to show differently then you could do this;

SELECT
dateadd(minute, (datediff(minute,0,sd.Date) / 5 * 5) + 3, 0) Interval
,AVG(sd.Value) AvgResult
FROM #SampleData sd
GROUP BY dateadd(minute, (datediff(minute,0,sd.Date) / 5 * 5) + 3, 0)

to give this result;

Interval                    AvgResult
2016-01-01 08:33:00.000     2
2016-01-01 08:38:00.000     7
2016-01-01 08:43:00.000     10