SQL实现CMPC表按AGE、ESPAC分组计算5-95分位数内变量统计值

原代码错误原因 #

• 子句顺序错误：SQL执行顺序要求FROM在前，GROUP BY在SELECT之后
• 聚合函数用法错误：AVG/MAX/MIN/STDEV单次仅支持计算单个字段，不能同时传入多个字段
• 字段类型不匹配：建表语句中NDVI、SAVI等植被指数字段为VARCHAR字符串类型，需转换为数值类型才可参与计算
• 缺少分位数过滤逻辑：未将各字段值限制在5-95分位数区间内再聚合

正确实现代码 #

以下为适配PostgreSQL、MySQL 8.0+等主流数据库的标准SQL实现：

WITH percentile_calc AS (
    -- 计算各指标全局5%、95%分位数
    SELECT
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY B2) AS B2_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY B2) AS B2_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY B3) AS B3_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY B3) AS B3_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY B4) AS B4_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY B4) AS B4_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY B8) AS B8_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY B8) AS B8_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(NDVI AS DECIMAL(18,6))) AS NDVI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(NDVI AS DECIMAL(18,6))) AS NDVI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(SAVI AS DECIMAL(18,6))) AS SAVI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(SAVI AS DECIMAL(18,6))) AS SAVI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(SIPI AS DECIMAL(18,6))) AS SIPI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(SIPI AS DECIMAL(18,6))) AS SIPI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(SR AS DECIMAL(18,6))) AS SR_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(SR AS DECIMAL(18,6))) AS SR_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(RGI AS DECIMAL(18,6))) AS RGI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(RGI AS DECIMAL(18,6))) AS RGI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY TVI) AS TVI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY TVI) AS TVI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(MSR AS DECIMAL(18,6))) AS MSR_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(MSR AS DECIMAL(18,6))) AS MSR_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(PRI AS DECIMAL(18,6))) AS PRI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(PRI AS DECIMAL(18,6))) AS PRI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(GNDVI AS DECIMAL(18,6))) AS GNDVI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(GNDVI AS DECIMAL(18,6))) AS GNDVI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(PSRI AS DECIMAL(18,6))) AS PSRI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(PSRI AS DECIMAL(18,6))) AS PSRI_p95,
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY CAST(GCI AS DECIMAL(18,6))) AS GCI_p5,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(GCI AS DECIMAL(18,6))) AS GCI_p95
    FROM CMPC
),
filtered_data AS (
    -- 过滤出各字段在5-95分位数区间内的行，同时完成类型转换
    SELECT
        c.AGE,
        c.ESPAC,
        c.B2,
        c.B3,
        c.B4,
        c.B8,
        CAST(c.NDVI AS DECIMAL(18,6)) AS NDVI,
        CAST(c.SAVI AS DECIMAL(18,6)) AS SAVI,
        CAST(c.SIPI AS DECIMAL(18,6)) AS SIPI,
        CAST(c.SR AS DECIMAL(18,6)) AS SR,
        CAST(c.RGI AS DECIMAL(18,6)) AS RGI,
        c.TVI,
        CAST(c.MSR AS DECIMAL(18,6)) AS MSR,
        CAST(c.PRI AS DECIMAL(18,6)) AS PRI,
        CAST(c.GNDVI AS DECIMAL(18,6)) AS GNDVI,
        CAST(c.PSRI AS DECIMAL(18,6)) AS PSRI,
        CAST(c.GCI AS DECIMAL(18,6)) AS GCI
    FROM CMPC c
    CROSS JOIN percentile_calc p
    WHERE 
        c.B2 BETWEEN p.B2_p5 AND p.B2_p95
        AND c.B3 BETWEEN p.B3_p5 AND p.B3_p95
        AND c.B4 BETWEEN p.B4_p5 AND p.B4_p95
        AND c.B8 BETWEEN p.B8_p5 AND p.B8_p95
        AND CAST(c.NDVI AS DECIMAL(18,6)) BETWEEN p.NDVI_p5 AND p.NDVI_p95
        AND CAST(c.SAVI AS DECIMAL(18,6)) BETWEEN p.SAVI_p5 AND p.SAVI_p95
        AND CAST(c.SIPI AS DECIMAL(18,6)) BETWEEN p.SIPI_p5 AND p.SIPI_p95
        AND CAST(c.SR AS DECIMAL(18,6)) BETWEEN p.SR_p5 AND p.SR_p95
        AND CAST(c.RGI AS DECIMAL(18,6)) BETWEEN p.RGI_p5 AND p.RGI_p95
        AND c.TVI BETWEEN p.TVI_p5 AND p.TVI_p95
        AND CAST(c.MSR AS DECIMAL(18,6)) BETWEEN p.MSR_p5 AND p.MSR_p95
        AND CAST(c.PRI AS DECIMAL(18,6)) BETWEEN p.PRI_p5 AND p.PRI_p95
        AND CAST(c.GNDVI AS DECIMAL(18,6)) BETWEEN p.GNDVI_p5 AND p.GNDVI_p95
        AND CAST(c.PSRI AS DECIMAL(18,6)) BETWEEN p.PSRI_p5 AND p.PSRI_p95
        AND CAST(c.GCI AS DECIMAL(18,6)) BETWEEN p.GCI_p5 AND p.GCI_p95
)
-- 按AGE、ESPAC分组计算各指标的统计值
SELECT
    AGE,
    ESPAC,
    AVG(B2) AS B2_mean,
    MAX(B2) AS B2_max,
    MIN(B2) AS B2_min,
    STDDEV(B2) AS B2_sd,
    AVG(B3) AS B3_mean,
    MAX(B3) AS B3_max,
    MIN(B3) AS B3_min,
    STDDEV(B3) AS B3_sd,
    AVG(B4) AS B4_mean,
    MAX(B4) AS B4_max,
    MIN(B4) AS B4_min,
    STDDEV(B4) AS B4_sd,
    AVG(B8) AS B8_mean,
    MAX(B8) AS B8_max,
    MIN(B8) AS B8_min,
    STDDEV(B8) AS B8_sd,
    AVG(NDVI) AS NDVI_mean,
    MAX(NDVI) AS NDVI_max,
    MIN(NDVI) AS NDVI_min,
    STDDEV(NDVI) AS NDVI_sd,
    AVG(SAVI) AS SAVI_mean,
    MAX(SAVI) AS SAVI_max,
    MIN(SAVI) AS SAVI_min,
    STDDEV(SAVI) AS SAVI_sd,
    AVG(SIPI) AS SIPI_mean,
    MAX(SIPI) AS SIPI_max,
    MIN(SIPI) AS SIPI_min,
    STDDEV(SIPI) AS SIPI_sd,
    AVG(SR) AS SR_mean,
    MAX(SR) AS SR_max,
    MIN(SR) AS SR_min,
    STDDEV(SR) AS SR_sd,
    AVG(RGI) AS RGI_mean,
    MAX(RGI) AS RGI_max,
    MIN(RGI) AS RGI_min,
    STDDEV(RGI) AS RGI_sd,
    AVG(TVI) AS TVI_mean,
    MAX(TVI) AS TVI_max,
    MIN(TVI) AS TVI_min,
    STDDEV(TVI) AS TVI_sd,
    AVG(MSR) AS MSR_mean,
    MAX(MSR) AS MSR_max,
    MIN(MSR) AS MSR_min,
    STDDEV(MSR) AS MSR_sd,
    AVG(PRI) AS PRI_mean,
    MAX(PRI) AS PRI_max,
    MIN(PRI) AS PRI_min,
    STDDEV(PRI) AS PRI_sd,
    AVG(GNDVI) AS GNDVI_mean,
    MAX(GNDVI) AS GNDVI_max,
    MIN(GNDVI) AS GNDVI_min,
    STDDEV(GNDVI) AS GNDVI_sd,
    AVG(PSRI) AS PSRI_mean,
    MAX(PSRI) AS PSRI_max,
    MIN(PSRI) AS PSRI_min,
    STDDEV(PSRI) AS PSRI_sd,
    AVG(GCI) AS GCI_mean,
    MAX(GCI) AS GCI_max,
    MIN(GCI) AS GCI_min,
    STDDEV(GCI) AS GCI_sd
FROM filtered_data
GROUP BY AGE, ESPAC
ORDER BY AGE, ESPAC;

兼容说明 #

如果使用的数据库不支持PERCENTILE_CONT函数，可替换为对应数据库的分位数计算函数：

• Hive/Spark SQL：替换为PERCENTILE(字段名, 分位数值)，大数据量场景可使用PERCENTILE_APPROX提升性能
• MySQL 8.0以下：需自定义分位数计算逻辑，或先导出数据在统计工具中计算分位数后再过滤

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