SQL 5分钟内任意值，平均值

最新推荐文章于 2024-08-10 10:41:28 发布

jsboy123

最新推荐文章于 2024-08-10 10:41:28 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/jsboy123/article/details/53286808

SQL 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了一种使用SQL从数据库中选择特定时间间隔内的记录的方法，并提供了计算这些记录平均值的技术。通过对时间戳进行数学运算来精确地筛选和聚合数据，从而实现每五分钟周期的数据收集与分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

任意值:

Select RecID, [RamCode],EquipMentID,[RecPressure],[ColTime] FROM

(SELECT RecID,[RamCode],EquipMentID,[RecPressure],[ColTime],
datediff(mi,[ColTime],DATEADD(mi,-5,CASE WHEN DATEPART(mi,GETDATE())%5=0 THEN DATEADD(mi,1,GETDATE()) ELSE GETDATE()END)) AS interval

FROM [KS_DataRecord] ) AS t WHERE t.interval%5 = 0

平均值：

-- This convert the period to date-time format
SELECT
-- note the 5, the "minute", and the starting point to convert the
-- period back to original time
DATEADD(minute, AP.FiveMinutesPeriod * 5, '2010-01-01T00:00:00') AS Period,
AP.AvgValue
FROM
-- this groups by the period and gets the average
(SELECT
P.FiveMinutesPeriod,
AVG(P.RecPressure) AS AvgValue
FROM
-- This calculates the period (five minutes in this instance)
(SELECT
-- note the division by 5 and the "minute" to build the 5 minute periods
-- the '2010-01-01T00:00:00' is the starting point for the periods
datediff(minute, '2010-01-01T00:00:00', T.ColTime)/5 AS FiveMinutesPeriod,
T.RecPressure
FROM [KS_DataBase].[dbo].[KS_DataRecord] T) AS P
GROUP BY P.FiveMinutesPeriod) AP order by Period