如何在ADX中按时间戳聚合并将TagId转为新列展示聚合值？

原始数据结构：

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

执行以下Kusto查询按小时聚合数据：

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

返回结果格式：

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

希望合并相同时间戳的行，为每个TagId创建单独的列（如21_avg_Value、22_avg_Value、23_avg_Value），得到如下格式的结果：

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

可以使用Kusto的pivot运算符实现行转列，修改后的查询如下：

T
| where Timestamp > ago(30d)
| where TagId in (21, 22, 23)
| summarize avg_val = round(avg(todouble(Value)), 2) by bin(Timestamp, 1h), TagId
| pivot(TagId, any(avg_val))
| rename 
    "21" to "21_avg_Value",
    "22" to "22_avg_Value",
    "23" to "23_avg_Value"
| order by Timestamp asc

说明： #

1. 先按小时和TagId聚合计算平均值，命名为avg_val；
2. 使用pivot将TagId的不同值转为列，any()函数用于提取对应TagId的平均值（因为每个时间戳+TagId组合只有一个值，所以any()适用）；
3. 通过rename将列名修改为你需要的格式；
4. 最后按时间戳升序排序。

如果某些时间戳下某个TagId没有数据，对应的列会显示null，若需要填充默认值（比如0），可在pivot后添加类似| extend 21_avg_Value = coalesce(21_avg_Value, 0)的语句，替换对应的列名和默认值即可。

内容的提问来源于stack exchange，提问作者Ville