CODEWITHSUNDEEP
azure-data-explorerkql
nrofis
nrofis

Reputation: 9766

KQL calculate the timespan of sequence

I have a table with two columns Ts that represents datetime and Index. I want to calculate the total timespan of continuous sequence of indexes.

To do that, I used scan to calculate timespan:

let t = datatable(Ts: datetime, Index:int)
[
   datetime(2022-12-1), 1,
   datetime(2022-12-5), 2,
   datetime(2022-12-6), 3,
   datetime(2022-12-2), 10,
   datetime(2022-12-3), 11,
   datetime(2022-12-3), 12,
   datetime(2022-12-1), 18,
   datetime(2022-12-1), 19,
];
t
| sort by Index asc
| scan declare (startTime: datetime, index:int, totalTime: timespan) with 
(
    step inSession: true => startTime = iff(isnull(inSession.startTime), Ts, inSession.startTime), index = Index;
    step endSession: Index != inSession.index + 1 => totalTime = Ts - inSession.startTime;
)

But I get:

Ts Index startTime index totalTime
2022-12-01T00:00:00Z 1 2022-12-01T00:00:00Z 1
2022-12-05T00:00:00Z 2 2022-12-01T00:00:00Z 2
2022-12-06T00:00:00Z 3 2022-12-01T00:00:00Z 3
2022-12-02T00:00:00Z 10 1.00:00:00
2022-12-02T00:00:00Z 10 2022-12-02T00:00:00Z 10
2022-12-03T00:00:00Z 11 2.00:00:00
2022-12-03T00:00:00Z 11 2022-12-02T00:00:00Z 11
2022-12-03T00:00:00Z 12 2.00:00:00
2022-12-03T00:00:00Z 12 2022-12-02T00:00:00Z 12
2022-12-01T00:00:00Z 18 -1.00:00:00
2022-12-01T00:00:00Z 18 2022-12-01T00:00:00Z 18
2022-12-01T00:00:00Z 19 00:00:00
2022-12-01T00:00:00Z 19 2022-12-01T00:00:00Z 19

Instead (the desired result):

Ts Index startTime index totalTime
2022-12-01T00:00:00Z 1 2022-12-01T00:00:00Z 1
2022-12-05T00:00:00Z 2 2022-12-01T00:00:00Z 2
2022-12-06T00:00:00Z 3 2022-12-01T00:00:00Z 3
2022-12-02T00:00:00Z 10 5.00:00:00
2022-12-02T00:00:00Z 10 2022-12-02T00:00:00Z 10
2022-12-03T00:00:00Z 11 2022-12-02T00:00:00Z 11
2022-12-03T00:00:00Z 12 2022-12-02T00:00:00Z 12
2022-12-01T00:00:00Z 18 1.00:00:00
2022-12-01T00:00:00Z 18 2022-12-01T00:00:00Z 18
2022-12-01T00:00:00Z 19 2022-12-01T00:00:00Z 19
2022-12-01T00:00:00Z 19 00:00:00

What is wrong with my query? How can I get the desired result?

Upvotes: 1

Views: 353

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

let t = datatable(Ts: datetime, Index:int)
[
   datetime(2022-12-1), 1,
   datetime(2022-12-5), 2,
   datetime(2022-12-6), 3,
   datetime(2022-12-2), 10,
   datetime(2022-12-3), 11,
   datetime(2022-12-3), 12,
   datetime(2022-12-1), 18,
   datetime(2022-12-1), 19,
];
t
| sort by Index asc
| summarize rows = count(), min(Ts), max(Ts), min(Index), max(Index) by group_id = Index - row_number()
| extend totalTime = max_Ts - min_Ts
| project-away group_id
| order by min_Index asc
rows min_Ts max_Ts min_Index max_Index totalTime
3 2022-12-01T00:00:00Z 2022-12-06T00:00:00Z 1 3 5.00:00:00
3 2022-12-02T00:00:00Z 2022-12-03T00:00:00Z 10 12 1.00:00:00
2 2022-12-01T00:00:00Z 2022-12-01T00:00:00Z 18 19 00:00:00

Fiddle

Upvotes: 1

Related Questions