KQL数据分组求和问题：按部门分组计算两类薪资总和

示例数据 #

以下是模拟的员工薪资数据表：

datatable(Department:string, Compesation:int, Salary:double)
[
    "Engineering", 1, 80000.0,
    "Engineering", 1, 95000.0,
    "Engineering", 0, 75000.0,
    "HR", 1, 65000.0,
    "HR", 0, 60000.0,
    "HR", 0, 55000.0,
    "Marketing", 1, 70000.0,
    "Marketing", 1, 78000.0,
    "Marketing", 0, 68000.0
]

期望输出格式 #

期望按部门分组，将两种Compesation值的薪资总和合并为一行两列：

原有方案问题分析 #

按Department和Compesation双字段分组的方式，会将每个部门的两种Compesation值拆分为独立行（如Engineering占2行、HR占2行、Marketing占2行，共6行），无法实现同一部门统计结果合并为一行的需求。

正确KQL语句 #

方法1：使用pivot函数（推荐） #

通过先按双字段分组统计，再将Compesation值转置为列：

datatable(Department:string, Compesation:int, Salary:double)
[
    "Engineering", 1, 80000.0,
    "Engineering", 1, 95000.0,
    "Engineering", 0, 75000.0,
    "HR", 1, 65000.0,
    "HR", 0, 60000.0,
    "HR", 0, 55000.0,
    "Marketing", 1, 70000.0,
    "Marketing", 1, 78000.0,
    "Marketing", 0, 68000.0
]
| summarize sum(Salary) by Department, Compesation
| pivot Compesation, sum(sum_Salary) into Total_Salary_Comp0=0, Total_Salary_Comp1=1

方法2：直接在summarize中条件求和 #

通过case函数判断Compesation值，分别计算对应薪资总和：

datatable(Department:string, Compesation:int, Salary:double)
[
    "Engineering", 1, 80000.0,
    "Engineering", 1, 95000.0,
    "Engineering", 0, 75000.0,
    "HR", 1, 65000.0,
    "HR", 0, 60000.0,
    "HR", 0, 55000.0,
    "Marketing", 1, 70000.0,
    "Marketing", 1, 78000.0,
    "Marketing", 0, 68000.0
]
| summarize 
    Total_Salary_Comp1 = sum(case(Compesation == 1, Salary, 0.0)),
    Total_Salary_Comp0 = sum(case(Compesation == 0, Salary, 0.0))
by Department

以上两种方法均可输出符合预期的结果：每个部门一行，包含Compesation=1和Compesation=0对应的薪资总和列。

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