(SQL)用区间选择平均值

如果你想让你的间歇时间从一小时开始,那么这是一个非常简单的解决方案。

示例数据

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)

查询

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)

后果

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

如果你想让周期显示不同,那么你可以这样做;

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)

给出该结果;

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

根据您的意见,您可以使用此查询将间隔中实例的实际平均值四舍五入到最接近的分钟;

SELECT
a.AVGDate
,AVG(sd.Value) AvgResult
FROM #SampleData sd
JOIN 
(
    SELECT
        DATEADD(minute, DATEDIFF(minute,0,DATEADD(s,30,CAST(AVG(CAST(sd.Date AS Float)) AS Datetime))),0) AVGDate
        ,dateadd(minute, datediff(minute,0,sd.Date) / 5 * 5, 0) Interval
    FROM #SampleData sd
    GROUP BY dateadd(minute, datediff(minute,0,sd.Date) / 5 * 5, 0) 
) a
ON dateadd(minute, datediff(minute,0,sd.Date) / 5 * 5, 0) = a.Interval
GROUP BY a.AVGDate

后果

AVGDate                     AvgResult
2016-01-01 08:33:00.000     2
2016-01-01 08:37:00.000     7
2016-01-01 08:40:00.000     10

计算从时间0开始的差值(以分钟为单位),然后除以5进行汇总:

select min(date), avg(value)
from t
group by (datediff(minute, 0, date) - 1) / 5; -- SQL Server does integer division