Skip to content

Latest commit

 

History

History
129 lines (94 loc) · 5.56 KB

percentile-disc-transact-sql.md

File metadata and controls

129 lines (94 loc) · 5.56 KB
titledescriptionauthorms.authorms.datems.servicems.subservicems.topicf1_keywordshelpviewer_keywordsdev_langsmonikerRange
PERCENTILE_DISC (Transact-SQL)
PERCENTILE_DISC (Transact-SQL)
MikeRayMSFT
mikeray
10/20/2015
sql
t-sql
reference
PERCENTILE_DISC
PERCENTILE_DISC_TSQL
PERCENTILE_DISC function
analytic functions,PERCENTILE_DISC
TSQL
>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current||=fabric

PERCENTILE_DISC (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

Computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions in [!INCLUDEssNoVersion]. For a given percentile value P, PERCENTILE_DISC sorts the expression values in the ORDER BY clause. It then returns the value with the smallest CUME_DIST value given (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 column value.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

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 values to sort and compute the percentile over. Only one order_by_expression is allowed. The default sort order is ascending. The list of values can be of any of the data types that are valid for the sort operation.

OVER ( <partition_by_clause>)**
Divides the FROM clause's result set into partitions. The percentile function is applied to these partitions. For more information, see OVER Clause (Transact-SQL). The <ORDER BY clause> and <rows or range clause>can't 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. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

General Remarks

Any nulls in the data set are ignored.

PERCENTILE_DISC is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.

Examples

Basic syntax example

The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find each department's median employee salary. They may not return the same value:

  • PERCENTILE_CONT returns the appropriate value, even if it doesn't exist in the data set.
  • PERCENTILE_DISC returns an actual set value.
USE AdventureWorks2022; SELECT DISTINCT Name AS DepartmentName ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BYph.Rate) OVER (PARTITION BY Name) AS MedianCont ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BYph.Rate) OVER (PARTITION BY Name) AS MedianDisc FROMHumanResources.DepartmentAS d INNER JOINHumanResources.EmployeeDepartmentHistoryAS dh ONdh.DepartmentID=d.DepartmentIDINNER JOINHumanResources.EmployeePayHistoryAS ph ONph.BusinessEntityID=dh.BusinessEntityIDWHEREdh.EndDate IS NULL;

Here is a partial result set.

DepartmentName MedianCont MedianDisc Document Control 16.8269 16.8269 Engineering 34.375 32.6923 Executive 54.32695 48.5577 Human Resources 17.427850 16.5865

Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]

Basic syntax example

The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find each department's median employee salary. They may not return the same value:

  • PERCENTILE_CONT returns the appropriate value, even if it doesn't exist in the data set.
  • PERCENTILE_DISC returns an actual set value.
-- Uses AdventureWorks SELECT DISTINCT DepartmentName ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY BaseRate) OVER (PARTITION BY DepartmentName) AS MedianCont ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY BaseRate) OVER (PARTITION BY DepartmentName) AS MedianDisc FROMdbo.DimEmployee;

Here is a partial result set.

DepartmentName MedianCont MedianDisc -------------------- ---------- ---------- Document Control 16.826900 16.8269 Engineering 34.375000 32.6923 Human Resources 17.427850 16.5865 Shipping and Receiving 9.250000 9.0000

See Also

PERCENTILE_CONT (Transact-SQL)

close