Início / dba / Perguntas / 115717
Accepted
Peter PitLock
Asked: 2015-09-22 22:03:58 +0800 CST

Identifique a sequência de eventos com base em certas regras

  • 772

Preciso de ajuda para identificar uma sequência de eventos nas linhas do SQL Server 2008 R2, por favor:

Estes são os dados de amostra com 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

Preciso de ajuda para identificar os Nrs do Evento, por favor - Regras que devo seguir:

  • Um evento começa com TypeID 1 ( @StartTypeID).

  • Um evento termina com TypeID 10 ( @EndTypeID).

  • Um evento tem um TypeID diferente depois @StartTypeIDe antes @EndTypeID.

  • E deve haver mais de 5 minutos entre EventNrs.

Este é o resultado esperado para o exemplo acima:

 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 para criar a amostra de dados:

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 respostas

  1. Best Answer

    Esta consulta fornece a saída correta:

    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

    Não usei o ID da coluna para fazer o pedido porque parece ser específico para sua amostra.

    • 2

relate perguntas