SQL Create sequence of rows according to time difference

Question

I'm trying to do the following thing: I have table with users activities in system, and I want to create a sequence of activities according to the time of each activity on each object and when the time between each activity is up to 10 seconds, and to give each sequence an unique ID.

for example to following table :

  userID   ObjectID        Timestamp             
 ======== ========== ===================== 

       1         52   2016-05-01 19:10:01        
       1         52   2016-05-01 19:10:05        
       1         52   2016-05-01 19:10:07        
       1         52   2016-05-01 19:12:01        
       1         52   2016-05-01 19:12:04        
       2         54   2016-05-01 19:11:09        
       2         54   2016-05-01 19:11:19        
 ======== ========== ===================== == ==

on this table expected output is:

  userID   ObjectID          Timestamp     seq seqID           
 ======== ========== ===================== 

       1         52   2016-05-01 19:10:01   1  1
       1         52   2016-05-01 19:10:05   2  1  
       1         52   2016-05-01 19:10:07   3  1   
       1         52   2016-05-01 19:12:01   1  2   
       1         52   2016-05-01 19:12:04   2  2   
       2         54   2016-05-01 19:11:09   1  3   
       2         54   2016-05-01 19:11:19   2  3   
 ======== ========== ===================== == ==

I tried do it using row_number() and dense_rank() functions but I didn't succeed to do it with the 10 second different condition.

There's quite a lot of examples if you search with gaps and islands. Your tags are also confusing, is this about SQL Server or MySQL — James Z, Mar 03 '17 at 18:15
Is your 10-second grouping relative to the first activity? eg. an activity performed on `2016-05-01 19:10:10` would still appear on seqID 1? — Josh Part, Mar 03 '17 at 18:30
No,the activity is relative to last activity that happened before, e.g 2016-05-01 19:10:10 would appear in seqID 1 because it has 3 seconds diff from 2016-05-01 19:10:07 — nlan, Mar 03 '17 at 18:44
Can Objects from two different userID's be in the same sequence ID? For ex: If `UserID: 1` has another `Timestamp` of `2016-05-01 19:11:05`, would it get a sequenceID 3? — Crazy Cucumber, Mar 03 '17 at 18:51

Nolan Shang · Answer 1 · 2017-03-04T00:10:20.713

1. Sort by in same minute

DECLARE @test TABLE(UserID INT,ObjectID INT,[Timestamp] DATETIME)
INSERT INTO @test
SELECT 1,52,CONVERT(DATETIME,'2016-05-01 19:10:01') UNION ALL   
SELECT 1,52,'2016-05-01 19:10:05' UNION ALL   
SELECT 1,52,'2016-05-01 19:10:07' UNION ALL   
SELECT 1,52,'2016-05-01 19:12:01' UNION ALL   
SELECT 1,52,'2016-05-01 19:12:04' UNION ALL   
SELECT 2,54,'2016-05-01 19:11:09' UNION ALL   
SELECT 2,54,'2016-05-01 19:11:19'

SELECT *
     , ROW_NUMBER()OVER(PARTITION BY DATEDIFF(MINUTE,0,Timestamp) ORDER BY [Timestamp]) AS seq   
     , DENSE_RANK()OVER(ORDER BY DATEDIFF(MINUTE,0,Timestamp)) as seqID
FROM @test

UserID      ObjectID    Timestamp               seq                  seqID
----------- ----------- ----------------------- -------------------- --------------------
1           52          2016-05-01 19:10:01.000 1                    1
1           52          2016-05-01 19:10:05.000 2                    1
1           52          2016-05-01 19:10:07.000 3                    1
2           54          2016-05-01 19:11:09.000 1                    2
2           54          2016-05-01 19:11:19.000 2                    2
1           52          2016-05-01 19:12:01.000 1                    3
1           52          2016-05-01 19:12:04.000 2                    3

2. I think the following is matched with your description. @test same as above.

;with div AS (
       SELECT t.UserID,t.ObjectID,t.Timestamp          
             ,ROW_NUMBER()OVER(ORDER BY t.UserID,t.Timestamp) AS SeqID 
             ,LEAD(t.[Timestamp])OVER(PARTITION BY t.UserID ORDER BY t.Timestamp) AS NextTime
             ,t.DiffSencond
       FROM (
          SELECT *,LEAD([Timestamp])OVER(PARTITION BY UserID ORDER BY Timestamp) AS NextTime
                ,DATEDIFF(SECOND,[Timestamp],LEAD([Timestamp])OVER(PARTITION BY UserID ORDER BY Timestamp)) AS DiffSencond
        FROM @test
     ) AS t
     WHERE t.DiffSencond IS NULL OR t.DiffSencond>10

    )

select *,ROW_NUMBER()OVER(PARTITION BY d.SeqID ORDER BY t.[Timestamp]) AS seq
from @test as t
outer apply (select top 1 SeqID from div where div.UserID=t.UserID and datediff(second,t.Timestamp,div.Timestamp)>=0 order by SeqID) d

    UserID  ObjectID    Timestamp   SeqID   seq
1   1   52  01.05.2016 19:10:01 1   1
2   1   52  01.05.2016 19:10:05 1   2
3   1   52  01.05.2016 19:10:07 1   3
4   1   52  01.05.2016 19:12:01 2   1
5   1   52  01.05.2016 19:12:04 2   2
6   2   54  01.05.2016 19:11:09 3   1
7   2   54  01.05.2016 19:11:19 3   2

Crazy Cucumber · Answer 2 · 2017-03-03T19:39:53.177

Select *
Into   #Temp
From   ATable

Declare 
    @LoopCounter int = 1
    ,@TimeStamp1 datetime
    ,@TimeStamp2 datetime
    ,@Userid1 int
    ,@ObjectID1 int
    ,@Userid2 int
    ,@ObjectID2 int
    ,@Seq int
    ,@SeqID int


While @LoopCounter <= (Select Count(*) From #Temp)
Begin
    Select Top 1 
        @TimeStamp1 = TimeStamp 
        ,@Userid1 = UserId
        ,@ObjectID1 = ObjectID
        ,@Seq = Seq
        ,@SeqID = SeqID
    From #Temp
    Delete TOP 1 from #Temp
    Select Top 1 
        @TimeStamp2 = TimeStamp 
        ,@Userid2 = UserId
        ,@ObjectID2 = ObjectID

    from #Temp
    If(
        (@TimeStamp2 - @TimeStamp1) < 10
        and @Userid2 = @Userid1
        and @ObjectID2 = @ObjectID1
       )
    Begin
        Update 
            TableName
        Set 
            Seq = @Seq + 1
            ,SeqID = @SeqID
        Where 
            ObjectID = @ObjectID2
            and TimeStamp = TimeStamp2
    End

    Else
    Begin
        Update 
            TableName
        Set
            Seq = 1
            ,SeqID = @SeqID + 1
        Where
            ObjectID = @ObjectID2
            and TimeStamp = @TimeStamp2
    End

    @LoopCounter = @LoopCounter + 1

End

I think this should do it. Let me know if I did any mistakes in the code.

SQL Create sequence of rows according to time difference

2 Answers2