Analytical Func

SQL Server 2016 supports the following analytic functions. Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

CUME_DIST (SQL Server 2012)

Calculates the cumulative distribution of a value in a group of values in SQL Server. That is, CUME_DIST computes the relative position of a specified value in a group of values. For a row r, assuming ascending ordering, the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows evaluated in the partition or query result set. CUME_DIST is similar to the PERCENT_RANK function.

Syatax:

CUME_DIST( )

    OVER ( [ partition_by_clause ] order_by_clause )

Arguments

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

Order_by_clause determines the logical order in which the operation is performed. order_by_clause is required.

The <rows or range clause> of the OVER syntax cannot be specified in a CUME_DIST function.

Return Types:

float(53)

General Remarks:

The range of values returned by CUME_DIST is greater than 0 and less than or

equal to 1.

Tie values always evaluate to the same cumulative distribution value.

NULL values are included by default and are treated as the lowest possible values.

CUME_DIST is nondeterministic.

PERCENT_RANK (SQL Server 2012)

Calculates the relative rank of a row within a group of rows in SQL Server 2016. Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition.

PERCENT_RANK is similar to the CUME_DIST function.

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12.

Syntax

PERCENT_RANK( )

    OVER ( [ partition_by_clause ] order_by_clause )

Formula:

            PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)          

Arguments

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. The order_by_clause is required.

The <rows or range clause> of the OVER syntax cannot be specified in a PERCENT_RANK function.

Return Types

float(53)

General Remarks

The range of values returned by PERCENT_RANK is greater than 0 and less than or equal to 1. The first row in any set has a PERCENT_RANK of 0.

NULL values are included by default and are treated as the lowest possible values.

PERCENT_RANK is nondeterministic.

FIRST_VALUE (SQL Server 2012)

Returns the first value in an ordered set of values in SQL Server 2016.

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12.

Syntax

FIRST_VALUE ( [scalar_expression ] )

    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Arguments

scalar_expression

Is the value to be returned. scalar_expression can be a column, subquery, or other arbitrary expression that results in a single value. Other analytic functions are not permitted.

OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. rows_range_clause further limits the rows within the partition by specifying start and end points.

Return Types

Is the same type as scalar_expression.

General Remarks

FIRST_VALUE is nondeterministic.

LAST_VALUE (Transact-SQL)

Returns the last value in an ordered set of values in SQL Server 2016.

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12.

Syntax

LAST_VALUE ( [scalar_expression )

    OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

Arguments

Scalar_expression

Is the value to be returned. scalar_expression can be a column, subquery, or other expression that results in a single value. Other analytic functions are not permitted.

OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

Order_by_clause determines the order of the data before the function is applied. The order_by_clause is required. rows_range_clause further limits the rows within the partition by specifying start and end points.

Return Types

Is the same type as scalar_expression.

General Remarks

LAST_VALUE is nondeterministic.
LAG (Transact-SQL)

Applies To: Azure SQL Database, SQL Server 2014, SQL Server 2016 Preview

Accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2016. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12 , Azure SQL Data Warehouse Public Preview.

Syntax

LAG (scalar_expression [,offset] [,default])

    OVER ( [ partition_by_clause ] order_by_clause )

Arguments

scalar_expression

The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

offset

The number of rows back from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default

The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. If partition_by_clause is specified, it determines the order of the data in the partition. The order_by_clause is required.

Return Types

The data type of the specified scalar_expression. NULL is returned if scalar_expression is nullable or default is set to NULL.

General Remarks

LAG is nondeterministic.

LEAD (Transact-SQL)

Applies To: Azure SQL Database, SQL Server 2014, SQL Server 2016 Preview

Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12, Azure SQL Data Warehouse Public Preview.

Syntax

LEAD ( scalar_expression [ ,offset ] , [ default ] )

    OVER ( [ partition_by_clause ] order_by_clause )

Arguments

scalar_expression

The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

offset

The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default

The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. When partition_by_clause is specified, it determines the order of the data in each partition. The order_by_clause is required.

Return Types

The data type of the specified scalar_expression.

NULL is returned if scalar_expression is nullable or default is set to NULL.

LEAD is nondeterministic.

PERCENTILE_CONT (SQL Server 2012)

Calculates a percentile based on a continuous distribution of the column value in SQL Server. The result is interpolated and might not be equal to any of the specific values in the column.

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12, Azure SQL Data Warehouse Public Preview.

Syntax

PERCENTILE_CONT ( numeric_literal )

    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )

    OVER ( [ <partition_by_clause> ] )

Arguments

numeric_literal

The percentile to compute. The value must range between 0.0 and 1.0.

WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ])

Specifies a list of numeric values to sort and compute the percentile over. Only one order_by_expression is allowed. The expression must evaluate to an exact numeric type (int, bigint, smallint, tinyint, numeric, bit, decimal, smallmoney, money) or an approximate numeric type (float, real). Other data types are not allowed. The default sort order is ascending.

OVER ( <partition_by_clause> )

Divides the result set produced by the FROM clause into partitions to which the percentile function is applied. For more information, see OVER Clause (Transact-SQL). The <ORDER BY clause> and <rows or range clause> of the OVER syntax cannot be specified in a PERCENTILE_CONT function.

Return Types

float(53)

Compatibility Support

Under compatibility level 110 and higher, WITHIN GROUP is a reserved keyword.

General Remarks

Any nulls in the data set are ignored.

PERCENTILE_CONT is nondeterministic.

PERCENTILE_DISC (SQL Server 2012)

Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset in SQL Server. For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to P. For example, PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values; the result is equal to a specific value in the column.

Applies to: SQL Server (SQL Server 2012 through current version), SQL Database V12, Azure SQL Data Warehouse Public Preview.

Syntax

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )  OVER ( [ <partition_by_clause> ] )

Arguments

literal

The percentile to compute. The value must range between 0.0 and 1.0.

WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ])

Specifies a list of numeric values to sort and compute the percentile over. Only one

order_by_expression is allowed. The default sort order is ascending.

OVER ( <partition_by_clause> )

Divides the result set produced by the FROM clause into partitions to which the percentile function is applied. For more information, see OVER Clause (Transact-SQL). The <ORDER BY clause> and <rows or range clause>cannot be specified in a PERCENTILE_DISC function.

Return Types

The return type is determined by the order_by_expression type.

Compatibility Support

Under compatibility level 110 and higher, WITHIN GROUP is a reserved keyword.

General Remarks

Any nulls in the data set are ignored.

PERCENTILE_DISC is nondeterministic.