如何让ADX/KQL中源表与物化视图的百分位数值一致？

已通过物化视图从源表生成聚合表，但发现直接从源表计算的百分位数与从聚合表（实际场景中stg01为源表的物化视图）计算的结果存在差异，以下是问题复现代码及解决方案：

源表直接计算百分位数的代码 #

let MySourceTable = datatable(col1:int, col2:int, Value:int, EventUTCDateTime:datetime)
[
1,2,10,'2022-01-01 01:00:01',
1,2,20,'2022-01-01 02:01:01',
1,2,30,'2022-01-01 03:05:01',
1,2,40,'2022-01-02 01:30:01',
1,2,20,'2022-01-02 02:30:01',
1,2,50,'2022-01-03 07:30:01',
1,2,50,'2022-01-05 06:30:01'
];
MySourceTable
| extend EventUTCDate = bin(EventUTCDateTime,30m)
| project col1,col2,Value,EventUTCDate
| summarize RecordsCount=count(), TotalValue=sum(Value), Min=min(Value), Max=max(Value),Median=percentile(Value,50) by col1,col2
| project col1,col2, Mean=TotalValue/RecordsCount, Min, Max,Median

聚合表分层计算百分位数的代码（存在差异） #

let MySourceTable = datatable(col1:int, col2:int, Value:int, EventUTCDateTime:datetime)
[
1,2,10,'2022-01-01 01:00:01',
1,2,20,'2022-01-01 02:01:01',
1,2,30,'2022-01-01 03:05:01',
1,2,40,'2022-01-02 01:30:01',
1,2,20,'2022-01-02 02:30:01',
1,2,50,'2022-01-03 07:30:01',
1,2,50,'2022-01-05 06:30:01'
];
let stg01 = MySourceTable
| extend EventUTCDate = bin(EventUTCDateTime,1d)
| project col1,col2,Value,EventUTCDate
| summarize RecordsCount=count(), TotalValue=sum(Value), Min=min(Value), Max=max(Value),Median=percentile(Value,50) by col1,col2,EventUTCDate;
stg01
| summarize RecordsCount=sum(RecordsCount), TotalValue=sum(TotalValue),Min=min(Min), Max=max(Max),Median=percentile(Median,50) by col1,col2
| project col1,col2, Mean=TotalValue/RecordsCount, Min, Max,Median 

问题原因 #

百分位数的计算依赖全量数据的排序分布，直接对分层计算出的百分位数再做聚合，本质是对"分组百分位数"的二次统计，丢失了原始数据的完整分布信息，必然和全局百分位数存在差异。

解决方案 #

方案1：使用tdigest类型实现增量式百分位数计算 #

tdigest是Kusto中用于高效计算近似百分位数的数据结构，支持合并操作。可以在物化视图中存储每个分组的tdigest，后续全局聚合时合并这些tdigest再计算百分位数，结果与直接从源表计算一致。

修正后的聚合表计算代码

let MySourceTable = datatable(col1:int, col2:int, Value:int, EventUTCDateTime:datetime)
[
1,2,10,'2022-01-01 01:00:01',
1,2,20,'2022-01-01 02:01:01',
1,2,30,'2022-01-01 03:05:01',
1,2,40,'2022-01-02 01:30:01',
1,2,20,'2022-01-02 02:30:01',
1,2,50,'2022-01-03 07:30:01',
1,2,50,'2022-01-05 06:30:01'
];
// 物化视图层：存储每个日分组的tdigest及其他聚合指标
let stg01 = MySourceTable
| extend EventUTCDate = bin(EventUTCDateTime,1d)
| project col1,col2,Value,EventUTCDate
| summarize 
    RecordsCount=count(), 
    TotalValue=sum(Value), 
    Min=min(Value), 
    Max=max(Value),
    ValueTdigest=tdigest(Value)  // 存储tdigest结构
by col1,col2,EventUTCDate;
// 全局聚合层：合并tdigest后计算百分位数
stg01
| summarize 
    RecordsCount=sum(RecordsCount), 
    TotalValue=sum(TotalValue),
    Min=min(Min), 
    Max=max(Max),
    Median=percentile_tdigest(merge_tdigests(ValueTdigest), 50)  // 合并tdigest并计算中位数
by col1,col2
| project col1,col2, Mean=TotalValue/RecordsCount, Min, Max,Median 

方案2：调整物化视图粒度（仅适用于数据量不大的场景） #

如果数据量允许，可以将物化视图的聚合粒度调整到与最终全局聚合一致，避免分层二次统计；或者在物化视图中保留原始数据的分组统计（比如用make_list(Value)存储每个分组的所有值），但这种方法会占用大量存储，仅适合小数据场景。

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