Azure Data Explorer - Approaches For Data Aggregation In Kusto

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,
  1. let demoData = datatable(Environment: string, Version:int, BugCount:int)  
  2. [  
  3. "dev",1, 1,  
  4. "test",1, 1,  
  5. "prod",1, 1,  
  6. "dev",2, 2,  
  7. "test",2, 0,  
  8. "dev",3, 2,  
  9. "test",3, 0,  
  10. "prod",2,2,  
  11. ];  
Get the average number of bugs falling under each category.
Expected Output
Azure Data Explorer - Approaches For Data Aggregation In Kusto
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.
  1. 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.
  1. demoData| join kind=leftouter (  
  2. demoData | summarize ceiling(avg(BugCount)) by Environment) on Environment  
  3. | project Environment, avg_BugCount  
  4. | 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.
  1. let Averages = demoData  
  2. | summarize ceiling(avg(BugCount)) by Environment;  
  3. demoData | lookup (Averages) on Environment  
  4. | project Environment, avg_BugCount  
  5. | distinct Environment,avg_BugCount  
I hope you enjoyed aggregating data.
Happy Kusto-ing!