CODEWITHSUNDEEP
sqlsql-server
Connor John
Connor John

Reputation: 433

Is there a way that I can group by records that have a "cluster" of a time difference?

For example, I have a set of log records and I want to group records together if they are less than 1 minute apart. If there is a gap of more than 1 minute between the log records in the sequence, then I want to have that be in its own separate group.

Example:

Time RecordType
00:00:01 A
00:00:02 A
00:00:03 B
00:01:02 A
00:02:05 A
00:02:06 B

Then I want to create something like:

Group # Total Count A Count B Count
1 4 3 1
2 2 1 1

Upvotes: 1

Views: 111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270311

Yes. Use lag() to determine the first record in each group. Then do a cumulative sum to assign the groups. And finally aggregate:

select min(time), max(time), count(*),
       sum(case when recordtype = 'A' then 1 else 0 end) as num_a,
       sum(case when recordtype = 'B' then 1 else 0 end) as num_b
from (select t.*,
             sum(case when prev_time > dateadd(minute, -1, time) then 0 else 1 end) over (order by time) as grp
      from (select t.*,
                   lag(time) over (order by time) as prev_time
            from t
           ) t
     ) t
group by grp;

Upvotes: 2

Related Questions