在ADX中，如何在特定的时间窗口中对数据进行扁平化以过滤空值

Question

我有来自单个设备的数据进入ADX，如下所示：

+-----------------------------+----------+-----------+-------------+
| dateTime                    | latitude | longitude | temperature |
+-----------------------------+----------+-----------+-------------+
| 2020-07-17T20:55:00.824313Z | 47.5783  | -78.1692  |             |
+-----------------------------+----------+-----------+-------------+
| 2020-07-17T20:55:00.824311Z |          |           | 60          |
+-----------------------------+----------+-----------+-------------+
| 2020-07-17T20:54:01.000258Z | 47.5653  | -78.2692  |             |
+-----------------------------+----------+-----------+-------------+
| 2020-07-17T20:53:00.877956Z |          |           | 62          |
+-----------------------------+----------+-----------+-------------+

来自此源的每个其他更新都包含lat/long或设备的温度。

如果我想在给定的时间窗口内获得设备完整状态的快照，我该如何压平这些数据？例如，如果我想要最新的设备状态，我想返回一行，其中包含：

+-----------------------------+----------+-----------+-------------+
| dateTime                    | latitude | longitude | temperature |
+-----------------------------+----------+-----------+-------------+
| 2020-07-17T20:55:00.824313Z | 47.5783  | -78.1692  |      60     |
+-----------------------------+----------+-----------+-------------+

Answer 1

一个选项是"分割"；将表格分为2个部分(一个带纬度，一个带温度(，然后join两部分：

let T = datatable(id:string, dateTime:datetime, latitude:double, longitude:double, temperature:double)
[ 
"a", datetime(2020-07-17T20:55:00.824313Z), 47.5783,      double(-78.1692), double(null),
"a", datetime(2020-07-17T20:55:00.824311Z), double(null), double(null),     60,
"a", datetime(2020-07-17T20:54:01.000258Z), 47.5653,      double(-78.2692), double(null),
"a", datetime(2020-07-17T20:53:00.877956Z), double(null), double(null),     62
];
T
| where isnotnull(temperature)
| summarize arg_max(dateTime, temperature) by id
| join (
T
| where isnotnull(latitude)
| summarize arg_max(dateTime, latitude, longitude) by id
) on id
| project id, dateTime, latitude, longitude, temperature