Skip to content

Latest commit

 

History

History
131 lines (95 loc) · 5.48 KB

percentile-cont-transact-sql.md

File metadata and controls

131 lines (95 loc) · 5.48 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
PERCENTILE_CONT (Transact-SQL)
PERCENTILE_CONT calculates a percentile based on a continuous distribution of the column value.
MikeRayMSFT
mikeray
randolphwest
05/23/2024
sql
t-sql
reference
PERCENTILE_CONT_TSQL
PERCENTILE_CONT
analytic functions, PERCENTILE_CONT
PERCENTILE_CONT function
TSQL
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

PERCENTILE_CONT (Transact-SQL)

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

Calculates a percentile based on a continuous distribution of the column value in the [!INCLUDE ssdenoversion-md]. The result is interpolated, and might not equal any of the specific values in the column.

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

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 )

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 or approximate numeric type, with no other data types allowed. Exact numeric types are int, bigint, smallint, tinyint, numeric, bit, decimal, smallmoney, and money. Approximate numeric types are float and real. 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 SELECT - OVER Clause. The ORDER BY clause and <rows or range clause> of the OVER syntax can't be specified in a PERCENTILE_CONT function.

Return types

float(53)

Compatibility support

WITHIN GROUP is a reserved keyword, starting with compatibility level 110. For more information, see ALTER DATABASE Compatibility Level.

Remarks

Any nulls in the data set are ignored.

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

Examples

[!INCLUDE article-uses-adventureworks]

A. Find median employee salary per department

The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find the median employee salary in each department. These functions might not return the same value. PERCENTILE_CONT interpolates the appropriate value, which might or might not exist in the data set, while PERCENTILE_DISC always returns an actual value from the set.

USE AdventureWorks2022;
GO

SELECT DISTINCT Name AS DepartmentName,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate)
        OVER (PARTITION BY Name) AS MedianCont,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate)
        OVER (PARTITION BY Name) AS MedianDisc
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh
    ON dh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory AS ph
    ON ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL;

Here's 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]

The following example uses the AdventureWorksDW2012 sample database.

B. Find median employee salary per department

The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find the median employee salary in each department. These functions might not return the same value. PERCENTILE_CONT interpolates the appropriate value, which might or might not exist in the data set, while PERCENTILE_DISC always returns an actual value from the set.

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
FROM dbo.DimEmployee;

Here's 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

Related content