azure-data-explorer - Kusto 查询以获取非空的最新列值(对于每一列)

Question

用这个例子来解释我想要完成的事情的最简单的方法是:

let data = datatable(id:int, b:string, c:string, dateTime:datetime)
[
    1,"","c one","2021-03-03",
    2,"b one","","2021-03-04",
    3,"","c two","2021-03-05"
];

如您所见，id 和 date 值存在于每一行中。 b 和 c 列的值是可选的。目标是在一行中获取每一列的最新值，当该值不为空时。结果应如下所示:

3, "b 一", "c 二", "2021-03-05"

在实际场景中有更多的列，我想知道是否有可能以一种简单的方式做到这一点，而无需编写太多查询。

Answer 1

假设存在您定义的数据顺序 - 您可以使用 arg_max() 聚合: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/arg-max-aggfunction

let data = datatable(id:int, b:string, c:string, dateTime:datetime)
[
    1,"","c one","2021-03-03",
    2,"b one","","2021-03-04",
    3,"","c two","2021-03-05"
];
['data']
| serialize n = row_number()
| summarize arg_max(iif(isnotnull(id), n, -1), id),
            arg_max(iif(isnotempty(b), n, -1), b),
            arg_max(iif(isnotempty(c), n, -1), c),
            arg_max(iif(isnotnull(dateTime), n, -1), dateTime)
| project-away max_*