Introduction
Regular aggregate functions, which operate on a complete table and are utilized with a GROUP BY clause, are widely known to database users. However, very few individuals use SQL's Window functions. These perform operations on a collection of rows and return an aggregated value for each row.
This article will discuss SQL’s Window functions. First, look at how Window Functions operate before seeing some examples.
MS SQL Server Window Function
Window Functions return a single aggregated value for each row when used on a group of rows. The collection of database rows on which the function will operate is known as the window.
The use of window functions can ease procedures. Window functions will act on the remaining rows (the data window) after you get the results of your query, that is, after the WHERE clause and any standard aggregation, to provide the results you want.
Some functions used in SQL Server are as follows:
CUME_DIST
The top or bottom x percent values from a data collection are sometimes what you want to include in a report, for example, the top 5 percent of salespeople by net sales. Using the
CUME_DIST() function in SQL Server is one approach to achieve this.
The cumulative distribution of a value within a group of values is calculated using the CUME DIST() function. In other words, it determines a value's position with another set of values.
Syntax
CUME_DIST() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
DENSE_RANK
Each row within a partition of a result set receives a rank due to the window function DENSE_RANK(). The DENSE_RANK() function, as compared to RANK(), returns rank values in order. If two rows in a partition have the same values, they are ranked equally.
Syntax
DENSE_RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
FIRST_VALUES
An analytical function is FIRST_VALUE(). The window function FIRST VALUE() returns the first value in an ordered partition of a result set. Unless you specify IGNORE NULLS, the function returns NULL if the first value in the set is null. This is a practical setting for data densification.
Syntax
FIRST_VALUE ( scalar_expression )
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
LAG
A window function in SQL Server called LAG() gives access to a row that is physically offset from the current row and comes before it.
In other words, you can retrieve data from the previous row, the row before the previous row, and so on by calling the LAG() function from the current row.
When comparing the current row's value with the previous row's value, the LAG() function can be helpful.
Syntax
LAG(return_value ,offset [,default])
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
LAST_VALUE
The function LAST VALUE() returns an ordered set of values' last value. Unless you specify IGNORE NULLS, the function returns NULL if the final value in the set is null.
Syntax
LAST_VALUE ( scalar_expression )
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
LEAD
A window function in SQL Server called LEAD() provides access to a row that follows the current row at a given physical offset.
The LEAD() function, for instance, allows you to get data from the current row for the following row, the row after that, and so on.
When comparing the value of the current row with the subsequent row, the LEAD() function can be beneficial.
Syntax
LEAD(return_value ,offset [,default])
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
NTILE
The window function NTILE() of the SQL Server divides the rows of an ordered partition into a predetermined number of nearly equal groups or buckets. Each group is given a bucket number, starting from one. The NTILE() function assigns a bucket number, which corresponds to the group to which the row belongs, to each row in a group.
Syntax
NTILE(buckets) OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
PERCENT_RANK
The PERCENT RANK() function is similar to the above-mentioned CUME DIST() function. The PERCENT RANK() function evaluates a value's position within a result set partition.
Syntax
PERCENT_RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
RANK
Each row within a partition of a result set receives a rank due to the window function RANK().
A partition's rows with similar values will have the same rank. The first row in a partition has rank one. The ranks may not be consecutive since the RANK() function combines the number of tied rows by the secured rank to determine the rank of the next row.
Syntax
RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
ROW_NUMBER
A sequential rank number is given to each new record in a partition by the SQL ranking function ROW NUMBER. The SQL Server ROW NUMBER function assigns different rank numbers to each pair of identical values it detects in the same partition.
Syntax
ROW_NUMBER() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)