在sql server 2022中,可以使用内置的 percentile_cont 和 percentile_disc 函数来计算连续和离散的分位数。这两个函数属于窗口函数,需要配合 over() 子句使用,并指定排序依据。通过设置 filter 条件或结合 group by,可以实现对特定数据组的分位数聚合计算。

如何计算连续分位数 PERCENTILE_CONT
PERCENTILE_CONT 用于计算连续分布的分位数,如果实际数据点不正好位于请求的分位点上,它会对相邻的两个值进行线性插值。使用时必须指定一个 WITHIN GROUP (ORDER BY ...) 子句来定义排序顺序,以及一个 OVER() 子句(如果是作为窗口函数)或直接作为聚合函数使用(在SQL Server 2022中支持直接聚合)。以下代码演示了如何计算销售额的中位数(50%分位数)。

SELECT
Category,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Amount) AS MedianAmount
FROM Sales
GROUP BY Category;上述查询按类别分组,计算每类销售额的中位数。如果某类别下只有奇数个记录,结果将是中间那个值;如果有偶数个记录,则是中间两个值的平均值。这提供了比简单平均数更稳健的中心趋势度量。
如何计算离散分位数 PERCENTILE_DISC
PERCENTILE_DISC 计算离散分布的分位数,它总是返回数据集中存在的某个实际值。与 PERCENTILE_CONT 不同,它不进行插值。当请求的分位数位置落在两个数据点之间时,它会选择较大的那个值(即向上取整的逻辑)。以下代码展示了如何计算每个部门员工薪资的第90百分位数。

SELECT
DepartmentID,
PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY Salary) AS P90Salary
FROM Employees
GROUP BY DepartmentID;此查询为每个部门找出第90百分位的薪资水平。这意味着至少有90%的员工薪资低于或等于这个值。由于是离散函数,返回值必然是表中存在的某个具体薪资数额,不会像连续函数那样产生插值结果。
基于输入数据为空时的处理逻辑
当输入数据组为空或所有值都相同时,分位数函数的行为需要特别注意。对于 PERCENTILE_CONT,如果组内没有数据,它将返回 NULL。如果组内只有一个唯一值,无论请求什么分位数,结果都是该值本身,因为不需要插值。对于 PERCENTILE_DISC,同样在空组时返回 NULL。但在单值情况下,它也返回该值。验证这一行为的代码如下:
SELECT
CASE WHEN COUNT(*) = 0 THEN 'Empty' ELSE 'Has Data' END AS GroupStatus,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Value) AS ContResult,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Value) AS DiscResult
FROM (
SELECT CAST(NULL AS INT) AS Value WHERE 1=0 -- Empty set simulation
UNION ALL
SELECT 10 AS Value -- Single value
) AS TestGroup
GROUP BY GroupStatus;运行结果显示,空组返回 NULL,单值组返回该值。开发者在处理可能包含空数据的业务场景时,应使用 ISNULL 或 COALESCE 函数对这些 NULL 结果进行默认值替换,以避免下游应用出现意外错误。

















