Analytic Functions
Overview
Analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions can return multiple rows for each group. The functions covered in this section are:
CUME_DIST
CUME_DIST calculates the relative position of a specified value in a group of values.
Return Type
FLOAT(53)
Syntax
CUME_DIST( )
OVER ( [ partition_by_clause ] order_by_clause )
Arguments
| Argument | Description | Example |
|---|---|---|
OVER | Determines the partitioning and ordering of a rowset before the associated window function is applied. | |
PARTITION BY | This optional clause divides the result set into partitions to which the function is applied. | |
ORDER BY | Determines the logical order in which the operation is performed. | Salary |
Example
This example returns a randomly generated, 60-byte number.
SELECT
Name,
Department,
CUME_DIST() OVER(ORDER BY Salary) AS percent_rank
FROM [Documentation].[Customer]
FIRST_VALUE
FIRST_VALUE returns the first value in an ordered set of values.
Return Type
Returns the same type as the expression.
Syntax
FIRST_VALUE ( [expression] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [partition_by] order_by [rows_range] )
Arguments
| Argument | Description | Example |
|---|---|---|
expression | A column, subquery, or expression to return the first value of. | Age |
IGNORE/RESPECT NULLS | An optional argument that can be used to specify whether nulls will be included when calculating the first value. If no argument is included, RESPECT NULLS will be used by default. | |
PARTITION BY | Optional. 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 | Required. Determines the logical order in which the operation is performed. | Salary |
ROWS_RANGE | Optional. Limits the rows within the partition by specifying start and end points. |
Example
This example the first (lowest) value from the Salary column of the [HR].[Employees] table.
SELECT
FIRST_VALUE(Salary)
OVER(ORDER BY Salary)
AS BottomRange
FROM [HR].[Employee]
LAST_VALUE
LAST_VALUE returns the last value in an ordered set of values.
Return Type
Returns the same type as the expression.
Syntax
LAST_VALUE ( [expression] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [partition_by] order_by [rows_range] )
Arguments
| Argument | Description | Example |
|---|---|---|
expression | A column, subquery, or expression to return the last value of. | Age |
IGNORE/RESPECT NULLS | An optional argument that can be used to specify whether nulls will be included when calculating the last value. If no argument is included, RESPECT NULLS will be used by default. | |
PARTITION BY | Optional. 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 | Required. Determines the logical order in which the operation is performed. | Salary |
ROWS_RANGE | Optional. Limits the rows within the partition by specifying start and end points. |
Example
This example the last (highest) value from the Salary column of the [HR].[Employees] table.
SELECT
LAST_VALUE(Salary)
OVER(ORDER BY Salary)
AS TopRange
FROM [HR].[Employee]