How to use Window functions in SQL Server

All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use window functions in SQL. These operate on a set of rows and return a single aggregate rate for each row.

The main advantage of using Window functions over regular aggregate functions is : window functions do not cause rows to become group into a individual end product course, the rows retain their classify identities and an aggregate value will be added to each row .
Let ’ s take a look at how window functions ferment and then see a few examples of using it in practice to be certain that things are clear and besides how the SQL and output compare to that for SUM ( ) functions .
As always be certain that you are in full backed up, particularly if you are trying out new things with your database .

Introduction to Window functions

Window functions operate on a set of rows and return a single aggregate value for each row. The term Window describes the set of rows in the database on which the routine will operate .
We define the Window ( set of rows on which functions operates ) using an OVER ( ) clause. We will discuss more about the OVER ( ) article in the article below .

Types of Window functions

• Aggregate Window Functions
  
  SUM(), MAX(), MIN(), AVG(). COUNT()
• Ranking Window Functions
  
  RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
• Value Window Functions
  

  LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Syntax

1 2 3 4

window_function ( [ ALL ] expression ) OVER ( [ PARTITION BY partition _ list ] [ ORDER BY order_list ] )

Arguments

window_function

Specify the name of the window affair

ALL

ALL is an optional keyword. When you will include ALL it will count all values including extra ones. DISTINCT is not supported in window functions

expression

The aim column or construction that the functions operates on. In other words, the name of the column for which we need an aggregate prize. For model, a column containing arrange amount indeed that we can see total orders received .

OVER

Specifies the window clauses for aggregate functions .

PARTITION BY partition_list

Defines the window ( set of rows on which window serve operates ) for window functions. We need to provide a field or list of fields for the partition after PARTITION BY clause. multiple fields need be separated by a comma as usual. If PARTITION BY is not specified, grouping will be done on entire postpone and values will be aggregated accordingly .

ORDER BY order_list

Sorts the rows within each partition. If ORDER BY is not specified, ORDER BY uses the entire table .

Examples

Let ’ s produce postpone and cut-in dummy records to write far queries. Run below code .

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

CREATE TABLE [ dbo ] . [ Orders ] ( order_id INT , order_date DATE , customer_name VARCHAR ( 250 ) , city VARCHAR ( 100 ) , order_amount MONEY ) INSERT INTO [ dbo ] . [ Orders ] SELECT ‘1001’ , ’04/01/2017′ , ‘David Smith’ , ‘GuildFord’ , 10000 UNION ALL    SELECT ‘1002’ , ’04/02/2017′ , ‘David Jones’ , ‘Arlington’ , 20000 UNION ALL    SELECT ‘1003’ , ’04/03/2017′ , ‘John Smith’ , ‘Shalford’ , 5000 UNION ALL    SELECT ‘1004’ , ’04/04/2017′ , ‘Michael Smith’ , ‘GuildFord’ , 15000 UNION ALL    SELECT ‘1005’ , ’04/05/2017′ , ‘David Williams’ , ‘Shalford’ , 7000 UNION ALL    SELECT ‘1006’ , ’04/06/2017′ , ‘Paum Smith’ , ‘GuildFord’ , 25000 UNION ALL SELECT ‘1007’ , ’04/10/2017′ , ‘Andrew Smith’ , ‘Arlington’ , 15000 UNION ALL    SELECT ‘1008’ , ’04/11/2017′ , ‘David Brown’ , ‘Arlington’ , 2000 UNION ALL    SELECT ‘1009’ , ’04/20/2017′ , ‘Robert Smith’ , ‘Shalford’ , 1000 UNION ALL    SELECT ‘1010’ , ’04/25/2017′ , ‘Peter Smith’ , ‘GuildFord’ , 500

Aggregate Window Functions

SUM()
We all know the SUM ( ) aggregate function. It does the sum of intend field for specified group ( like city, submit, nation etc. ) or for the entire postpone if group is not specified. We will see what will be the output of regular SUM ( ) aggregate function and window SUM ( ) aggregate officiate .
The follow is an exemplar of a regular SUM ( ) aggregate function. It sums the order total for each city .
You can see from the result set that a regular aggregate serve groups multiple rows into a single end product row, which causes individual rows to lose their identity .

1 2 3 4

SELECT city , SUM ( order_amount ) total_order_amount FROM [ dbo ] . [ Orders ] GROUP BY city

