PERCENTILE_CONT (Transact-SQL)

مقالة
05/16/2013

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

Topic link icon Transact-SQL Syntax Conventions (Transact-SQL)

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, 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.

Examples

A. Basic syntax example

The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find the median employee salary in each department. Note that these functions may not return the same value. This is because PERCENTILE_CONT interpolates the appropriate value, whether or not it exists in the data set, while PERCENTILE_DISC always returns an actual value from the set.

USE AdventureWorks2012;

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 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

مشاركة عبر