如何获取按名称分组的时差持续时间总计以及值何时以某种方式更改?

Question

我正在寻找值最初为 1 和现在为 0 的行之间的时间值差异总和，按名称分组。

单个名称的示例数据，但实际数据中有许多不同的名称。

+--------+---------------+---------------------+--------+
| RowNum |     Name      |      Timestamp      | Value  |
+--------+---------------+---------------------+--------+
|      1 | D1_HS_308_ALM | 2020-02-10 11:55:00 | 0      |
|      2 | D1_HS_308_ALM | 2020-02-10 11:51:00 | 0      |
|      3 | D1_HS_308_ALM | 2020-02-10 11:49:00 | NULL   |
|      4 | D1_HS_308_ALM | 2020-02-10 11:46:00 | 1      |
|      5 | D1_HS_308_ALM | 2020-02-10 08:02:00 | 0      |
|      6 | D1_HS_308_ALM | 2020-02-10 08:02:00 | NULL   |
|      7 | D1_HS_308_ALM | 2020-02-10 08:02:00 | 0      |
|      8 | D1_HS_308_ALM | 2020-02-10 07:56:00 | 1      |
|      9 | D1_HS_308_ALM | 2020-02-10 07:51:00 | 1      |
|     10 | D1_HS_308_ALM | 2020-02-10 07:50:00 | 0      |
+--------+---------------+---------------------+--------+

对于第 9 行(>7 行转换(，此数据应返回11 分钟，对于第 4 行>2 转换，此数据应返回 5 分钟;对于此名称，总共返回 16 分钟。

当不能保证下一行具有正确的值更改时，我不确定如何完成此操作 - 有时它会在几行或许多行之后。

Answer 1

DECLARE @myTable TABLE
(
RowNum INT,
Name VARCHAR(20),
[Timestamp] DATETIME,
Value INT
);
INSERT INTO @myTable
(
RowNum,
Name,
[Timestamp],
Value
)
VALUES
(1, 'D1_HS_308_ALM', '2020-02-10 11:55:00', 0),
(2, 'D1_HS_308_ALM', '2020-02-10 11:51:00', 0),
(3, 'D1_HS_308_ALM', '2020-02-10 11:49:00', NULL),
(4, 'D1_HS_308_ALM', '2020-02-10 11:46:00', 1),
(5, 'D1_HS_308_ALM', '2020-02-10 08:02:00', 0),
(6, 'D1_HS_308_ALM', '2020-02-10 08:02:00', NULL),
(7, 'D1_HS_308_ALM', '2020-02-10 08:02:00', 0),
(8, 'D1_HS_308_ALM', '2020-02-10 07:56:00', 1),
(9, 'D1_HS_308_ALM', '2020-02-10 07:51:00', 1),
(10, 'D1_HS_308_ALM', '2020-02-10 07:50:00', 0);
SELECT *,
DATEDIFF(MINUTE, tStart, tEnd) AS duration
FROM
(
SELECT t1.Name,
MIN(t1.RowNum) AS rStart,
MIN(t1.Timestamp) AS tStart,
t.rNo AS rEnd,
t.tEnd
FROM @myTable t1
OUTER APPLY
(
SELECT TOP (1)
t2.RowNum,
t2.Timestamp
FROM @myTable t2
WHERE t1.Name = t2.Name
AND t2.Timestamp > t1.Timestamp
AND t2.Value = 0
ORDER BY t2.Timestamp
) t(rNo, tEnd)
WHERE t1.Value = 1
GROUP BY t1.Name,
t.rNo,
t.tEnd
) tmp;

更新：

SELECT Name,
Sum(DATEDIFF(MINUTE, tStart, tEnd)) AS duration
FROM
(
SELECT t1.Name,
MIN(t1.Timestamp) AS tStart,
t.tEnd
FROM @myTable t1
OUTER APPLY
(
SELECT TOP (1)
t2.Timestamp
FROM @myTable t2
WHERE t1.Name = t2.Name
AND t2.Timestamp > t1.Timestamp
AND t2.Value = 0
ORDER BY t2.Timestamp
) t(tEnd)
WHERE t1.Value = 1
GROUP BY t1.Name,
t.tEnd
) tmp
group by name;

Answer 2

您可以使用窗口函数：

• 有条件地min()获取值0处的下一个时间戳。
• lag()以查看以前的值。

然后过滤到从 0 到 1 的过渡，并进行一些日期算术：

select t.*,
datediff(seconds, timestamp, next_ts_0) as duration
from (select t.*,
min(case when value = 0 then timestamp end) over (partition by name order by timestamp desc) as next_ts_0,
lag(value) over (partition by name order by timestamp) as prev_value
from t.*
) t
where (prev_value = 0 or prev_value is null) and value = 1

Answer 3

只是根据您的需要修改了 Cetin 的选择查询：

DECLARE @myTable TABLE
(
RowNum INT,
Name VARCHAR(20),
[Timestamp] DATETIME,
Value INT
);
INSERT INTO @myTable
(
RowNum,
Name,
[Timestamp],
Value
)
VALUES
(1, 'D1_HS_308_ALM', '2020-02-10 11:55:00', 0),
(2, 'D1_HS_308_ALM', '2020-02-10 11:51:00', 0),
(3, 'D1_HS_308_ALM', '2020-02-10 11:49:00', NULL),
(4, 'D1_HS_308_ALM', '2020-02-10 11:46:00', 1),
(5, 'D1_HS_308_ALM', '2020-02-10 08:02:00', 0),
(6, 'D1_HS_308_ALM', '2020-02-10 08:02:00', NULL),
(7, 'D1_HS_308_ALM', '2020-02-10 08:02:00', 0),
(8, 'D1_HS_308_ALM', '2020-02-10 07:56:00', 1),
(9, 'D1_HS_308_ALM', '2020-02-10 07:51:00', 1),
(10, 'D1_HS_308_ALM', '2020-02-10 07:50:00', 0);

SELECT tmp.Name,
SUM(DATEDIFF(MINUTE, tStart, tEnd)) AS duration
FROM
(
SELECT t1.Name,
--MIN(t1.RowNum) AS rStart,
MIN(t1.Timestamp) AS tStart,
--t.rNo AS rEnd,
t.tEnd
FROM @myTable t1
OUTER APPLY
(
SELECT TOP (1)
--t2.RowNum,
t2.Timestamp
FROM @myTable t2
WHERE t1.Name = t2.Name
AND t2.Timestamp > t1.Timestamp
AND t2.Value = 0
ORDER BY t2.Timestamp
--) t(rNo, tEnd)
) t(tEnd)
WHERE t1.Value = 1
GROUP BY t1.Name,
--t.rNo,
t.tEnd
) tmp
GROUP BY tmp.Name;