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,
//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))
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.