How to Generate Row Numbers in Kusto | Row Number Function in Kusto | Kusto Query Language Tutorial

 Topic: How to Generate Row Numbers in Kusto Query Language


In this article, we are going to learn about row number function in Kusto so row number returns the index of the current row in a serialized, row set so the row number index starts with the default one, and then if you want to increment by one that's what's going to happen by default but there is an option you can start with the different number as well, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.


 //row_number() Returns the current row's index in a serialized row set.  
 // The row index starts by default at 1 for the first row, and is incremented by 1 for each additional row.   
 //Optionally, the row index can start at a different value than 1. Additionally, the row index may be reset according to some provided predicate.  
 //Syntax  
 //row_number ( [StartingIndex [, Restart]] )  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .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  
   

 //Add RowNumber to results set  
 TotalSale  
 | order by id  
 | extend rn=row_number()  
   
  
 
 //Add RowNumber to results set by start from specific number  
 TotalSale  
 | order by id  
 | extend rn=row_number(100)  
   

 // let's see if we can use partiton of row_number  
 TotalSale  
 | order by Region,id asc  
 | extend rn=row_number(1,Region!=prev(Region))  
   


Video Demo: How to Generate Row Numbers in Kusto | Row Number Function in Kusto 

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.