KQL summarize by continues data

Question

I have a table that represents events in time windows (ordered by start time):

Row	Event	StartTime	EndTime
1	A	0	1
2	B	0.9	2
3	C	10	15
4	A	16	17
5	C	17	18

I want to summarize all the windows in a way so if the StartTime of the current row is not bigger than 1.5 + the EndTime of the previous row, it should be considered as the same window, and list all the events there.

The expected output:

Row	StartTime	EndTime	Events
1	0	2	[A, B]
2	10	18	[C, A]

Since the start time of B is smaller than 1+1.5 (so rows 1 and 2 are combined) but the start time of the first C (10) is bigger than 2+1.5 so it should be considered as a new row, and rows 3, 4, and 5 should be combined as well.

How can I do that with KQL in Azure Data Explorer?

Answer 1

Use the below code to achieve your requirement.

datatable(Row:int, Event:string, StartTime:real, EndTime:real)
[
    1, 'A', 0, 1,
    2, 'B', 0.9, 2,
    3, 'C', 10, 15,
    4, 'A', 16, 17,
    5, 'C', 17, 18,
]
| order by Row asc
| extend com1 = iif((StartTime-prev(EndTime)) <= 1.5, prev(Row),Row)
| extend com2=iif(com1==prev(Row),prev(com1),com1)
| project-away com1
| summarize Row=row_number(),StartTime = min(StartTime), EndTime = max(EndTime), Events = make_set(Event) by com2
| project-away com2

My Result: