PostgreSQL 9.6按类别求中位数及标准差偏移值的实现

嘿，针对你在PostgreSQL 9.6里的需求，我来帮你调整代码，解决标准差处理和百分位区间的问题。首先明确几个关键函数：PostgreSQL 9.6提供了stddev_samp（样本标准差）和stddev_pop（总体标准差）聚合函数，同时用percentile_cont/percentile_disc可以轻松计算中位数和任意百分位。下面是整合后的完整代码：

WITH category_stats AS (
    SELECT
        spec_catcode AS scat_code,
        MAX(unadjustedprice) AS max_sqm_rate,
        MIN(unadjustedprice) AS min_sqm_rate,
        COUNT(unadjustedprice) AS sample_no,
        AVG(unadjustedprice) AS avg_rate,
        -- 用标准百分位函数计算中位数，如果你有自定义median函数可以直接替换这里
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY unadjustedprice) AS median_rate,
        -- 样本标准差，若你的数据是全量总体，替换为stddev_pop即可
        STDDEV_SAMP(unadjustedprice) AS std_dev,
        -- 示例：计算25th和75th百分位（四分位区间），可自定义其他百分位
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY unadjustedprice) AS p25_rate,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY unadjustedprice) AS p75_rate
    FROM processed_data.d_voa_record1
    GROUP BY spec_catcode
)
SELECT
    scat_code,
    max_sqm_rate,
    min_sqm_rate,
    sample_no,
    avg_rate,
    median_rate,
    std_dev,
    -- 中位数±1倍、2倍标准差的计算
    median_rate - std_dev AS median_minus_1std,
    median_rate + std_dev AS median_plus_1std,
    median_rate - 2 * std_dev AS median_minus_2std,
    median_rate + 2 * std_dev AS median_plus_2std,
    -- 百分位区间相关字段
    p25_rate,
    p75_rate,
    p75_rate - p25_rate AS iqr -- 可选：四分位距，用于异常值判断
FROM category_stats;

关键细节说明： #

• 中位数计算：我用了PERCENTILE_CONT(0.5)，这是连续型的中位数计算（适合数值型数据）；如果你习惯离散型结果，可以换成PERCENTILE_DISC(0.5)，二者差异在于离散型会返回数据中实际存在的数值。如果你的自定义median函数已经验证过正确性，直接替换掉这行即可。
• 标准差选择：STDDEV_SAMP适用于抽样数据，STDDEV_POP适用于全量总体数据，根据你的数据集类型选择。
• 百分位扩展：如果需要其他百分位区间（比如10th和90th），只需新增PERCENTILE_CONT(0.1)...和PERCENTILE_CONT(0.9)...即可，非常灵活。
• NULL值处理：所有聚合函数会自动排除unadjustedprice为NULL的行，和你原代码逻辑一致。如果分组只有1条数据，标准差会返回NULL，此时±标准差的结果也会是NULL，你可以用COALESCE(median_rate - std_dev, median_rate)来替换，让这种情况直接返回中位数。

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