如何编写KQL实现Azure Data Explorer中IoT传感器signals表的行转列？

问题根因 #

ADX的pivot()运算符默认会自动推导聚合规则和分组键，未显式指定的情况下会对数值型Value字段使用sum()聚合，同时将所有非旋转列、非值列作为分组键，一旦存在隐性字段干扰、或者Timestamp存在精度级的微小偏差，就会出现重复时间戳行，无匹配信号的分组会返回sum()的默认值0。

由于你确认同一Timestamp + TestId组合下每个信号仅存在一个值，不需要做聚合计算，使用any()聚合函数直接取唯一值即可解决问题，同时显式指定分组键避免逻辑异常。

正确查询语句 #

let testId = "cbb8bff1-ee9d-4ead-bbd6-c9c246d84fd3";
signals
| where TestId == testId 
| where SignalName in ("Signal1", "Signal2", "Signal3")
| evaluate pivot(SignalName, any(Value), Timestamp, TestId)
| order by Timestamp desc

改动说明 #

• 显式指定any(Value)作为聚合规则：直接取分组内的唯一值，不会触发求和计算，避免出现默认填充0的问题
• 显式指定Timestamp、TestId作为分组键：排除无关字段干扰，确保每一行对应唯一的时间戳+测试ID组合，不会出现重复行
• 用in()简化多值匹配逻辑，代码可读性更高

关于物化视图/更新策略/函数的选择 #

你可以根据使用场景选择方案：

• 仅按需查询指定TestId数据：直接使用上面的即席查询即可，也可以封装为用户定义函数简化调用，无额外存储开销。
  示例函数定义：

  .create-or-alter function GetTestSignals(testId:string)
  {
      signals
      | where TestId == testId 
      | where SignalName in ("Signal1", "Signal2", "Signal3")
      | evaluate pivot(SignalName, any(Value), Timestamp, TestId)
  }
  

  调用方式：GetTestSignals("目标TestId") | order by Timestamp desc
• 高频查询所有TestId的宽表数据、要求低延迟：可以搭配物化视图+更新策略预计算存储转换后的数据，预计算会占用额外的集群资源，查询频率不高的场景不推荐使用。

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