在Application Insights中用Kusto查询统计distinct字段的正确方式

我正在使用Application Insights的customEvent，需要统计带有distinct字段的事件数量。事件结构如下：

{
    "statusCode" : 200,
    "some_field": "ABC123QWERTY"
}

我想要统计statusCode为200时的unique some_field数量。尝试了多种查询后结果各不相同，对应的SQL写法如下：

SELECT COUNT(DISTINCT my_field) AS Count
FROM customEvents
WHERE statusCode=200

哪种写法是正确的？

1 - 默认精度的dcount #

customEvents
| where (customDimensions.statusCode == 200) and timestamp >= startofday(datetime(2022-12-01T00:00:00Z)) and timestamp <= endofday(datetime(2022-12-31T00:00:00Z))
| summarize dcount(tostring(customDimensions.some_field))

结果：17,853 项

2 - 按my_field分组计数后统计行数 #

customEvents
| extend my_field = tostring(customDimensions.some_field)
| where customDimensions.statusCode == 200 and timestamp >= startofday(datetime(2022-12-01T00:00:00Z)) and timestamp <= endofday(datetime(2022-12-31T00:00:00Z))
| summarize Count = count() by my_field

结果：17,774 项

3 - 按some_field分组的summarize #

customEvents
| extend StatusCode = tostring(customDimensions["statusCode"]), MyField = tostring(customDimensions["some_field"])
| where timestamp >= startofday(datetime(2022-12-01T00:00:00Z)) and timestamp <= endofday(datetime(2022-12-31T00:00:00Z))
| summarize any(StatusCode) by MyField
| summarize Count = count() by any_StatusCode

结果：17,626 项

4 - 更高精度的dcount #

customEvents
| where (customDimensions.statusCode == 200) and timestamp >= startofday(datetime(2022-12-01T00:00:00Z)) and timestamp <= endofday(datetime(2022-12-31T00:00:00Z))
| summarize dcount(tostring(customDimensions.some_field),4)

结果：17,736 项

5 - 预览版的count_distinct #

customEvents
| where (customDimensions.statusCode == 200) and timestamp >= startofday(datetime(2022-12-01T00:00:00Z)) and timestamp <= endofday(datetime(2022-12-31T00:00:00Z))
| summarize count_distinct(tostring(customDimensions.some_field))

结果：17,744 项

函数说明 #

• 官方文档指出：可使用dcount和dcountif统计特定列中的distinct值。
• dcount聚合函数：返回分组中表达式的distinct值的估算值，并非精确结果。
• count_distinct函数：统计每个汇总组中标量表达式指定的唯一值，若未指定汇总组则统计全局唯一值总数，该函数返回精确结果。

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