Edit

Analytic functions (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Azure SQL Edge SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

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. Use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

The Microsoft SQL Database Engine provides the following analytic functions in some or all platforms. Refer to each syntax article for applicable platforms.

  • ANY_VALUE - Returns any non-NULL value from a group of rows, or NULL if all values are NULL.
  • APPROX_MEDIAN - Returns an approximate median value for a set of values.
  • APPROX_QUANTILE - Returns an approximate quantile value for a specified quantile position.
  • CUME_DIST - Calculates the cumulative distribution of a value within a group of values.
  • FIRST_VALUE - Returns the first value in an ordered set of values.
  • LAG - Returns a value from a previous row in the same result set without requiring a self-join.
  • LAST_VALUE - Returns the last value in an ordered set of values.
  • LEAD - Returns a value from a subsequent row in the same result set without requiring a self-join.
  • MEDIAN - Returns the median value of the values in a group.
  • PERCENT_RANK - Calculates the relative rank of a row within a group of rows.
  • PERCENTILE_CONT - Calculates a percentile based on a continuous distribution, interpolating a result that might not equal a value in the group.
  • PERCENTILE_DISC - Calculates a percentile based on a discrete distribution, returning a value from the group.
  • QUANTILE - Returns the value corresponding to a specified quantile within a group.

Related content

  • Last updated on