SQL Server如何计算精确的Lower和Upper Quartiles（上下四分位数）

问题根因 #

你当前用NTILE函数计算四分位数不符合预期的核心原因是：NTILE仅会将排序后的数据集平均拆分为指定数量的分组，当数据行数无法被4整除时会优先给前几个分组多分配行，且完全不支持插值计算，所以和你手动用插值法得到的结果存在偏差。

最优解决方案（SQL Server 2012及以上版本） #

直接使用SQL Server内置的PERCENTILE_CONT函数，该函数专门用于计算连续百分位数，默认采用线性插值法计算结果，完全符合你的需求。
仅返回产品号、上下四分位数的查询语句如下：

SELECT DISTINCT
    ProductNumber,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY AveragePrice) OVER (PARTITION BY ProductNumber) AS Quartile_1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY AveragePrice) OVER (PARTITION BY ProductNumber) AS Quartile_3
FROM #temp_products
ORDER BY ProductNumber

如果需要同时返回最小值、最大值、数据条数，可以扩展为：

SELECT DISTINCT
    ProductNumber,
    MIN(AveragePrice) OVER (PARTITION BY ProductNumber) AS Minimum,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY AveragePrice) OVER (PARTITION BY ProductNumber) AS Quartile_1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY AveragePrice) OVER (PARTITION BY ProductNumber) AS Quartile_3,
    MAX(AveragePrice) OVER (PARTITION BY ProductNumber) AS Maximum,
    COUNT(1) OVER (PARTITION BY ProductNumber) AS [Number of items]
FROM #temp_products
ORDER BY ProductNumber

低版本兼容解决方案（SQL Server 2008及以下） #

如果你的数据库版本不支持PERCENTILE_CONT，可以手动实现插值逻辑：

WITH RankedData AS (
    SELECT 
        ProductNumber,
        AveragePrice,
        ROW_NUMBER() OVER (PARTITION BY ProductNumber ORDER BY AveragePrice) AS rn,
        COUNT(1) OVER (PARTITION BY ProductNumber) AS total_cnt
    FROM #temp_products
)
SELECT 
    ProductNumber,
    -- 计算Q1插值
    AVG(CASE 
        WHEN rn = FLOOR((total_cnt + 1) * 0.25) OR rn = CEILING((total_cnt + 1) * 0.25) 
        THEN AveragePrice 
        END) AS Quartile_1,
    -- 计算Q3插值
    AVG(CASE 
        WHEN rn = FLOOR((total_cnt + 1) * 0.75) OR rn = CEILING((total_cnt + 1) * 0.75) 
        THEN AveragePrice 
        END) AS Quartile_3
FROM RankedData
GROUP BY ProductNumber
ORDER BY ProductNumber

拿你提供的2号产品5条测试数据验证，以上两种方案返回的Q1为45.8981、Q3为61.41535，和你手动计算的结果完全一致。

内容的提问来源于stack exchange，提问作者LordSilvermort