sql - 查询以查找连续事件之间的时间差

Question

目标:返回一个数据集，显示相同类型的连续事件之间的时间差

表结构:

CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[JobID] [int] NOT NULL, --FK to Job Table
[LastRun] [datetime] NOT NULL,
[LastRunEnd] [datetime] NULL,
[LastRunResult] [int] NOT NULL, --FK to Result
) ON [PRIMARY]
--Event ID is PK

由于数据来自顺序事件，LastRun 将始终针对每个作业增加。
如果创建条目的进程在生成数据之前发生致命故障，则 LastRunEnd 可能为 null。
LastRun 总是大于所有之前的 LastRunEnd

我正在尝试编写一个 TSQL 查询，为每个条目显示 JobID、LastRun 和上一个条目的 LastRun。这将给我两者之间的差距，从而检测错过的运行。

到目前为止我最好的镜头:

select this.EventId as thisEvent,prev.Eventid as  prevEvent,
this.lastrun as thisRun,
prev.LastRun as prevRun,
datediff(hh,prev.LastRun,this.lastrun) as gap
from Event this
join (select 
     EventID, JobID,LastRun from Event ) prev on prev.jobid = 
     this.jobid and prev.EventID = (Select max(EventID) from Event 
                                    where LastRun < This.LastRun)
where this.LastRun > '2016-08-01' and job.jobid = 57

这似乎适用于它返回的行，但它返回的行太少。使用给定的 where 子句，有 15 个事件。我预计返回 14 行，但只有 3 行。返回的三个看起来像这样:

thisEvent   prevEvent   thisRun          prevRun          gap
----------- ----------- ---------------- ---------------- ----
5172        5239        2016-08-01 16:16 2016-05-31 15:45 1489
5174        5239        2016-08-02 15:45 2016-05-31 15:45 1512
5176        5239        2016-08-03 15:45 2016-05-31 15:45 1536

我期待这样的事情:

thisEvent   prevEvent   thisRun          prevRun          gap
----------- ----------- ---------------- ---------------- ----
5176        5174        2016-08-03 15:45 2016-08-02 15:45 24
5174        5172        2016-08-02 15:45 2016-08-01 16:16 23

很明显，所有行都选择了相同的先前事件。我也不知道为什么只返回 3 行

任何帮助将不胜感激...

Answer 1

您可以使用 lag解析窗函数:

select   jobid,
         thisevent,
         prevevent,
         thisrun,
         prevrun,
         datediff(hh, thisrun, prevrun) as gap
from     (select  jobid,
                  eventid thisevent, 
                  lastrun thisrun,
                  lag(eventid) over (partition by jobid order by lastrun) prevevent,
                  lag(lastrun) over (partition by jobid order by lastrun) prevrun
          from    event 
          where   lastrun > '2016-08-01'
              and jobid = 57
         ) base
order by jobid, 
         thisrun