基于ID查找行间时间间隔的SQL
基于ID查找行间时间间隔的SQL
提问于 2017-04-12 12:04:26
我在SQL (HeartbeatHistory)中有下表
Timestamp | Comment | Id
------------------------注释可以包含OK或ERR。
Id是具有该注释的事物的Id。
我希望能够查询该表并找到任何给定id处于错误状态的持续时间。
Timestamp | Comment | Id
------------------------
12:00:00 | OK | 1
11:59:00 | ERR | 2
11:58:00 | OK | 4
11:57:00 | OK | 3
11:45:00 | ERR | 4
11:20:00 | OK | 2
11:00:00 | ERR | 3
11:30:00 | OK | 5
11:20:00 | ERR | 1
11:10:00 | OK | 1
11:00:00 | ERR | 1
10:30:00 | ERR | 5所以在上表中，如果我询问11:00到13:00，我想看看。
ErrorStart | ErrorEnd | Id
--------------------------
11:00:00 | 11:10:00 | 1
11:20:00 | 12:00:00 | 1
11:59:00 | 12:00:00 | 2
11:00:00 | 11:57:00 | 3
11:45:00 | 11:58:00 | 4
11:00:00 | 11:30:00 | 5(请注意5在查询日期之前启动错误！！)
这个是可能的吗？此外，Id可能在查询期间多次更改状态。
到目前为止，我有这样的功能，它适用于单个Id，但我需要使它适用于多个Id。
declare @startDate datetime = @from;
declare @endDate datetime = @to;
declare @kpiId = 1;
select Foo.RowCreatedTimestamp, Foo.Comment, Foo.NextTimeStamp, Foo.NextComment, Foo.HeartBeatId, Foo.NextHeartBeatId
from (
select RowCreatedTimestamp, Comment,
lag(RowCreatedTimestamp, 1, 0) over (order by RowCreatedTimestamp desc) as NextTimeStamp,
lag(Comment, 1, 0) over (order by RowCreatedTimestamp desc) as NextComment,
HeartBeatId
from dbo.tblHeartbeatHistory
where RowCreatedTimestamp >= @startDate and RowCreatedTimestamp <= @endDate
and HeartbeatId in
(
select HeartbeatId
from dbo.tblKpiHeartBeats
where KpiId = @kpiId
)
) as Foo
where Foo.Comment like '%set to ERR%'
order by Foo.RowCreatedTimestamp desc;因此，如果select HeartbeatId from dbo.tblKpiHeartBeats返回单个Id，则此操作有效。一旦他们是多个身份证，它就不会：
为了避免混淆：
带有时间戳、注释和Id的表是HeartbeatHistory。
我的SQL中引用的另一个表是dbo.tblKpiHeartBeats。
这张桌子看起来像：
Kpi | HeartbeatId
-----------------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5所以我想要Kpi =1的所有错误间隔，它会返回HeartbeatId 1,2,3,4和5的错误间隔。
进一步说明。在进入OK之前，数据可能在一行中有多个错误。
它可能只是查询期间的所有错误或全部确定。
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-04-12 12:42:27
您可以添加第二个CTE Id，您希望完全连接、错误和确定行(下面的代码仅用于OK行)
WIRH History AS (
SELECT
FROM HeartbeatHistory
WHERE Timestamp BETWEEN @DateStart AND @DateEnd
), Errors AS(
SELECT Id, MIN(Timestamp) AS ErrorStart
FROM History
WHERE Comment = 'ERR'
GROUP BY Id
)
SELECT
ErrorStart = E.ErrorStart ,
ErrorEnd = O.Timestamp,
Id = O.Id
FROM History O
LEFT JOIN Errors E ON E.Id = O.Id
WHERE O.Comment = 'OK'编辑:您可以将prevOK timespan (或PK)列添加到表(可能是计算持久的)链接到最后一个好行。它将用作报告中的行Id。
试试这个索引：
CREATE INDEX IDX_EXAMPLE ON HeartbeatHistory (Timestamp, Id, prevOK, Comment)
WIRH History AS (
SELECT
FROM HeartbeatHistory
WHERE Timestamp BETWEEN @DateStart AND @DateEnd
)
SELECT
ErrorStart = E.ErrorStart ,
ErrorEnd = O.Timestamp,
Id = O.Id
FROM History O
OUTER APPLY (
SELECT MIN(Timestamp) AS ErrorStart
FROM History E
WHERE E.Id = O.ID AND E.prevOK = O.prevOK
)
WHERE O.Comment = 'OK'Stack Overflow用户
发布于 2017-04-12 12:15:25
最简单的方法是使用lead()。如果我假设ERR不会在一行中发生两次(就像在示例数据中那样)：
select (case when timestamp >= '11:00:00' then timestamp else '11:00:00' end) as errorStart,
(case when next_timestamp <= '13:00:00' then next_timestamp else '13:00:00') as errorEnd,
id
from (select t.*,
lead(timestamp) over (partition by id order by timestamp) as next_timestamp
from t
) t
where comment = 'ERR' and
(timestamp <= '13:00:00' and
(next_timestamp >= '11:00:00' or next_timestamp is null)
);Stack Overflow用户
发布于 2017-04-12 13:16:30
试试这个：
DECLARE @table TABLE (Timestmp TIME(1), Comment NVARCHAR(5), Id INT) --your table
INSERT INTO @table VALUES
('12:00:00','OK ','1'),('11:59:00','ERR','2'),('11:58:00','OK ','4'),('11:57:00','OK ','3'),
('11:45:00','ERR','4'),('11:20:00','OK ','2'),('11:00:00','ERR','3'),('11:30:00','OK ','5'),
('11:20:00','ERR','1'),('11:10:00','OK ','1'),('11:00:00','ERR','1'),('10:30:00','ERR','5')
DECLARE @ROWER TABLE (id INT IDENTITY(1,1), Timestmp TIME(1))
INSERT INTO @ROWER SELECT Timestmp FROM @table WHERE Comment='OK' ORDER BY Timestmp
DECLARE @TIME TIME(1) = '11:00:00' --your condition
SELECT DISTINCT CASE WHEN A.Timestmp >=@TIME THEN A.Timestmp ELSE @TIME END ErrorStart,
CASE WHEN B.Timestmp > A.Timestmp THEN B.Timestmp ELSE '' END ErrorEnd,
A.Id FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id,Timestmp) rowid,* FROM @table WHERE Comment = 'ERR'
) A LEFT JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY id,Timestmp) rowid,* FROM @table WHERE Comment = 'OK'
) B ON A.rowid = B.rowid
LEFT JOIN ( SELECT A.id,A.Timestmp t1,B.Timestmp t2 FROM @ROWER A
LEFT JOIN (SELECT id-1 id, Timestmp FROM @ROWER) B ON A.id=B.id
) C ON A.Timestmp BETWEEN C.t1 AND C.t2 ORDER BY A.Id希望能帮上忙。:)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43369028
复制相关文章
相似问题
腾讯云开发者
