• Window Function Clauses
  • OVER
  • PARTITION BY
• Math Functions
  • COUNT
    • Syntax
    • Example(s)
  • COUNT_BIG
    • Syntax
    • Example(s)
  • MIN
    • Syntax
    • Example(s)
  • MAX
    • Syntax
    • Example(s)
  • SUM
    • Syntax
    • Exaqmple(s)
  • AVG
    • Syntax
    • Example(s)
  • MEDIAN
    • Syntax
    • Example(s)
  • STDEV
    • Syntax
    • Example(s)
  • STDEVP
    • Syntax
    • Example(s)
  • VAR
    • Syntax
    • Example(s)
  • VARP
    • Syntax
    • Example(s)
• Ranking Functions
  • RANK
    • Syntax
    • Example(s)
  • DENSE_RANK
    • Syntax
    • Example(s)
  • ROW_NUMBER
    • Syntax
    • Example(s)
  • NTILE
    • Syntax
    • Parameters
    • Example(s)
• Analytical Functions
  • PERCENT_RANK
    • Syntax
    • Parameters

Window functions allow you to create computed fields from a group of rows (a window) that return a result for each row, as opposed to one computed result for a set of rows, as is the case with aggregate functions. The driver supports the following window function syntax.

Note: 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()

Example(s)

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()

Example(s)

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)

Example(s)

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)

Example(s)

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)

Exaqmple(s)

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)

Example(s)

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)

Example(s)

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)

Example(s)

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)

Example(s)

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)

Example(s)

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)

Example(s)

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 in 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()

Example(s)

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()

Example(s)

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()

Example(s)

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.

Example(s)

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.