This does not happen with window aggregate functions. Rows retain their identity and besides show an aggregate value for each quarrel. In the exercise below the question does the same thing, namely it aggregates the data for each city and shows the summarize of sum decree total for each of them. however, the question immediately inserts another column for the total order total so that each row retains its identity. The column marked grand total is the new column in the exemplar below .

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order _ amount , SUM ( order_amount ) OVER ( PARTITION BY city ) as grand_total FROM [ dbo ] . [ Orders ]

AVG()
AVG or Average works in precisely the like way with a Window function .
The succeed question will give you average order come for each city and for each month ( although for simplicity we ’ ve only used data in one calendar month ) .
We specify more than one average by specifying multiple fields in the partition list .
It is besides worth noting that that you can use expressions in the lists like MONTH ( order_date ) as shown in below question. As always you can make these expressions equally complex as you want therefore long as the syntax is correct !

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order _ measure , AVG ( order_amount ) OVER ( PARTITION BY city , MONTH ( order_date ) ) as    average_order_amount FROM [ dbo ] . [ Orders ]

From the above visualize, we can intelligibly see that on an average we have received orders of 12,333 for Arlington city for April, 2017 .
average Order Amount = Total Order Amount / Total Orders
= ( 20,000 + 15,000 + 2,000 ) / 3
= 12,333
You can besides use the combination of SUM ( ) & COUNT ( ) function to calculate an average .
MIN()
The MIN ( ) aggregate function will find the minimal value for a specified group or for the integral table if group is not specified .
For exemplar, we are looking for the smallest order ( minimum arrange ) for each city we would use the keep up question .

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order _ sum , MIN ( order_amount ) OVER ( PARTITION BY city ) as minimum_order_amount FROM [ dbo ] . [ Orders ]

MAX()
fair as the MIN ( ) functions gives you the minimum value, the MAX ( ) affair will identify the largest value of a assign discipline for a stipulate group of rows or for the stallion table if a group is not specified .
let ’ s find the biggest order ( maximum order come ) for each city .

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order _ come , MAX ( order_amount ) OVER ( PARTITION BY city ) as maximum_order_amount FROM [ dbo ] . [ Orders ]

COUNT()
The COUNT ( ) function will count the records / rows .
note that DISTINCT is not supported with window COUNT ( ) affair whereas it is supported for the regular COUNT ( ) function. DISTINCT helps you to find the clear-cut values of a pin down playing field .
For case, if we want to see how many customers have placed an club in April 2017, we can not immediately count all customers. It is possible that the lapp customer has placed multiple orders in the same calendar month .
COUNT(customer_name) will give you an wrong leave as it will count duplicates. Whereas COUNT(DISTINCT customer_name) will give you the correct consequence as it counts each singular customer only once .
Valid for unconstipated COUNT ( ) serve :

1 2 3 4 5

SELECT city , COUNT ( DISTINCT customer_name ) number_of_customers FROM [ dbo ] . [ Orders ] GROUP Read more: How to register as a VIP in GTA Online BY city

Invalid for window COUNT ( ) function :

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order _ come , COUNT ( DISTINCT customer_name ) OVER ( PARTITION BY city ) as number_of_customers FROM [ dbo ] . [ Orders ]

The above question with Window function will give you below error .

now, let ’ s find the total order received for each city using window COUNT ( ) function .

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order _ amount , COUNT ( order_id ) OVER ( PARTITION BY city ) as total_orders FROM [ dbo ] . [ Orders ]

Ranking Window Functions

just as Window aggregate functions aggregate the value of a stipulate field, RANKING functions will rank the values of a specify field and categorize them according to their rank .
The most common consumption of RANKING functions is to find the top ( N ) records based on a certain value. For exercise, Top 10 highest paid employees, Top 10 ranked students, Top 50 largest orders etc .
The play along are supported rate functions :

RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

Let ’ s discuss them one by one .
RANK()
The RANK ( ) affair is used to give a singular rank to each criminal record based on a specified value, for case wage, order amount etc .
If two records have the same value then the RANK ( ) function will assign the lapp crying to both records by skipping the future crying. This means – if there are two identical values at crying 2, it will assign the same social station 2 to both records and then skip absolute 3 and assign rank 4 to the adjacent phonograph record .
Let ’ s rank and file each order by their order amount .

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , RANK ( ) OVER ( ORDER BY order_amount DESC ) [ Rank ] FROM [ dbo ] . [ Orders ]

From the above persona, you can see that the like rank and file ( 3 ) is assigned to two identical records ( each having an order amount of 15,000 ) and it then skips the adjacent rate ( 4 ) and assign rank 5 to future read .
DENSE_RANK()
The DENSE_RANK ( ) function is identical to the RANK ( ) routine except that it does not skip any rank. This means that if two identical records are found then DENSE_RANK ( ) will assign the same rank to both records but not skip then skip the next social station .
Let ’ s see how this works in exercise .

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order_amount , DENSE_RANK ( ) OVER ( ORDER BY order_amount DESC ) [ Rank ] FROM [ dbo ] . [ Orders ]

