SqlServer计算中位数函数

SqlServer计算中位数函数

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 求中位数的列名) OVER (PARTITION BY 分组字段)

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY 求中位数的列名) OVER (PARTITION BY 分组字段)

A. 基本语法示例
下面的示例使用 PERCENTILE_CONT 和 PERCENTILE_DISC 函数找出每个部门内雇员的薪金中值。 这些函数可能返回不同的值。 PERCENTILE_CONT 内插适当的值，它在数据集中可能存在，也可能不存在，而 PERCENTILE_DISC 始终从数据集中返回实际值。

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;  

以下为部分结果集。

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

B. 基本语法示例
下面的示例使用 PERCENTILE_CONT 和 PERCENTILE_DISC 函数找出每个部门内雇员的薪金中值。 这些函数可能返回不同的值。 PERCENTILE_CONT 内插适当的值，它在数据集中可能存在，也可能不存在，而 PERCENTILE_DISC 始终从数据集中返回实际值。

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

以下为部分结果集。

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