Azure Data Explorer - Approaches For Data Aggregation In Kusto

This blog talks about various approaches for aggregating data via Kusto Query Language.

In my previous posts I tried to transcribe the things that were not too obvious for me when I initially started working on Kusto Query Language. Continuing with the same thought, this time I’m going to share a few of the approaches that can be taken to aggregate the data.

 

Let’s consider the below input data,

let demoData = datatable(Environment: string, Version:int, BugCount:int)
[
"dev",1, 1,
"test",1, 1,
"prod",1, 1,
"dev",2, 2,
"test",2, 0,
"dev",3, 2,
"test",3, 0,
"prod",2,2,
];

Description

 

Get the average number of bugs falling under each category.

 

Expected Output

 

 

There are several approaches to achieve this.

 

Approach 1 - Using Partition Operator

 

Partition operator first partitions the input data with defined criteria and then combines all the results.

demoData| partition by Environment (summarize ceiling(avg(BugCount)) by Environment);

Approach 2 - Using Join Operator

 

Join merges the two tables based on the specified key.

demoData| join kind=leftouter (
demoData | summarize ceiling(avg(BugCount)) by Environment) on Environment
| project Environment, avg_BugCount
| distinct Environment,avg_BugCount;

Approach 3 - Using Lookup Operator

 

Lookup operator extends the column of the second table and looks up the values in the first one.

let Averages = demoData
| summarize ceiling(avg(BugCount)) by Environment;
demoData | lookup (Averages) on Environment
| project Environment, avg_BugCount
| distinct Environment,avg_BugCount

I hope you enjoyed aggregating data.

 

Happy Kusto-ing!