查询以查找连续事件之间的时间差 [英] Query to find the time difference between successive events

问题描述

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

Goal: Return a dataset showing time differences between successive events of the same type

表格结构:

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

Since the data comes from sequential events, LastRun will always increase for each job.
LastRunEnd could be null if the process that creates the entry fails fatally before generating the data.
LastRun will always be greater than all previous LastRunEnd

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

I'm trying to write a TSQL query that shows for each entry, the JobID, the LastRun and the LastRun of the previous entry. This will give me the gap between the two, and will thus detect missed runs.

我目前最好的一次投篮:

My best shot so far:

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 行.返回的三个看起来像这样:

This seems to work for the rows it returns, however it returns too few rows. Using the where clause given, there are 15 events. I expected 14 rows returned, but there were only 3. The three that were returned looked like this:

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 行

Clearly, all rows are picking up the same previous event. I'm also at a loss as to why only 3 rows are returned

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

Any help would be appreciated...

推荐答案

你也可以使用cte的帮助..

You can use the help of cte also..

1. 创建示例数据.

1. creating sample data .

CREATE TABLE #Event
([EventID] [int] NOT NULL,
 [JobID] [int] NOT NULL, --FK to Job Table
 [LastRun] [datetime] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO #Event 
VALUES (5172,1,'2016-08-01 16:16') 
  ,(5174,1,'2016-08-02 15:45') 
  ,(5176,1,'2016-08-03 15:45')
  ,(5239,1,'2016-05-31 15:45') 

使用以下查询获得预期结果

use the below query for the expeted result

With cte_1
     as (select  e.jobid,
              e.eventid thisevent, 
              e.lastrun thisrun,
              lag(e.eventid) over (partition by e.jobid order by e.lastrun) prevevent,
              lag(e.lastrun) over (partition by e.jobid order by e.lastrun) prevrun
      from    #event e
      where   e.lastrun > '2016-08-01')

    select   jobid,
             thisevent,
             prevevent,
             thisrun,
             prevrun,
             ABS(datediff(hh, thisrun, prevrun)) as gap
     FROM cte_1 
     ORDER BY jobid, thisrun

这篇关于查询以查找连续事件之间的时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！