使用Kusto查询湿度高于前一日的记录ID

需求 #

筛选出数据表中湿度值高于前一日对应记录的所有行的id字段值。

给定数据表 #

datatable(id:int, recorddate:datetime, humidity:real)[
    1, datetime(2022-11-01), 10.0,
    2, datetime(2022-11-02), 12.0,
    3, datetime(2022-11-03), 15.0,
    4, datetime(2022-11-04), 18.0,
    5, datetime(2022-11-05), 20.0,
    6, datetime(2022-11-06), 22.0,
    7, datetime(2022-11-07), 19.0,
    8, datetime(2022-11-08), 16.0,
    9, datetime(2022-11-09), 14.0,
    10, datetime(2022-11-10), 11.0,
];

Kusto查询语句 #

datatable(id:int, recorddate:datetime, humidity:real)[
    1, datetime(2022-11-01), 10.0,
    2, datetime(2022-11-02), 12.0,
    3, datetime(2022-11-03), 15.0,
    4, datetime(2022-11-04), 18.0,
    5, datetime(2022-11-05), 20.0,
    6, datetime(2022-11-06), 22.0,
    7, datetime(2022-11-07), 19.0,
    8, datetime(2022-11-08), 16.0,
    9, datetime(2022-11-09), 14.0,
    10, datetime(2022-11-10), 11.0,
]
| order by recorddate asc
| extend prev_humidity = prev(humidity)
| where humidity > prev_humidity
| project id

查询结果 #

id
2
3
4
5
6

以上结果中的id对应的湿度值均高于前一行记录。

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