CODEWITHSUNDEEP
azure-data-explorerkql
Anila Zafar
Anila Zafar

Reputation: 33

How to write a kusto query to group n number of consecutive rows based on value in a column

I have a dataset that has values as following

DataSet:

Col1 Col2
A 0
B 1
C 1
D 1
E 0
F 0
G 1
H 0
I 0
J 0
K 1

I want the result to be as follows:

Col1      Col2 
[A]        0
[B, C, D]  1
[E, F]     0
[G]        1
[H, I, J]  0
[K]        1

or Alternatively as:

Col1      Col2 
[B, C, D]  1
[G]        1
[K]        1

Is there any way to achieve this using kql?

I have looked into using prev function but it only works if there is fixed number of rows for grouping everytime. but here I need to group n rows based on a value in column, but the groups need to separate every time the same value is started again.

Upvotes: 3

Views: 4282

Answers (1)

Slavik N
Slavik N

Reputation: 5298

Here you go:

datatable(Col1:string,Col2:long) [
  "A", 0,
  "B", 1,
  "C", 1,
  "D", 1,
  "E", 0,
  "F", 0,
  "G", 1,
  "H", 0,
  "I", 0,
  "J", 0,
  "K", 1
]
| serialize 
| extend NewSession = (row_number() == 1) or (Col2 != prev(Col2))
| extend SessionId = row_cumsum(iff(NewSession, 1, 0))
| summarize Col1 = make_list(Col1) by Col2, SessionId
| project Col1, Col2

The output will be exactly what you wanted.

Note: I use serialize, but you can use order by as well - but you need at least one of them, so that the records will be ordered.

Upvotes: 2

Related Questions