首页 > 代码库 > SQL Server ->> PERCENTILE_CONT、PERCENTILE_DISC 和 PERCENT_RANK 函数

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

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)

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