Window Functions in SQL

Types of Window Functions in SQL

SQL window functions are powerful tools used to perform calculations across a set of rows related to the current row without collapsing the result set.

Aggregate Window Functions

Aggregate window functions compute a single result from a set of input values.

SUM(), AVG(), COUNT(), MAX(), MIN()

These functions operate similarly to their non-window counterparts but allow for calculations over a specified window or partition of rows.

Example using AVG() with PARTITION BY:

SELECT
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary_by_dept
FROM
    employees;

Output:

Explanation:

The AVG() function calculates the average salary for each department separately (PARTITION BY department), resulting in the avg_salary_by_dept column.

Ranking Window Functions

Ranking window functions assign a rank to each row based on the ordering specified within the window.

RANK()

Assigns a unique rank to each distinct row within the partition of a result set.

DENSE_RANK()

Similar to RANK(), but ranks are consecutive integers without gaps.

ROW_NUMBER()

Assigns a unique sequential integer to each row within the partition.

These functions are useful for tasks such as top-N queries, pagination, and ranking results based on specific criteria.

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], ...
)

Difference Between Window Functions and Aggregate Functions

While both window functions and aggregate functions perform calculations on sets of rows, they serve different purposes in SQL. The key difference lies in how they return results and whether they preserve individual row-level data.

Frequently Asked Questions

When should we use Window functions?

When using window functions, calculations are done on a set of linked rows. However, windowing functions do not collapse the outcome of the rows into a single value, in contrast to aggregate functions.

What is the basic purpose of the window function in SQL?

Data in the records right before and following the current record can be accessed using window functions. To perform a calculation across the set of data in the window, a window function defines a frame or window of rows with a specified length around the current row.

Are window functions faster than subqueries?

The significant difference in execution time is evident: window functions perform far better. The window function approach needs far fewer rows for consideration and takes much less to execute—a difference of about 5 minutes and 17 seconds.

What Are the Commonly Used SQL Server Window Functions?

Common SQL Server window functions include: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE(). These functions perform calculations across a set of rows while retaining individual row data.

Conclusion

This article extensively discussed the topic of MS SQL server window functions in detail. We started with an introduction and debated the definition of MS SQL server window functions. Later, we also discussed the SQL server's functions and syntax. We hope this blog has helped you enhance your knowledge of the MS SQL server window functions.