Application Insights中KQL转换为double后order by排序失效问题

问题描述 #

在Application Insights中编写KQL查询时，按RequestCharge属性降序排序的操作失效，以下两个datatable场景可复现该问题：

场景一：RequestCharge为string类型，转换为double后排序 #

datatable(operation_Id: string, timestamp: datetime, name: string, RequestCharge: string, duration: int)
[
    "id_1", datetime(2023-09-14T01:00:00), "operation_1", "45.50", 300,
    "id_2", datetime(2023-09-14T02:00:00), "operation_2", "0.0", 150,
    "id_3", datetime(2023-09-14T03:00:00), "operation_3", "78.23", 550,
    "id_4", datetime(2023-09-14T04:00:00), "operation_4", "32.10", 400,
    "id_5", datetime(2023-09-14T05:00:00), "operation_5", "10.25", 350,
    "id_6", datetime(2023-09-14T06:00:00), "operation_6", "90.85", 450
]
| project operation_Id, timestamp, name, RequestCharge = todouble(trim(" ", RequestCharge)), duration
| where isnotnull(RequestCharge)
| order by RequestCharge desc

执行后结果未按RequestCharge降序排列。

场景二：RequestCharge直接定义为double类型 #

datatable(operation_Id: string, timestamp: datetime, name: string, RequestCharge: double, duration: int)
[
    "id_1", datetime(2023-09-14T01:00:00), "operation_1", 45.50, 300,
    "id_2", datetime(2023-09-14T02:00:00), "operation_2", 0.0, 150,
    "id_3", datetime(2023-09-14T03:00:00), "operation_3", 78.23, 550,
    "id_4", datetime(2023-09-14T04:00:00), "operation_4", 32.10, 400,
    "id_5", datetime(2023-09-14T05:00:00), "operation_5", 10.25, 350,
    "id_6", datetime(2023-09-14T06:00:00), "operation_6", 90.85, 450
]
| project operation_Id, timestamp, name, RequestCharge, duration
| where isnotnull(RequestCharge)
| order by RequestCharge desc

执行后排序结果仍不符合预期。

原因分析 #

查询逻辑看似正确，但排序失效通常由以下几点导致：

• Application Insights界面默认排序干扰：查询结果界面会自动对timetamp列排序，覆盖KQL语句中指定的排序规则。
• 数据转换不彻底（场景一）：isnotnull无法过滤转换失败产生的nan或inf值，这类异常值会打乱排序顺序。
• 分页显示问题：结果集过大时，界面分页可能未严格遵循排序后的顺序展示。

解决方案 #

1. 明确排序优先级，覆盖界面默认行为 #

在order by子句中明确指定排序规则，若需保留时间维度，将其设为次要排序字段：

// 场景一修复版
datatable(operation_Id: string, timestamp: datetime, name: string, RequestCharge: string, duration: int)
[
    "id_1", datetime(2023-09-14T01:00:00), "operation_1", "45.50", 300,
    "id_2", datetime(2023-09-14T02:00:00), "operation_2", "0.0", 150,
    "id_3", datetime(2023-09-14T03:00:00), "operation_3", "78.23", 550,
    "id_4", datetime(2023-09-14T04:00:00), "operation_4", "32.10", 400,
    "id_5", datetime(2023-09-14T05:00:00), "operation_5", "10.25", 350,
    "id_6", datetime(2023-09-14T06:00:00), "operation_6", "90.85", 450
]
| project operation_Id, timestamp, name, RequestCharge = todouble(trim(" ", RequestCharge)), duration
| where isfinite(RequestCharge) // 过滤nan/inf等异常值，比isnotnull更严格
| order by RequestCharge desc, timestamp asc // 明确主、次排序字段

2. 验证数据转换结果（场景一专属） #

添加临时列查看转换前后的值，确认无异常：

datatable(operation_Id: string, timestamp: datetime, name: string, RequestCharge: string, duration: int)
[
    "id_1", datetime(2023-09-14T01:00:00), "operation_1", "45.50", 300,
    "id_2", datetime(2023-09-14T02:00:00), "operation_2", "0.0", 150,
    "id_3", datetime(2023-09-14T03:00:00), "operation_3", "78.23", 550,
    "id_4", datetime(2023-09-14T04:00:00), "operation_4", "32.10", 400,
    "id_5", datetime(2023-09-14T05:00:00), "operation_5", "10.25", 350,
    "id_6", datetime(2023-09-14T06:00:00), "operation_6", "90.85", 450
]
| extend RawCharge = RequestCharge, ConvertedCharge = todouble(trim(" ", RequestCharge))
| where isfinite(ConvertedCharge)
| project operation_Id, timestamp, name, RequestCharge = ConvertedCharge, duration
| order by RequestCharge desc

3. 取消界面自动排序 #

在Application Insights查询结果界面，点击timetamp列的排序按钮，取消默认排序，确保仅应用KQL语句中指定的规则。

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