Summarize Aggregate Functions in Kusto Query Language | Kusto Query Language (KQL)

 Topic: Summarize Aggregate Functions in Kusto Query Language | Kusto Query Language (KQL)

In this video we are going to learn about summarize  so summarize produce a table that aggregates the contents of input table with summarize we will be using a lot of functions such as count some and different other ones. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //summarize -- Produces a table that aggregates the content of the input table.  
 //  
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary:int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
 // Get Count of all records ( Count of CustomerId)  
 Customer  
 | summarize TotalRows=count(CustomerId)  
 // get Sum of all salaries  
 Customer  
 | summarize TotalSalaryamount=sum(Salary)  
 // Get Count of All rows and Sum of Salary  
 Customer  
 | summarize CountofEmployees=count(),Totalsalary=sum(Salary)  
 // Get Count by Region  
 TotalSale  
 | summarize SalePersonCnt=count() by Region  
 // Group by Multiple Columns   
 TotalSale  
 | summarize CountofItems=count() ,sum(ItemsSold) by Region,Country  
 //using arg_max() -- > Finds a row in the group that maximizes ExprToMaximize, and returns the value of ExprToReturn (or * to return the entire row).  
 // Let's say if you want to find out in each of the region , which country has sold the most(max) Items.  
 TotalSale  
 | summarize arg_max(ItemsSold, Country) by Region  
 //using arg_min() -- >Finds a row in the group that minimizes ExprToMinimize, and returns the value of ExprToReturn (or * to return the entire row).  
 // Let's say if you want to find out in each of the region , which country has sold the least(minimum) Items.  
 TotalSale  
 | summarize arg_min(ItemsSold, Country) by Region  
 // using avg() - Calculates the average (arithmetic mean) of Expr across the group.  
 // Find out Avg sold price for each of product  
 TotalSale  
 | summarize avg(SoldPrice) by ProductName  
 //using max  
 TotalSale  
 | summarize  max(SoldPrice)  
 // using min   
 TotalSale  
 | summarize min(SoldPrice)  
 // get min sold price by Region  
 TotalSale  
 | summarize min(SoldPrice) by Region  
 // using sum -- Getting Sole price of all items  
 TotalSale  
 | summarize sum(SoldPrice)  
 // get sold price by Product  
 TotalSale  
 | summarize sum(SoldPrice) by ProductName  
 // take_any -- Arbitrarily chooses one record for each group in a summarize operator,  
 // and returns the value of one or more expressions over each such record.  
 TotalSale  
 | summarize take_any(ItemsSold) by Region,Country  
 // make_set --> Returns a dynamic (JSON) array of the set of distinct values that Expr takes in the group.  
 TotalSale  
 | summarize make_set(Region)  
 //dcount - Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.  
 TotalSale  
 | summarize count(City), dcount(City)   
 //make_list() -- Returns a dynamic (JSON) array of all the values of Expr in the group.  
 TotalSale  
 | summarize make_list(Region)  
 TotalSale  
 | distinct Region  
 | summarize make_list(Region)  
 // helpful link  
 https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/hll-aggfunction  

Video Demo: Summarize Aggregate Functions in Kusto Query Language

6 comments:

  1. Get the best accounting essayhelp online from a top-rated assignment expert. We have over ten years of assignment assistance experience.  accounting essay assignment help offers all types of assignment assistance https://masteressaywriters.co.uk/accounting-essay to college and university students. For all assignment help, our assignment helpers in uk guarantee on-time delivery and plagiarism-free content.

    ReplyDelete
  2. A friend of mine recommended brillianthackers800@gmail.com to me although i was feeling skeptical about them at first but i went ahead to message them and they helped me settle bank loans and they also helped me increase my credit score you can chat them on WhatsApp +14106350697

    They will surely help you with your hacking problems at a very affordable price and they are very reliable and they deliver a perfect job

    ReplyDelete
  3. SSLC Blueprint 2023 Kerala will be announced online by the Board of Secondary School Leaving Certificate. Candidates, you may get your Blueprint by inputting subjects one by one. The SSLC Blueprint 2023 for Kerala Board will be announced by the Board. Full details about the SSLC Blueprint 2023 for Kerala Board are given below. Kerala SSLC Blueprint 2023 This is excellent news for the students who have been waiting for the Kerala SSLC Blueprint 2023. The Kerala Board 10th Examination Blueprint will be available in 2023 at keraIa Blueprints 2023. After the Board has decelerated, candidates may also get their Kerala SSLC Blueprint here. We will offer a new link here to readily get Kerala SSLC Blueprint 2023 Kerala.

    ReplyDelete
  4. We offer loans at low Interest rate. Business loans and Personal loans are available. If you are In Debt we still offer you a loan since we are the last bus stop in financial Help contact us WhatsApp No:+918929509036 financialserviceoffer876@gmail.com Dr. James Eric

    ReplyDelete
  5. Brothers/Sister's. Are you looking for loan to finance your large or small business,we'll help you get the large amount of loan you desire for your business,we offer first class business and commercial loan to enable small scale business attain success in obtaining start up or refinance their business Bad credit rating accepted and poor business performance are accepted.apply today

    Are you a business man or woman? Do you need funds to start up your own business? Do you need loan to settle your debt or pay off your bills or start a nice business? Do you need funds to finance your project? We Offers guaranteed loan services of any amount and to any part of the world for (Individuals, Companies, Realtor and Corporate Bodies) at our superb interest rate of 3%. For application and more information send replies to the

    via email ronniefinancehome247@gmail.com
    Whats App +91 93118 56893
    LOAN APPLICATION FORM
    Name:
    Sex:
    Address:
    Occupation:
    State:
    Country:
    Phone Number:
    Loan Duration:
    Loan Needed:
    Have you applied before?:
    Email us ronniefinancehome247@gmail.com
    Whats-App +91 93118 56893
    Dr. Mark Thomas

    ReplyDelete
  6. I rarely write reviews, but I had to for this! Because I tried several apps to help with no luck. Back then, I was very confused and always felt awful about my partner’s cheating attitude. I really wanted to track and catch him red-handed. I downloaded this app hoping it would help but I wasn’t satisfied with its features as I needed to monitor my partner closely. I then spoke with a trusted colleague of mine at work and she gave me a genuine recommendation about an ethical private investigator named Christopher Kevin, I wrote to his email at WIZARDBRIXTON AT GMAIL DOT COM explaining how I wanted to have complete access to his call log, iMessage/SMS, social media activities, gallery, texts, deleted or not, his precise location day in day out. I paid for his services and in about 2-3 hours, I had complete access to his phone. I got concrete evidence. it was unbelievable to see the evidence of cheating on me. Who likes to live with a cheater, I used the evidence I got to file for a divorce. I’m thankful to Chris for helping me out of my dilemma and suspicions. His services are highly rated and affordable. If you are having trust issues and need valid evidence, how about you contact WIZARDBRIXTON AT GMAIL DOT COM? Thank you Chris for an incredible job !! I highly recommend his services, simply the best.

    Contact : WIZARDBRIXTON AT GMAIL DOT COM
    Cell Number : (+) 1807234 0428

    ReplyDelete