添加时间段内开/关记录的运行时分钟数

我有一个SQL数据库，从谷仓收集温度和传感器数据。

表定义为:

CREATE TABLE [dbo].[DataPoints]
(
[timestamp] [datetime] NOT NULL,
[pointname] [nvarchar](50) NOT NULL,
[pointvalue] [float] NOT NULL
)

传感器报告外部温度(度)、内部温度(度)和加热(开/关)。

当之前的读数发生变化时，传感器创建一个记录，因此每隔几分钟产生一次温度，一次记录加热开始，一次记录加热关闭，等等。

我感兴趣的是一夜之间用了多少分钟的热量，所以从昨天早上6点到今天早上6点的24小时就可以了。

这个查询:

SELECT * 
FROM [home_network].[dbo].[DataPoints]
WHERE (pointname = 'Heaters')
AND (timestamp BETWEEN '2022-12-18 06:00:00' AND '2022-12-19 06:00:00')
ORDER BY timestamp

返回这个数据:

2022-12-19 02:00:20   |  Heaters   |  1
2022-12-19 02:22:22   |  Heaters   |  0
2022-12-19 03:43:28   |  Heaters   |  1
2022-12-19 04:25:31   |  Heaters   |  0

最终结果应该是22分钟+ 42分钟= 64分钟的热量，但我看不出如何从单个查询中获得此结果。碰巧这个结果集有两个完整的热开/关循环，但情况并非总是如此。因此，如果第一个热记录为= 0，这意味着在上午6点，热已经打开，但开始时间不会在查询中显示。同样的道理也适用于最后一次高温记录是1，比如说在05:15，这意味着45分钟必须加到总数中。

是否有可能通过单个查询获得此分钟热时间结果?实际上，我不知道正确的方法，如果我必须运行几个查询也没关系。如果需要，我可以使用一个小应用程序来读取原始数据，并应用SQL之外的逻辑来获得总数。但是我更希望能够在SQL中做到这一点。

这不是一个完整的答案，但它应该可以帮助您开始。从帖子中的SQL，我假设你正在使用SQL Server。我已经格式化了代码以匹配。如果您想在自己的数据上进行测试，请将@input替换为上面的查询。(SELECT * FROM [home_network].[dbo]...)

--generate dummy table with sample output from question
declare @input as table(
[timestamp] [datetime] NOT NULL,
[pointname] [nvarchar](50) NOT NULL,
[pointvalue] [float] NOT NULL
)
insert into @input values
('2022-12-19 02:00:20','Heaters',1),
('2022-12-19 02:22:22','Heaters',0),
('2022-12-19 03:43:28','Heaters',1),
('2022-12-19 04:25:31','Heaters',0);
--Append a row number to the result
WITH A as (
SELECT *,
ROW_NUMBER() OVER(ORDER BY(SELECT 1)) as row_count
from @input)
--Self join the table using the row number as a guide
SELECT sum(datediff(MINUTE,startTimes.timestamp,endTimes.timestamp))
from A as startTimes
LEFT JOIN A as endTimes on startTimes.row_count=endTimes.row_count-1
--Only show periods of time where the heater is turned on at the start
WHERE startTimes.row_count%2=1

你的问题可以分为两个步骤:

过滤传感器类型和日期范围，同时还通过按时间顺序计算当前记录的时间戳与下一个记录的日期差来获得每条记录的时间跨度。
过滤ON状态的记录并汇总持续时间
(可选)转换为HH:MM:SS格式显示

这是我对这个问题的看法，以及我在每一步中所做的注释，所有这些都合并到一个查询中。

-- Step 3: Convert output to HH:MM:SS, this is just for show and can be reduced
SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SECOND, total_duration, 0), 108), 
1, 2, CAST(FLOOR(total_duration / 3600) AS VARCHAR(5)))
FROM (
-- Step 2: select records with status ON (1) and aggregate total duration in seconds 
SELECT sum(duration) as total_duration
FROM (
-- Step 1: Use LEAD to get next adjacent timestamp and calculate date difference (time span) between the current record and the next one in time order
SELECT TOP 100 PERCENT
DATEDIFF(SECOND, timestamp, LEAD(timestamp, 1, '2022-12-19 06:00:00') OVER (ORDER BY timestamp)) as duration,
pointvalue
FROM [dbo].[DataPoints]
-- filtered by sensor name and time range
WHERE pointname = 'Heaters'
AND (timestamp BETWEEN '2022-12-18 06:00:00' AND '2022-12-19 06:00:00')
ORDER BY timestamp ASC
) AS tmp
WHERE tmp.pointvalue = 1
) as tmp2

注:由于最后一条记录没有下一个相邻的时间戳，因此它将填充检查结束时间(在本例中为第二天的6AM)。

我真的不认为这可能在一个查询中实现。

选项1:

实现存储过程，您可以实现一些逻辑如何计算这些周期。

选项2:

添加新列(duration)，并在插入新记录时计算现在与前一个时间戳的差值和前一个记录的更新持续时间

相关内容

最新更新

热门标签：