As you can distinctly see above, the lapp rank is given to two identical records ( each having the same order amount ) and then the future social station number is given to the next record without skipping a rank rate .
ROW_NUMBER()
The name is self-explanatory. These functions assign a unique row act to each record .
The row total will be reset for each partition if PARTITION BY is specified. Let ’ s see how ROW_NUMBER ( ) works without PARTITION BY and then with PARTITION BY .
ROW_ NUMBER() without PARTITION BY

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order_amount , ROW_NUMBER ( ) OVER ( ORDER BY order_id ) [ row_number ] FROM [ dbo ] . [ Orders ]

ROW_NUMBER() with PARTITION BY

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order_amount , ROW_NUMBER ( ) OVER ( PARTITION BY city ORDER BY order_amount DESC ) [ row_number ] FROM [ dbo ] . [ Orders ]

note that we have done the partition on city. This means that the rowing count is reset for each city and so restarts at 1 again. however, the order of the rows is determined by order measure so that for any given city the largest order measure will be the first row and then assign rowing number 1 .
NTILE()
NTILE ( ) is a very helpful window function. It helps you to identify what percentile ( or quartile, or any other section ) a given row falls into .
This means that if you have 100 rows and you want to create 4 quartiles based on a intend value field you can do so easily and see how many rows fall into each quartile .
Let ’ s see an case. In the question below, we have specified that we want to create four quartiles based on order sum. We then want to see how many orders fall into each quartile .

1 2 3 4 5

SELECT order_id , order_date , customer_name , city , order_amount , NTILE ( 4 ) OVER ( ORDER BY order_amount ) [ row_number ] FROM [ dbo ] . [ Orders ]

NTILE creates tiles based on following convention :
No of rows in each tile = number of rows in leave set / issue of tiles specified
here is our case, we have full 10 rows and 4 tiles are specified in the question so number of rows in each tile will be 2.5 ( 10/4 ). As issue of rows should be whole number, not a decimal. SQL engine will assign 3 rows for inaugural two groups and 2 rows for remaining two groups .

Value Window Functions

Value window functions are used to find first, concluding, former and future values. The functions that can be used are LAG ( ), LEAD ( ), FIRST_VALUE ( ), LAST_VALUE ( )
LAG() and LEAD()
LEAD ( ) and LAG ( ) functions are identical brawny but can be complex to explain .
As this is an introductory article below we are looking at a very childlike exercise to illustrate how to use them .
The LAG function allows to access data from the previous row in the same result set without use of any SQL joins. You can see in under exemplar, using LAG function we found former holy order date .
script to find previous order date using LAG ( ) function :

1 2 3 4 5 6

SELECT order_id , customer_name , city , order_amount , order_date , — in below line , 1 indicates check for previous row of the current row LAG ( order_date , 1 ) OVER ( ORDER BY order_date ) prev_order_date FROM [ dbo ] . [ Orders ]

LEAD function allows to access data from the adjacent row in the same solution set without use of any SQL joins. You can see in below model, using LEAD function we found following club go steady .
script to find future arrange date using LEAD ( ) officiate :

1 2 3 4 5 6

SELECT order_id , customer_name , city , order_amount , order_date , — in below line , 1 indicates check for next row of the current row LEAD ( order_date , 1 ) OVER ( ORDER BY order_date ) next_order_date FROM [ dbo ] . [ Orders ]

FIRST_VALUE() and LAST_VALUE()
These functions help you to identify first and last record within a division or entire table if PARTITION BY is not specified .
Let ’ s find the first and survive order of each city from our existing dataset. note order BY article is compulsory for FIRST_VALUE ( ) and LAST_VALUE ( ) functions

1 2 3 4 5 6

SELECT order_id , order_date , customer_name , city , order_amount , FIRST_VALUE ( order_date ) OVER ( PARTITION BY city ORDER BY city ) first_order_date , LAST_VALUE ( order_date ) OVER ( PARTITION BY city ORDER BY city ) last_order_date FROM [ dbo ] . [ Orders ]

From the above double, we can clearly see that first rate received on 2017-04-02 and last order received on 2017-04-11 for Arlington city and it works the like for early cities.

Useful Links

Other great articles from Ben

How SQL Server selects a deadlock victim

How To Use Window Functions

informant : https://thefartiste.com
Category : Tech