Dynamic summarize without column name

Question

How can you construct a summarize statement dynamically based on a string array of columns ?

deviceTelemetry 
| summarize avg(column) by deviceId

EDIT

Here is a dataset, "keys" contains columns names I would like to use in the summary aggregation, "deviceTelemetry" the expected result (In this case, columns are hardcoded).

let deviceTelemetry = datatable (deviceId:guid, timestamp:datetime, value:dynamic)[
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 17:01:35.7750000', dynamic({ "level":  60}),
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 18:01:35.7750000', dynamic({ "level":  50}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 17:01:35.7750000', dynamic({ "level": 100, "flow": 350}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 18:01:35.7750000', dynamic({ "level":  90, "flow": 360}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 19:01:35.7750000', dynamic({ "level":  80, "flow": 370}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 20:01:35.7750000', dynamic({ "level":  70, "flow": 380}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  120}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 20:01:35.7750000', dynamic({ "pressure":  130}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  140}),
];
let keys = deviceTelemetry
| summarize make_bag(value)
| extend keys=bag_keys(bag_value)
| mv-expand keys
| distinct tostring(keys);
keys;
let flowColumnName = "flow";
let levelColumnName = "level";
let pressureColumnName = "pressure";
deviceTelemetry
| evaluate  bag_unpack(value)
| summarize
            level=avg(toreal(column_ifexists(levelColumnName, levelColumnName))), 
            flow=avg(toreal(column_ifexists(flowColumnName, flowColumnName))),
            pressure=avg(toreal(column_ifexists(pressureColumnName, pressureColumnName)))
            by timestamp=bin(timestamp , 1d) , deviceId

user_6336479 · Answer

pivot plugin

let deviceTelemetry = datatable (deviceId:guid, timestamp:datetime, value:dynamic)[
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 17:01:35.7750000', dynamic({ "level":  60}),
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 18:01:35.7750000', dynamic({ "level":  50}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 17:01:35.7750000', dynamic({ "level": 100, "flow": 350}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 18:01:35.7750000', dynamic({ "level":  90, "flow": 360}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 19:01:35.7750000', dynamic({ "level":  80, "flow": 370}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 20:01:35.7750000', dynamic({ "level":  70, "flow": 380}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  120}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 20:01:35.7750000', dynamic({ "pressure":  130}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  140}),
];
deviceTelemetry
| mv-expand kind=array value
| extend k = tostring(value[0]), v = toreal(value[1])
| extend timestamp_bin = bin(timestamp , 1d)
| evaluate pivot(k, avg(v),  timestamp_bin, deviceId)

timestamp_bin	deviceId	flow	level	pressure
2020-05-15T00:00:00Z	fddf1cec-16db-4461-9057-3d08e46b6bcf	NaN	55	NaN
2020-05-15T00:00:00Z	aaaaaaaa-fed4-c23b-422b-e85e0877c092	365	85	NaN
2020-05-15T00:00:00Z	cb04ccff-48bc-4108-9d16-7d7db9152895	NaN	NaN	130

Fiddle

Dynamic summarize without column name

1 Answers