主页 / dba / 问题 / 115717
Accepted
Peter PitLock
Asked: 2015-09-22 22:03:58 +0800 CST

根据特定规则识别事件序列

  • 772

我需要帮助来识别 SQL Server 2008 R2 行中的一系列事件，请：

这是SQLFiddle的示例数据：

   ID   EventTime       TypeID             EventNr
    1   06:34:51        1
    2   06:35:51        3
    3   06:36:51        40
    4   06:37:51        10
    5   06:48:51        1
    6   06:49:51        2
    7   06:50:51        4
    8   06:51:51        40
    9   06:52:51        5
    10  06:53:51        10

我需要帮助来识别 Event Nrs - 我必须遵守的规则：

  • 事件以 TypeID 1 ( @StartTypeID) 开始。

  • 事件以 TypeID 10 ( @EndTypeID) 结束。

  • @StartTypeID事件在 之后和 之前具有不同的 TypeID @EndTypeID。

  • 而且EventNrs之间必须有5分钟以上。

这是上述示例的预期结果：

 ID EventTime   TypeID  Notes                                               EventNr
1   06:34:51    1       Starts with TypeID @StartID 1     (OK)                     1
2   06:35:51    3       Has different TypeID in sequence before @EndID reached(OK) 1
3   06:36:51    40                                                                 1
4   06:37:51    10      Ends with @EndID 10 + >5min before next Event(OK EventEnd) 1

5   06:48:51    1       Starts with TypeID @StartID 1                              2
6   06:49:51    2       Has different TypeID in sequence before @EndID reached     2
7   06:50:51    4                                                                  2
8   06:51:51    40                                                                 2
9   06:52:51    5       Has different TypeID in sequence before  @EndID reached    2
10  06:53:51    10      Ends with @EndID 10                                        2

用于创建数据样本的 SQL：

declare @FirstTypeID int = 1

declare @LastTypeID int = 10


create table #T (ID int identity , EventTime datetime, TypeID int)
insert into #T(EventTime,TypeID)
values
 (DATEADD(minute,-30,getdate()),1)
 ,(DATEADD(minute,-29,getdate()),3)
 ,(DATEADD(minute,-28,getdate()),40)
 ,(DATEADD(minute,-27,getdate()),10)  
 ,(DATEADD(minute,-16,getdate()),1)
 ,(DATEADD(minute,-15,getdate()),2)
 ,(DATEADD(minute,-14,getdate()),4)
 ,(DATEADD(minute,-13,getdate()),40)
 ,(DATEADD(minute,-12,getdate()),5) 
 ,(DATEADD(minute,-11,getdate()),10)    


select * from #T

drop table #T
sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    此查询给出正确的输出：

    With ord as (
    Select first, last 
        , EventNr = ROW_NUMBER() Over(order by first) 
    From (
        Select first = t1.EventTime, last = t2.EventTime
            , rn = ROW_NUMBER() Over(partition by t1.ID order by t2.eventTime) 
        From #T t1
        Inner Join #T t2 On t2.EventTime > t1.EventTime  and t2.TypeID = @LastTypeID
        Where t1.TypeID = @FirstTypeID
    ) ids
    Where rn = 1
)
Select t.ID, t.EventTime, t.TypeID, o.EventNr From #T t
Inner Join ord o On t.EventTime >= o.first and t.EventTime <= o.last
Order By o.EventNr, t.EventTime

    我没有使用 Column ID 进行排序，因为它似乎特定于您的样本。

    • 2

相关问题