CODEWITHSUNDEEP
azure-data-explorerkql
Venkat Yalla
Venkat Yalla

Reputation: 558

How to sum integer arrays across rows

How to achieve the equivalent of summarize sum(Trend) by id where Trend is array of integers?

Input:
——————————————————————————
Id    | ParentId | Trend 
——————————————————————————
C1-P1 | P1       | [1,2,3]
C2-P1 | P1       | [4,5,6]
C3-P1 | P1       | [1,1,1]
P1    |          |
C1-P2 | P2       | [4,5,6]
C2-P2 | P2       | [7,8,9]
P2    |          |
—————————————————————————-

Needed Output:
——————————————————————————
Id    | ParentId | Trend 
——————————————————————————
C1-P1 | P1       | [1,2,3]
C2-P1 | P1       | [4,5,6]
C3-P1 | P1       | [1,1,1]
P1    |          | [6,8,10] 
C1-P2 | P2       | [4,5,6]
C2-P2 | P2       | [7,8,9]
P2    |          | [11,13,15]
—————————————————————————-

Upvotes: 0

Views: 1070

Answers (1)

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

Please check if the query below solves your scenario: It uses mv-expand operator and 'with_itemindex' option to expand the values of the array.

let _data = datatable(Id:string, ParentId:string, Trend:dynamic)
[
'C1-P1','P1', dynamic([1,2,3]),
'C2-P1', 'P1',  dynamic([4,5,6]),
'C3-P','P1',dynamic([1,1,1]),
'P1','',dynamic([]),
'C1-P2','P2',dynamic([4,5,6]),
'C2-P2','P2',dynamic([7,8,9]),
'P2', '', dynamic([])
];
_data 
| mv-expand with_itemindex=x Trend to typeof(long)
| summarize sum(Trend) by  ParentId, x
| summarize Trend=make_list(sum_Trend) by ParentId
| union (_data | where isnotempty( ParentId))

Upvotes: 2

Related Questions