Блог

Как мне преобразовать данные kusto, которые выглядят следующим образом:

 let fauxData = datatable (OrgName:string, Status:string, EastUS:long, SouthCentralUS:long, WestUS2:long)
['Apple', 'Red',    50, 10, 90,
'Apple', 'Orange', 30, 30, 10,
'Apple', 'Yellow', 10, 0, 0,
'Apple', 'Green', 10, 60, 0,
'Ball', 'Red', 20, 20, 20,
'Ball', 'Orange', 30, 30, 30,
'Ball', 'Yellow', 0, 0, 0,
'Ball', 'Green', 50, 50, 50,
];
  

Чтобы выглядеть так:

 ['Apple', 'ComboOfRedandOrange', 80, 40, 100,
'Apple', 'ComboOfGreenandYellow', 20, 60, 0,
'Ball', 'ComboOfRedandOrange', 50, 50, 50,
'Ball', 'ComboOfGreenandYellow', 50, 50, 50,
]
  

Вы можете использовать следующий запрос для достижения своей цели:

 let fauxData = datatable (OrgName:string, Status:string, EastUS:long, SouthCentralUS:long, WestUS2:long)
['Apple', 'Red',    50, 10, 90,
'Apple', 'Orange', 30, 30, 10,
'Apple', 'Yellow', 10, 0, 0,
'Apple', 'Green', 10, 60, 0,
'Ball', 'Red', 20, 20, 20,
'Ball', 'Orange', 30, 30, 30,
'Ball', 'Yellow', 0, 0, 0,
'Ball', 'Green', 50, 50, 50,
];
fauxData
| extend combo = case(Status in ('Red', 'Orange'), 'ComboOfRedandOrange',
                      Status in ('Green', 'Yellow'), 'ComboOfGreenandYellow',
                     'Unknown')
| summarize sum(EastUS), sum(SouthCentralUS), sum(SouthCentralUS) by OrgName, combo
  

вы могли бы попробовать что-то вроде этого:

 let T = 
    datatable(OrgName: string, Status: string, EastUS: long, SouthCentralUS: long, WestUS2: long)
    [
        'Apple', 'Red', 50, 10, 90,
        'Apple', 'Orange', 30, 30, 10,
        'Apple', 'Yellow', 10, 0, 0,
        'Apple', 'Green', 10, 60, 0,
        'Ball', 'Red', 20, 20, 20,
        'Ball', 'Orange', 30, 30, 30,
        'Ball', 'Yellow', 0, 0, 0,
        'Ball', 'Green', 50, 50, 50,
    ]
;
let F = (statuses:dynamic)
{
    T
    | where Status in(statuses)
    | summarize sum(EastUS), sum(SouthCentralUS), sum(WestUS2) by OrgName, Status = strcat("ComboOf", strcat_array(statuses, "And"))
}
;
union 
 F(dynamic(['Red', 'Orange'])),
 F(dynamic(['Green', 'Yellow']))
| order by OrgName asc, Status asc