T-SQL Window Functions

Introduction to T-SQL Window Functions

Window functions in T-SQL provide a powerful way to perform calculations across a set of table rows that are related to the current row. This is achieved without collapsing the rows into a single output row, unlike traditional aggregate functions. Window functions allow for more complex analytical queries, such as calculating running totals, rankings, and moving averages.

Overview

Window functions operate on a "window" or a set of rows defined by the OVER clause. The OVER clause specifies how the rows are partitioned and ordered, enabling calculations to be performed relative to the current row.

Types of Window Functions

T-SQL window functions can be broadly categorized into three groups:

1. Ranking Functions

These functions assign a rank to each row within a partition. Common ranking functions include:

ROW_NUMBER(): Assigns a unique, sequential integer to each row within its partition.
RANK(): Assigns a rank to each row within its partition. Rows with the same value receive the same rank, and the next rank is skipped (e.g., 1, 1, 3).
DENSE_RANK(): Similar to RANK(), but assigns consecutive ranks without gaps (e.g., 1, 1, 2).
NTILE(n): Divides the rows in the partition into a specified number of groups (n) and assigns a group number to each row.

2. Analytic Functions (or Value Functions)

These functions return a value that is derived from other rows in the same partition. They often operate on values from preceding or succeeding rows.

LAG(): Accesses data from a previous row in the same result set without using a subquery.
LEAD(): Accesses data from a subsequent row in the same result set without using a subquery.
FIRST_VALUE(): Returns the value of an expression from the first row in the window frame.
LAST_VALUE(): Returns the value of an expression from the last row in the window frame.
NTH_VALUE(): Returns the value of an expression from the nth row in the window frame.

3. Aggregate Functions

Standard aggregate functions (like SUM, AVG, COUNT, MIN, MAX) can also be used as window functions when combined with the OVER clause. This allows them to operate over a window of rows rather than an entire group.

Syntax

The core of a window function is the OVER clause:


expression OVER (
    [ PARTITION BY value_expression ,...n ]
    [ ORDER BY clause ]
    [ frame_clause ]
)

expression: The window function to be applied (e.g., ROW_NUMBER(), SUM(SalesAmount)).
PARTITION BY: Divides the rows into partitions to which the window function is applied independently. If omitted, the entire result set is treated as a single partition.
ORDER BY: Orders rows within each partition. This is mandatory for ranking and analytic functions and influences the window frame.
frame_clause: Defines the subset of rows within the partition on which the function operates for the current row (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for running totals).

Examples

Example: Running Total of Sales

Calculate the cumulative sum of sales for each employee.

Using SUM() OVER()


SELECT
    EmployeeID,
    OrderDate,
    SalesAmount,
    SUM(SalesAmount) OVER (PARTITION BY EmployeeID ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM
    SalesOrders;

Example: Ranking Employees by Sales

Rank employees based on their total sales amount.

Using RANK() OVER()


SELECT
    EmployeeID,
    SUM(SalesAmount) AS TotalSales,
    RANK() OVER (ORDER BY SUM(SalesAmount) DESC) AS SalesRank
FROM
    SalesOrders
GROUP BY
    EmployeeID
ORDER BY
    SalesRank;

Example: Using LAG to Compare Sales

Compare the current month's sales to the previous month's sales for each employee.

Using LAG() OVER()


WITH MonthlySales AS (
    SELECT
        EmployeeID,
        DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS SaleMonth,
        SUM(SalesAmount) AS MonthlySalesAmount
    FROM SalesOrders
    GROUP BY EmployeeID, DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
)
SELECT
    EmployeeID,
    SaleMonth,
    MonthlySalesAmount,
    LAG(MonthlySalesAmount, 1, 0) OVER (PARTITION BY EmployeeID ORDER BY SaleMonth) AS PreviousMonthSales
FROM MonthlySales
ORDER BY EmployeeID, SaleMonth;

Key Considerations

Performance: While powerful, window functions can be resource-intensive on large datasets. Ensure proper indexing and query optimization.
ORDER BY in OVER: For ranking and value functions, the ORDER BY clause within the OVER clause is crucial for defining the order of operations.
Window Frames: Understanding and correctly specifying the window frame (e.g., ROWS BETWEEN ..., RANGE BETWEEN ...) is essential for accurate results, especially with aggregate window functions.
NULL Handling: Be mindful of how NULL values are handled by specific window functions and your data.

Mastering T-SQL window functions can significantly enhance your ability to analyze data and derive meaningful insights directly within your SQL Server database.

Table of Contents