时间戳

首页 > 解决方案 > 返回结果的平均值

sql - 返回结果的平均值

问题描述

我们有一个系统，每小时可以存储来自各种传感器的 1000 个读数。这些值被发送到 .net 图表模块中。它有效，但是当您查看几天的数据时，基本上有成千上万的结果被输入图表，并且处理它们的速度很慢。作为一个快速修复，我将它设置为将所有结果分组并平均到一个小时桶中，方法是将日期列分解为小时（去掉分钟和秒）。这工作得更快，但我希望它变得更好。这是当前（庞大的）查询：

SELECT Value, CAST(CAST(yr AS char(4)) + '/' + CAST(mth AS char(2)) + '/' + CAST(dte AS char(2)) + ' ' + CAST(hr AS char(2)) + ':00:00' AS datetime) AS Date FROM (SELECT AVG(Value) AS Value, DATEPART(year, Date) AS yr, DATEPART(month, Date) AS mth, DATEPART(day, Date) AS dte, DATEPART(hour, Date) AS hr FROM SensorReadings WHERE (SensorID='15') AND (Date >= DATEADD(hour, - 336, getutcdate())) GROUP BY DATEPART(year, Date), DATEPART(month, Date), DATEPART(day, Date), DATEPART(hour, Date)) AS derivedtbl_1 ORDER BY date

这可以返回几千个结果。让图表模块可能只有 300 像素宽，尝试绘制基本上会重叠的 1000 个点似乎仍然没有效率。

我的问题是：有没有办法告诉 SQL 只返回 300 个结果并让它平均 Value 和 Date 列？粗略地类似于“仅选择 300 avg(value) 作为 Value, ave(date) as Date from SensorReadings order by Date”

谢谢！

标签： sql.netsql-server

解决方案

这将起作用 - 它按读数数量将范围分成偶数段：

DECLARE @Partitions INT = 300;

WITH AllValues AS
(  SELECT Value,Date,RowNum = ROW_NUMBER() over(ORDER BY Date asc)
   FROM SensorReadings WHERE (SensorID='15')
)
SELECT AverageValue = AVG(Value),
       AverageTime = DATEADD(second,AVG(CAST(datediff(second,'1/1/2000',Date) as BIGINT)),'1/1/2000')
FROM AllValues
GROUP BY RowNum / @Partitions
ORDER BY RowNum / @Partitions

请注意，您不能取日期时间的平均值。但是您可以取自给定时间以来的平均秒数。

推荐阅读