Kusto异常检测代码报错求助：计算分销商坏苹果占比遇语义错误

错误原因 #

触发语义错误的核心是多个语法与逻辑问题叠加：

• 数据表列名不匹配：原datatable的第一列存储的是分销商名称，但列名设为table_name，后续代码错误引用了不存在的distributor1、distributor2作为分组列。
• 聚合字段笔误：代码中sum(total_5g_drops)是错误输入，原数据表对应的字段是total_bad_apples。
• 分组逻辑错误：summarize和make-series需按分销商名称动态分组，而非硬编码两个分销商名称，否则会因分组列不存在导致聚合逻辑失效，最终引发make-series的语义报错。

修正后的代码 #

datatable(distributor: string, date: datetime, total_apples: int, total_bad_apples: int)
[
    ["distributor1", datetime(2023-06-01), 200, 10],
    ["distributor1", datetime(2023-06-02), 300, 20],
    ["distributor1", datetime(2023-06-03), 400, 30],
    ["distributor1", datetime(2023-06-04), 500, 40],
    ["distributor1", datetime(2023-06-05), 600, 50],
    ["distributor1", datetime(2023-06-06), 700, 60],
    ["distributor2", datetime(2023-06-01), 100, 5],
    ["distributor2", datetime(2023-06-02), 200, 10],
    ["distributor2", datetime(2023-06-03), 150, 8],
    ["distributor2", datetime(2023-06-04), 250, 12],
    ["distributor2", datetime(2023-06-05), 180, 9],
    ["distributor2", datetime(2023-06-06), 220, 11]
]
| let start_date = now() - 70d;
let end_date = now();
where date between (start_date .. end_date)
| summarize all_apples = sum(total_apples), bad_apples = sum(total_bad_apples) by date, distributor
| where all_apples >= 100
| project bad_apples_per_all = round(iff(all_apples != 0, todouble(bad_apples)/todouble(all_apples), 0.0), 10), date, distributor
| make-series bad_apples_per_all_t = bad_apples_per_all on date from start_date to end_date step 1d by distributor
| extend (anomalies, score, baseline) = series_decompose_anomalies(bad_apples_per_all_t, 1.5, -1, 'linefit')

关键修正说明 #

1. 优化数据表列名：将原table_name改为语义化的distributor，避免后续逻辑混淆。
2. 修复聚合字段：将错误的sum(total_5g_drops)改为sum(total_bad_apples)，匹配数据表实际字段。
3. 修正分组逻辑：summarize和make-series均按distributor字段分组，确保每个分销商的每日数据被正确聚合和序列化。
4. 简化空值处理：将冗余的0.000000000简化为0.0，不影响计算精度。

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