Topic: How to find Running Total in Kusto | Prev Function in Kusto Query.
In this video we are going to learn about the previous function in Kusto, previous function returns the value of a specific column in a specified row the specified row is a specified offset from a current row in the serialized row set so there are three different options we have with the previous we can simply provide the column or we can provide the offset value as well or there is a third one if there is a default value you would like to put for empty values, also you are going to learn how to find or calculate the running total by entire table or by a specific group
//prev() - Returns the value of a specific column in a specified row. The specified row is at a specified offset from the current row in a serialized row set.
//Syntax
//prev(column)
//prev(column, offset)
//prev(column, offset, default_value)
//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
//using next() to get next value from row
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(id)
//using next() to get next value from row -- using offset
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(id,2)
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)
// using the third parameter to fill the blanks
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)
TotalSale
| order by id asc
| project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2,"Fill the Blanks")
// More real time scenario
//Running total by Region
TotalSale
| order by Region,ItemsSold
| project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold)
// Getting Running total for Region base
TotalSale
| order by Region,ItemsSold
| project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold, Region != prev(Region))
No comments:
Post a Comment