Summarize by timestamp and id to new columns in ADX

Question

My data looks like this:

TagId   Timestamp                 Value
23  2022-06-19T10:25:51.7229267Z    90
22  2022-06-19T10:25:51.7229267Z    90
21  2022-06-19T10:25:51.7229267Z    90
21  2022-06-19T10:30:51.7229267Z    90
22  2022-06-19T10:30:51.7229267Z    90

I want to aggregate it by hour, so I query like this:

T
| where Timestamp > ago(30d)
| where TagId in (21, 22, 23)
| summarize round(avg(todouble(Value)), 2) by bin(Timestamp, 1h), TagId
| order by Timestamp asc

The returned data looks like this:

Timestamp          TagId   avg_Value
2022-06-20T12:00:00Z    21  59
2022-06-20T12:00:00Z    23  59
2022-06-20T12:00:00Z    22  59
2022-06-20T13:00:00Z    23  58.08
2022-06-20T13:00:00Z    22  58.08
2022-06-20T13:00:00Z    21  58.17

Is it possible to combine "same" timestamps and instead create new columns for each TagId, so the returned data would be instead something like this:

Timestamp            21_avg_Value 22_avg_Value 23_avg_Value
2022-06-20T12:00:00Z    59            59           59
2022-06-20T13:00:00Z    59            59           59
2022-06-20T14:00:00Z    59            59           59
2022-06-20T15:00:00Z    58.08         58.08        58.08
2022-06-20T16:00:00Z    58.08         58.08        58.08
2022-06-20T17:00:00Z    58.17         58.17        58.17

user_6336479 · Answer

pivot plugin

let T = materialize(range i from 1 to 1000000 step 1 | extend Timestamp = ago(5h*rand()), TagId = 20 + toint(rand(5)), Value = rand(100));  
T
| where Timestamp > ago(30d)
| where TagId in (21, 22, 23)
| summarize round(avg(todouble(Value)), 2) by bin(Timestamp, 1h), TagId
| evaluate pivot(TagId, take_any(avg_Value), Timestamp)
| order by Timestamp asc

Timestamp	21	22	23
2022-07-20T08:00:00Z	49.93	49.05	49.67
2022-07-20T09:00:00Z	49.42	49.71	49.53
2022-07-20T10:00:00Z	49.4	49.41	49.39
2022-07-20T11:00:00Z	49.53	49.53	49.4
2022-07-20T12:00:00Z	49.68	49.41	49.56
2022-07-20T13:00:00Z	49.84	49.81	49.63

Fiddle

In principle there is no need for summarize before pivot, however pivot does not accept round around the aggregation function, yielding the following error:

evaluate pivot(): the following error(s) occurred while evaluating the output schema: evaluate pivot(): aggregation function 'round' is not supported. Supported functions are (count,sum,dcount,avg,variance,stdev,max,min,any,take_any,make_bag,make_list,make_set)

let T = materialize(range i from 1 to 1000000 step 1 | extend Timestamp = ago(5h*rand()), TagId = 20 + toint(rand(5)), Value = rand(100));  
T
| where Timestamp > ago(30d)
| where TagId in (21, 22, 23)
| extend Timestamp = bin(Timestamp, 1h)
| evaluate pivot(TagId, avg(todouble(Value)), Timestamp)
| order by Timestamp asc

Timestamp	21	22	23
2022-07-20T08:00:00Z	49.639317570437491	49.688689968741123	49.418424208224586
2022-07-20T09:00:00Z	49.49681320887283	49.509350879412878	49.443463251113222
2022-07-20T10:00:00Z	49.64722369517461	49.51051396034164	49.373391203994082
2022-07-20T11:00:00Z	49.175818954738688	49.452813225782286	49.633952188451367
2022-07-20T12:00:00Z	49.575321744736776	49.568760191728025	49.4777564329407
2022-07-20T13:00:00Z	49.372085830272567	49.69027496271368	49.533668459200371

Fiddle

Summarize by timestamp and id to new columns in ADX

1 Answers