SQL Server ->> PERCENTILE_CONT、PERCENTILE_DISC 和 PERCENT_RANK 函数

时间:2023-11-24 14:45:38

PERCENTILE_CONT和PERCENTILE_DISC都是为了计算百分位的数值,比如计算在某个百分位时某个栏位的数值是多少。他们的区别就是前者是连续型,后者是离散型。CONT代表continuous,DISC代表discrete。PERCENTILE_CONT是连续型意味它考虑的是区间,所以值是绝对的中间值。而PERCENTILE_DISC是离散型,所以它更多考虑向上或者向下取舍,而不会考虑区间。下面是MSDN的一个例子:

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;

PERCENT_RANK虽然也带有PERCENT,但是和前两个没有什么关系。它是为了计算当前值按百分比计算所处在百分位位置。

参考:

PERCENTILE_CONT (Transact-SQL)

PERCENTILE_DISC (Transact-SQL)

PERCENT_RANK (Transact-SQL)