Window

Window function support is an experimental feature of the driver. This functionality extends beyond the driver’s core scope of being SQL-92 compliant. As such, performance with window functions may not be optimal.

Window Function Clauses

OVER

The OVER clause defines the window over which window functions are performed.

SELECT A, B, <window function> OVER (<window frame>) FROM TableName

The refers to any supported window function clause, and the refers to one or more clauses that specify the logic by which the window is defined.

PARTITION BY

The PARTITION BY clause subdivides a window into sub-windows called partitions. For each unique value in the column specified in the PARTITION BY clause, every record with that value collectively forms an individual partition. SELECT A, B, OVER (PARTITION BY A ORDER BY B) From Lead The refers to any supported window function clause.

Math Functions

These window functions perform mathematical operations on the records within the window.

COUNT

Calculates the number of records in each partition. The calculated column is of the data type “int”. Syntax

COUNT()

Examples In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT() OVER (PARTITION BY Role) FROM Employees

COUNT_BIG

Calculates the number of records in each partition. The calculated column is of the data type “bigint”. Syntax

COUNT_BIG()

Examples In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT_BIG() OVER (PARTITION BY Role) FROM Employees

MIN

Calculates the minimum value of a numerical column per partition. Syntax

MIN(numeric_column)

Examples In each partition, every record will display the minimum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MIN(Earnings) OVER (PARTITION BY Role) FROM Employees

MAX

Calculates the maximum value of a numerical column per partition. Syntax

MAX(numeric_column)

Examples In each partition, every record will display the maximum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MAX(Earnings) OVER (PARTITION BY Role) FROM Employees

SUM

Calculates the sum of a numerical column per partition. Syntax

SUM(numeric_column)

Examples In each partition, every record will display the sum of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, SUM(Earnings) OVER (PARTITION BY Role) FROM Employees

AVG

Calculates the average value of a numerical column per partition. Syntax

AVG(numeric_column)

Examples In each partition, every record will display the average value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, AVG(Earnings) OVER (PARTITION BY Role) FROM Employees

MEDIAN

Calculates the median value of a numerical column per partition. Syntax

MEDIAN(numeric_column)

Examples In each partition, every record will display the median value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MEDIAN(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEV

Calculates the standard deviation of a numerical column per partition. Syntax

STDEV(numeric_column)

Examples In each partition, every record will display the standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEV(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEVP

Calculates the population standard deviation of a numerical column per partition. Syntax

STDEVP(numeric_column)

Examples In each partition, every record will display the population standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEVP(Earnings) OVER (PARTITION BY Role) FROM Employees

VAR

Calculates the statistical standard variance of a numerical column per partition. Syntax

VAR(numeric_column)

Examples In each partition, every record will display the statistical standard variance of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VAR(Earnings) OVER (PARTITION BY Role) FROM Employees

VARP

Calculates the variance population of a numerical column per partition. Syntax

VARP(numeric_column)

Examples In each partition, every record will display the variance population of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VARP(Earnings) OVER (PARTITION BY Role) FROM Employees

Ranking Functions

These window functions rank records that fall within the window and its partitions.

RANK

Assigns a rank number to each record in a window based on the value of the column specified in the required ORDER BY clause. If two or more records have an equal value in the ranked column, they all receive the same rank number and the rank count increments internally, skipping ahead one rank number for each record with a duplicate value in the ORDER BY column. Syntax

RANK()

Examples

SELECT Id, FullName, RANK() OVER (ORDER BY FullName) AS Rank FROM Lead

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Id, FullName, RANK() OVER (PARTITION BY Id ORDER BY FullName) AS Rank FROM Lead

DENSE_RANK

Operates like the RANK() function, but it doesn’t increment the internal rank counter for each record with a duplicate value in the ranked column. This means that, while records with identical values in the ORDER BY column still share a rank number, the function never skips a rank number. Syntax

DENSE_RANK()

Examples

SELECT Id, FullName, DENSE_RANK() OVER (PARTITION BY Id ORDER BY FullName) AS Rank FROM Lead

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Id, FullName, DENSE_RANK() OVER (PARTITION BY Id ORDER BY FullName) AS Rank FROM Lead

ROW_NUMBER

Calculates a row number for each record. An ORDER BY clause in the OVER clause is required. Syntax

ROW_NUMBER()

Examples

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (ORDER BY Role) FROM Employees

If you define multiple partitions with PARTITION BY, a new set of row numbers are calculated for each partition.

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Earnings) FROM Employees

NTILE

Distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs. Syntax

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Parameters

buckets The number of buckets into which the rows are divided. The buckets can be an expression or subquery that evaluates to a positive integer. It cannot be a window function.
PARTITION BY Distributes rows of a result set into partitions to which the NTILE() function is applied.
ORDER BY Clause that specifies the logical order of rows in each partition to which the NTILE() is applied.

If the number of rows is not divisible by the buckets, the NTILE() function returns groups of two sizes with the difference by one. The larger groups always precede the smaller group in the order set by ORDER BY in the OVER() clause. If the total of rows is divisible by the number of buckets, the function divides the rows evenly among buckets. Examples The following statement creates a new table named ntile_demo that stores 10 integers:

CREATE TABLE sales.ntile_demo (
    v INT NOT NULL
);
     
INSERT INTO sales.ntile_demo(v)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
     
SELECT * FROM sales.ntile_demo;

This statement uses the NTILE() function to divide ten rows into three groups:
SELECT
    v,
    NTILE (3) OVER (
        ORDER BY v
    ) buckets
FROM
    sales.ntile_demo;

Analytical Functions

These window functions perform analytical operations on the records within the window.

PERCENT_RANK

Calculates the relative rank SQL Percentile of each row. It returns values greater than zero, but the maximum value is one. It does not count any NULL values. This function is nondeterministic. Syntax

PERCENT_RANK() OVER (
      [PARTITION BY partition_expression, ... ]
      ORDER BY sort_expression [ASC | DESC], ...
  )

Parameters

PARTITION BY By default, SQL Server treats the whole data set as a single set. You can specify the PARTITION BY clause to divide data into multiple sets. The Percent_Rank function performs the analytical calculations on each set. This parameter is optional.
ORDER BY Sorts the data in either ascending or descending order. This parameter is required.

Overview

DDL Commands

SQL Commands

SQL Functions

Window Function Clauses

OVER

PARTITION BY

Math Functions

COUNT

COUNT_BIG

MIN

MAX

SUM

AVG

MEDIAN

STDEV

STDEVP

VAR

VARP

Ranking Functions

RANK

DENSE_RANK

ROW_NUMBER

NTILE

Analytical Functions

PERCENT_RANK

​Window Function Clauses

​OVER

​PARTITION BY

​Math Functions

​COUNT

​COUNT_BIG

​MIN

​MAX

​SUM

​AVG

​MEDIAN

​STDEV

​STDEVP

​VAR

​VARP

​Ranking Functions

​RANK

​DENSE_RANK

​ROW_NUMBER

​NTILE

​Analytical Functions

​PERCENT_RANK

Window Function Clauses

OVER

PARTITION BY

Math Functions

COUNT

COUNT_BIG

MIN

MAX

SUM

AVG

MEDIAN

STDEV

STDEVP

VAR

VARP

Ranking Functions

RANK

DENSE_RANK

ROW_NUMBER

NTILE

Analytical Functions

PERCENT_RANK