数秒切换间隔SQL Server [英] Count seconds on switch interval SQL Server

问题描述

我有一个这样的表：

Value TimeStamp
1   2016-04-01 00:01:09.000
0   2016-04-01 00:01:09.000
0   2016-04-01 00:01:37.000
1   2016-04-01 00:01:37.000
1   2016-04-01 00:04:52.000
1   2016-04-01 00:09:58.000
1   2016-04-01 00:15:05.000
1   2016-04-01 00:20:11.000
1   2016-04-01 00:24:49.000
1   2016-04-01 00:29:55.000
1   2016-04-01 00:31:19.000
0   2016-04-01 00:31:19.000
0   2016-04-01 00:31:46.000
1   2016-04-01 00:31:46.000
1   2016-04-01 00:35:01.000
1   2016-04-01 00:40:07.000
1   2016-04-01 00:44:46.000
1   2016-04-01 00:49:52.000
1   2016-04-01 00:54:58.000
1   2016-04-01 01:00:04.000
1   2016-04-01 01:01:28.000
0   2016-04-01 01:01:28.000
0   2016-04-01 01:05:10.000
0   2016-04-01 01:09:49.000

我想计算每天值1（打开）的秒数，这是交易；当timeStamp重复时，这意味着切换值从0变为1或反之亦然，我已经有了许多类似的过程：

And i want to count the seconds where value is 1 (switch ON) PER DAY, here is the deal; When the timeStamp repeats it means that there was a change from 0 to 1 or viceversa in the switch value, I already had many aproches like:

Q1 AS (SELECT ROW_NUMBER() OVER (ORDER BY TimeStamp) AS id,
Value, Timestamp
FROM Q2
GROUP BY idVBox, sensorType, sensorSubtype, timeStamp
HAVING COUNT(TimeStamp) > 1)

Then:

SELECT A.Value, DATEDIFF(SECOND,A.TimeStamp,B.TimeStamp)
FROM Q1 AS A
INNER JOIN Q1 AS B
ON B.ID = A.ID + 1
AND B.ID%2 = 0

然后按和求和，但是这里的问题是我不知道过去一天的值是1还是0，并且开关可以更改它的状态很快，永远不会获得其实际状态的实际值。还有其他想法吗？

Then Group by and Sum, but here the problem is that i don't know if the value comes in 1 or 0 from the past day, and the switch can change it's state quick and never get an actual value of it's actual state. Any other idea?

推荐答案

您要执行的操作是，在开始时将虚拟传感器状态开关添加到您的设备中开始计算的前一天。

What you want to do, is add a dummy sensor state switch into your set at the beginning of the day before you start your calculation.

添加的额外记录是：

0, '2016-04-01 00:00:00'
1, '2016-04-01 00:00:00' -- This is conditional on the first record in your set having a value of 1

总体查询如下

注意：为了确定什么

The overall query is below
Note: in order to determine what record is actually the first in sequence I used "ID" column.

;WITH Q0 AS(
    -- Inserts a new record ( 0, '2016-04-01 00:00:00' ) to the beginning of the day
    SELECT TOP 1 0 AS Value, CONVERT( DATETIME, CONVERT( DATE, LogDate )) AS LogDate
    FROM #SwitchLog
    UNION ALL
    -- Inserts a new record ( 1, '2016-04-01 00:00:00' ) to the beginning of the day when the first record has Value = 1
    SELECT Value, CONVERT( DATETIME, CONVERT( DATE, LogDate )) AS LogDate
    FROM
        ( SELECT TOP 1 ID, Value, LogDate
        FROM #SwitchLog
        ORDER BY LogDate ASC, ID ASC ) AS DummyRecord --<-- NOTE: the use of a table ID column
    WHERE Value = 1
    UNION ALL
    SELECT Value, LogDate
    FROM #SwitchLog
)
,
Q1 AS (SELECT ROW_NUMBER() OVER (ORDER BY LogDate) AS id,
SUM( Value ) AS Value, LogDate
FROM Q0
GROUP BY LogDate
HAVING COUNT(LogDate) > 1)

SELECT A.Value, DATEDIFF(SECOND,A.LogDate,B.LogDate) AS Total
FROM Q1 AS A
INNER JOIN Q1 AS B
ON B.ID = A.ID + 1 AND B.ID%2 = 0

输出：

Value       Total
----------- -----------
1           69
1           1782
1           1782

相同的方法应该用于在时段/日期（（day + 1）00:00:00）的末尾插入虚拟记录，以适应当天末传感器值为1的情况。

Same approach should be used to insert dummy record(s) at the end of the period/day ((day + 1) 00:00:00) to cater for situations where sensor value is 1 at the end of the day.

这篇关于数秒切换间隔SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！