Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial

Topic: Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg,


In this article, we are going to learn about series_stats, Series stats returns statistics for a numerical series using multiple columns. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.


 //series_stats - series_stats() returns statistics for a numerical series using multiple columns.  
 //   
 //The series_stats() function takes an expression returning a dynamical numerical array as input, and calculates the following statistics:  
   
 Statistic           Description  
 min               Minimum value in the input array.  
 min_idx             The first position of the minimum value in the input array.  
 max               Maximum value in the input array.  
 max_idx             First position of the maximum value in the input array.  
 avg               Average value of the input array.  
 variance           Sample variance of input array.  
 stdev             Sample standard deviation of the input array.  

   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
   
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
    
    
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   
 print x=dynamic([23,46,23,87,4,8,3,75,2,56,13,75,32,16,29])   
 | project series_stats(x), series_stats_dynamic(x)  
   
 TotalSale | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
   
 TotalSale   
 | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | project Region,TotalItemsSold,series_stats(TotalItemsSold),series_stats_dynamic(TotalItemsSold)  
   

 // show only selected values  
   
 TotalSale   
 | make-series TotalItemsSold=sum(ItemsSold) default=0 on SoldDate from datetime(2014-03-09T00:00:00Z) to datetime(2015-07-15T00:00:00Z) step 1d by Region  
 | extend series_stats(TotalItemsSold),arryofvalues=series_stats_dynamic(TotalItemsSold)  
 | project Region,arryofvalues.min,arryofvalues.max,arryofvalues.avg  
   

Video Demo: Standard Deviation of Series in Kusto | Series Stats Get the Min, Max, Avg, | Kusto Query Tutorial

No comments:

Post a Comment