Kusto关联两表并统计首表每条记录对应两表的键数量

实现逻辑 #

不要先直接关联两张原表做计数，否则同键值下的多对多匹配会产生笛卡尔积，导致统计结果偏大。正确处理流程：

• 先分别对T表、U表按关联键做分组聚合，算出每个键对应的记录总数
• 再以原T表为基准，关联两个聚合结果，匹配不到U表键值时填充计数为0即可

完整可运行查询语句 #

let T = datatable(TId:int, TName:string, Tkey:string)
[
   1, "A", "xyz",
   2, "B", "xyz",
   3, "C", "yza",
];
let U = datatable(UId:int, UName:string, Ukey:string)
[
   1, "bla1", "xyz",
   2, "bla2", "xyz",
   30, "bla3", "xyz",
   12, "bla5", "xyz",
   80, "bla9", "xyz",
   4, "bla11", "cde",
   55, "bla12", "yza",
   96, "bla21", "yza",
];
// 统计T表内每个Tkey对应的记录总数
let T_key_stat = T | summarize T_same_key_count = count() by Tkey;
// 统计U表内每个Ukey对应的记录总数
let U_key_stat = U | summarize U_match_key_count = count() by Ukey;
T
// 关联T表自身的键统计结果
| lookup T_key_stat on Tkey
// 关联U表的键统计结果
| lookup U_key_stat on $left.Tkey == $right.Ukey
// 对U表无匹配的键填充计数0
| extend U_match_key_count = coalesce(U_match_key_count, 0)
// 按需要输出字段
| project TId, TName, Tkey, T_same_key_count, U_match_key_count

运行结果 #

执行上述语句后得到的输出完全匹配需求：

注：这里用lookup代替普通join是因为lookup针对维度表关联场景做了性能优化，适合这种小聚合表关联原事实表的场景，执行效率更高。

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