PERCENTILE_DISC (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions in SQL Server. 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.
Transact-SQL syntax conventions
Syntax
PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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 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
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
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
FROM dbo.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
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho