在SQL中计算分组数据的LQ、UQ及四分位距IQR的实现方法

实现逻辑 #

• 下四分位数LQ对应该组Data列的第25百分位值
• 上四分位数UQ对应该组Data列的第75百分位值
• 四分位距IQR = UQ - LQ
• 分组计算只需将分组字段ID加入GROUP BY子句，百分位计算会自动在每个ID的分组内独立执行

主流SQL引擎通用写法 #

支持MySQL 8.0+、PostgreSQL、Spark SQL、Hive、Flink SQL等绝大多数支持标准SQL百分位函数的引擎，计算结果和你给出的预期完全一致：

SELECT
    ID,
    -- 计算25%分位即下四分位数
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Data) AS LQ,
    -- 计算75%分位即上四分位数
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Data) AS UQ,
    -- 计算四分位距
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Data) - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Data) AS IQR
FROM 你的表名
GROUP BY ID
ORDER BY ID;

说明：PERCENTILE_CONT为连续百分位函数，会基于排序后的数据做插值计算，返回连续数值；如果需要返回分组内真实存在的Data值，可以替换为PERCENTILE_DISC函数。

PostgreSQL优化写法 #

PostgreSQL支持一次性返回多个百分位结果，减少重复计算：

SELECT
    ID,
    quantile_arr[1] AS LQ,
    quantile_arr[2] AS UQ,
    quantile_arr[2] - quantile_arr[1] AS IQR
FROM (
    SELECT
        ID,
        PERCENTILE_CONT(ARRAY[0.25, 0.75]) WITHIN GROUP (ORDER BY Data) AS quantile_arr
    FROM 你的表名
    GROUP BY ID
) t
ORDER BY ID;

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