Kusto查询百分比计算结果异常问题排查

问题根源 #

你遇到的是Kusto的整数除法特性：当两个整数类型的数值相除时，Kusto会直接舍去小数部分，只返回整数结果。你的CurrentNull和CurrentRows都是通过toint()转换的整数类型，所以12517/352393的计算结果会被取整为0，再乘以100自然还是0。

解决方法 #

只需要将其中一个操作数转换为浮点类型（比如real或decimal），强制触发浮点除法即可得到正确的百分比。以下是两种修改后的查询示例：

方式1：将被除数转为real类型 #

customEvents
| where name == "Nulls"
| project timestamp,    
    Column = tostring(customDimensions["Column"]),
    CurrentNull = toint(customDimensions["CurrentNull"]),
    PreviousNull = toint(customDimensions["PreviousNull"]),
    CurrentRows = toint(customDimensions["CurrentRows"]),
    PreviousRows = toint(customDimensions["PreviousRows"])    
| distinct timestamp, Column, CurrentNull, PreviousNull, CurrentRows, PreviousRows
| extend Delta = CurrentNull - PreviousNull,
         CurrentNullPercent = (toreal(CurrentNull)/CurrentRows) * 100,
         PreviousNullPercent = (toreal(PreviousNull)/PreviousRows) * 100
| distinct timestamp, Column, CurrentNull, PreviousNull, Delta, CurrentNullPercent, CurrentRows
| order by Delta desc

方式2：调整计算顺序（先乘后除） #

如果担心数值溢出（你的场景中数值较小，不会有问题），也可以先乘以100再做除法，同时确保至少一个操作数为浮点类型：

customEvents
| where name == "Nulls"
| project timestamp,    
    Column = tostring(customDimensions["Column"]),
    CurrentNull = toint(customDimensions["CurrentNull"]),
    PreviousNull = toint(customDimensions["PreviousNull"]),
    CurrentRows = toint(customDimensions["CurrentRows"]),
    PreviousRows = toint(customDimensions["PreviousRows"])    
| distinct timestamp, Column, CurrentNull, PreviousNull, CurrentRows, PreviousRows
| extend Delta = CurrentNull - PreviousNull,
         CurrentNullPercent = (CurrentNull * 100) / toreal(CurrentRows),
         PreviousNullPercent = (PreviousNull * 100) / toreal(PreviousRows)
| distinct timestamp, Column, CurrentNull, PreviousNull, Delta, CurrentNullPercent, CurrentRows
| order by Delta desc

上述两种方式都能让12517/352393 * 100得到正确的近似3的结果。

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