Kusto: summarize consecutive segments of the same value per batch

Here’s a reliable and efficient Kusto query that solves the "gaps and islands" problem to group consecutive rows with the same team per batch, without merging non-consecutive segments:

kusto

YourTable
| order by batch_number asc, datetime asc
| serialize
| extend 
    prev_batch = prev(batch_number),
    prev_team = prev(team)
| extend 
    is_new_segment = 
        isnull(prev_team) 
        or (batch_number != prev_batch) 
        or (team != prev_team)
| extend 
    segment_id = row_cumsum(iff(is_new_segment, 1, 0))
| summarize 
    start_time = min(datetime),
    end_time = max(datetime),
    team = take_any(team)
    by batch_number, segment_id
| project batch_number, team, start_time, end_time
| order by batch_number asc, start_time asc

Explanation:

• order by + serialize ensures the data is properly ordered and allows safe use of prev().
• We detect the start of a new segment whenever the batch changes or the team is different from the previous row.
• row_cumsum creates a unique segment ID for each consecutive group.
• summarize aggregates the min/max datetime per segment, keeping batch_number in the by clause to group correctly per batch.
• Final project and order by give the clean expected output.

This produces exactly the desired result for your sample data, preserving separate segments for the same team when they are not consecutive (e.g., team A appears twice in batch 001).

.............................................................................................

https://jjewellery.vn/blogs/news/jjewellery-cua-hang-nhan-cuoi-uy-tin-tai-quan-1-tp-hcm