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

word image 93word image 93
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

]

word image 94word image 94
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

]

word image 95word image 95
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

]

word image 96word image 96
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

]

word image 97word image 97
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

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 .
word image 98word image 98
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

]

word image 99word image 99

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

]

word image 100word image 100
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

]

word image 101word image 101
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

]

word image 102word image 102
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

]

word image 103word image 103
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

]

word image 104word image 104
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

]

word image 105word image 105
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

]

word image 106word image 106
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

]

word image 107word image 107
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

About admin

I am the owner of the website thefartiste.com, my purpose is to bring all the most useful information to users.

Check Also

articlewriting1

Manage participants in a zoom meeting webinar

Call the people who attend the meet as follows Alternate host host Who scheduled the …

Leave a Reply

Your email address will not be published.