CODEWITHSUNDEEP
kqlazure-data-explorer
Mckens Jeoy
Mckens Jeoy

Reputation: 3

KQL query to extend new column with other row values

I have a table containing two columns, ID and Value, I hope to extend a third column Sum, in which each row's value should be the sum of values of rows with ids in range [currentRowId, currentRowId+3].

Example:

ID Value
3 1
4 4
5 5
1 6
2 7
0 3
7 3
2 2

Expected result:

ID Value Sum
3 1 13(sum of values of rows with id in range [3, 3+3], 1+4+5+3 =13)
4 4 15(sum of values of rows with id in range [4, 4+3], 4+5+3+3 = 15)
5 5
1 6
2 7
6 3
7 3
2 2

The Sum value of first row is 13, because first row ID is 3, what I want is the sum of values from rows whose id is in [3, 3+3] range.

I am totally new to KQL, is this possible to do in KQL?

Upvotes: 0

Views: 406

Answers (1)

RithwikBojja
RithwikBojja

Reputation: 11383

Below KQL query worked to get your expected result:

let rith_Table = datatable(ID: int, Value: int)
[
    3, 1,
    4, 4,
    5, 5,
    1, 6,
    2, 7,
    6, 3,
    7, 3,
    2, 2
];
rith_Table
| extend rith_range = range(ID, ID + 3, 1)  
| mv-expand rith_range to typeof(int)  
| join kind=inner (rith_Table | project ID_2 = ID, R_Value_2 = Value) on $left.rith_range == $right.ID_2
| summarize Rith_Sum = sum(R_Value_2) by ID, Value
| project ID, Value, Rith_Sum

Output:

enter image description here

Fiddle.

Upvotes: 0

Related Questions