using output of one query in another in kql

user_651486 0
Viewed 34
let average = materialize(FooTable
| summarize avg(value) by group, class
| summarize arg_min(avg_value, class) by group

This should output something like (i.e. minimum value for the group averages per class):

group class avg_value
G1 C1 100
G2 C2 150
.. .. ..

Now, I would like to display all the group, class and value row which shows the delta from their group's minimum class average as calculated by the query above.

FooTable
| where value > ( here I want to insert the query to get min by group and class)

Output should be something like:

group class min_avg_value delta
G1 C1 100 0
G1 C2 120 20
G2 C1 200 50
G2 C2 150 0
.. .. .. ..

Thanks for the help in advance!

kql azure-data-explorer
2 Answers

lookup

let FooTable = datatable (group:string, class:string, value:int)
[
    'G1' ,'C1', 100
   ,'G1' ,'C2', 120
   ,'G2' ,'C1', 200
   ,'G2' ,'C2', 150
];
let average = materialize(
FooTable
| summarize avg(value) by group, class
| summarize min(avg_value) by group
);
FooTable
| lookup kind=inner average on group
| extend delta = value - min_avg_value
Show expand view group class value min_avg_value delta
G1 C1 100 100 0
G1 C2 120 100 20
G2 C1 200 150 50
G2 C2 150 150 0

Fiddle

user_63364790

join

let FooTable = datatable (group:string, class:string, value:int)
[
    'G1' ,'C1', 100
   ,'G1' ,'C2', 120
   ,'G2' ,'C1', 200
   ,'G2' ,'C2', 150
];
let average = materialize(
FooTable
| summarize avg(value) by group, class
| summarize min(avg_value) by group
);
average
| join kind=inner FooTable on group
| extend delta = value - min_avg_value
group min_avg_value group1 class value delta
G1 100 G1 C1 100 0
G1 100 G1 C2 120 20
G2 150 G2 C1 200 50
G2 150 G2 C2 150 0

Fiddle

user_63364790
Related