BigQuery按AGE、ESPAC分组计算5-95分位数内变量统计值SQL求助

可直接运行的SQL语句 #

WITH quantile_thresholds AS (
  SELECT 
    *,
    -- 计算各变量按AGE+ESPAC分组的5%、95%分位阈值
    PERCENTILE_CONT(B2, 0.05) OVER(PARTITION BY AGE, ESPAC) AS B2_p5,
    PERCENTILE_CONT(B2, 0.95) OVER(PARTITION BY AGE, ESPAC) AS B2_p95,
    PERCENTILE_CONT(B3, 0.05) OVER(PARTITION BY AGE, ESPAC) AS B3_p5,
    PERCENTILE_CONT(B3, 0.95) OVER(PARTITION BY AGE, ESPAC) AS B3_p95,
    PERCENTILE_CONT(B4, 0.05) OVER(PARTITION BY AGE, ESPAC) AS B4_p5,
    PERCENTILE_CONT(B4, 0.95) OVER(PARTITION BY AGE, ESPAC) AS B4_p95,
    PERCENTILE_CONT(B8, 0.05) OVER(PARTITION BY AGE, ESPAC) AS B8_p5,
    PERCENTILE_CONT(B8, 0.95) OVER(PARTITION BY AGE, ESPAC) AS B8_p95,
    PERCENTILE_CONT(NDVI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS NDVI_p5,
    PERCENTILE_CONT(NDVI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS NDVI_p95,
    PERCENTILE_CONT(SAVI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS SAVI_p5,
    PERCENTILE_CONT(SAVI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS SAVI_p95,
    PERCENTILE_CONT(SIPI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS SIPI_p5,
    PERCENTILE_CONT(SIPI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS SIPI_p95,
    PERCENTILE_CONT(SR, 0.05) OVER(PARTITION BY AGE, ESPAC) AS SR_p5,
    PERCENTILE_CONT(SR, 0.95) OVER(PARTITION BY AGE, ESPAC) AS SR_p95,
    PERCENTILE_CONT(RGI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS RGI_p5,
    PERCENTILE_CONT(RGI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS RGI_p95,
    PERCENTILE_CONT(TVI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS TVI_p5,
    PERCENTILE_CONT(TVI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS TVI_p95,
    PERCENTILE_CONT(MSR, 0.05) OVER(PARTITION BY AGE, ESPAC) AS MSR_p5,
    PERCENTILE_CONT(MSR, 0.95) OVER(PARTITION BY AGE, ESPAC) AS MSR_p95,
    PERCENTILE_CONT(PRI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS PRI_p5,
    PERCENTILE_CONT(PRI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS PRI_p95,
    PERCENTILE_CONT(GNDVI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS GNDVI_p5,
    PERCENTILE_CONT(GNDVI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS GNDVI_p95,
    PERCENTILE_CONT(PSRI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS PSRI_p5,
    PERCENTILE_CONT(PSRI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS PSRI_p95,
    PERCENTILE_CONT(GCI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS GCI_p5,
    PERCENTILE_CONT(GCI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS GCI_p95
  FROM `[PROJECT_ID].spectra_calibration.CMPC`
),
filtered_data AS (
  SELECT 
    AGE,
    ESPAC,
    -- 过滤各变量仅保留区间内数值
    CASE WHEN B2 BETWEEN B2_p5 AND B2_p95 THEN B2 END AS B2,
    CASE WHEN B3 BETWEEN B3_p5 AND B3_p95 THEN B3 END AS B3,
    CASE WHEN B4 BETWEEN B4_p5 AND B4_p95 THEN B4 END AS B4,
    CASE WHEN B8 BETWEEN B8_p5 AND B8_p95 THEN B8 END AS B8,
    CASE WHEN NDVI BETWEEN NDVI_p5 AND NDVI_p95 THEN NDVI END AS NDVI,
    CASE WHEN SAVI BETWEEN SAVI_p5 AND SAVI_p95 THEN SAVI END AS SAVI,
    CASE WHEN SIPI BETWEEN SIPI_p5 AND SIPI_p95 THEN SIPI END AS SIPI,
    CASE WHEN SR BETWEEN SR_p5 AND SR_p95 THEN SR END AS SR,
    CASE WHEN RGI BETWEEN RGI_p5 AND RGI_p95 THEN RGI END AS RGI,
    CASE WHEN TVI BETWEEN TVI_p5 AND TVI_p95 THEN TVI END AS TVI,
    CASE WHEN MSR BETWEEN MSR_p5 AND MSR_p95 THEN MSR END AS MSR,
    CASE WHEN PRI BETWEEN PRI_p5 AND PRI_p95 THEN PRI END AS PRI,
    CASE WHEN GNDVI BETWEEN GNDVI_p5 AND GNDVI_p95 THEN GNDVI END AS GNDVI,
    CASE WHEN PSRI BETWEEN PSRI_p5 AND PSRI_p95 THEN PSRI END AS PSRI,
    CASE WHEN GCI BETWEEN GCI_p5 AND GCI_p95 THEN GCI END AS GCI
  FROM quantile_thresholds
)
SELECT 
  AGE,
  ESPAC,
  -- B2统计指标
  AVG(B2) AS B2_mean,
  MAX(B2) AS B2_max,
  MIN(B2) AS B2_min,
  STDDEV(B2) AS B2_std,
  -- B3统计指标
  AVG(B3) AS B3_mean,
  MAX(B3) AS B3_max,
  MIN(B3) AS B3_min,
  STDDEV(B3) AS B3_std,
  -- B4统计指标
  AVG(B4) AS B4_mean,
  MAX(B4) AS B4_max,
  MIN(B4) AS B4_min,
  STDDEV(B4) AS B4_std,
  -- B8统计指标
  AVG(B8) AS B8_mean,
  MAX(B8) AS B8_max,
  MIN(B8) AS B8_min,
  STDDEV(B8) AS B8_std,
  -- NDVI统计指标
  AVG(NDVI) AS NDVI_mean,
  MAX(NDVI) AS NDVI_max,
  MIN(NDVI) AS NDVI_min,
  STDDEV(NDVI) AS NDVI_std,
  -- SAVI统计指标
  AVG(SAVI) AS SAVI_mean,
  MAX(SAVI) AS SAVI_max,
  MIN(SAVI) AS SAVI_min,
  STDDEV(SAVI) AS SAVI_std,
  -- SIPI统计指标
  AVG(SIPI) AS SIPI_mean,
  MAX(SIPI) AS SIPI_max,
  MIN(SIPI) AS SIPI_min,
  STDDEV(SIPI) AS SIPI_std,
  -- SR统计指标
  AVG(SR) AS SR_mean,
  MAX(SR) AS SR_max,
  MIN(SR) AS SR_min,
  STDDEV(SR) AS SR_std,
  -- RGI统计指标
  AVG(RGI) AS RGI_mean,
  MAX(RGI) AS RGI_max,
  MIN(RGI) AS RGI_min,
  STDDEV(RGI) AS RGI_std,
  -- TVI统计指标
  AVG(TVI) AS TVI_mean,
  MAX(TVI) AS TVI_max,
  MIN(TVI) AS TVI_min,
  STDDEV(TVI) AS TVI_std,
  -- MSR统计指标
  AVG(MSR) AS MSR_mean,
  MAX(MSR) AS MSR_max,
  MIN(MSR) AS MSR_min,
  STDDEV(MSR) AS MSR_std,
  -- PRI统计指标
  AVG(PRI) AS PRI_mean,
  MAX(PRI) AS PRI_max,
  MIN(PRI) AS PRI_min,
  STDDEV(PRI) AS PRI_std,
  -- GNDVI统计指标
  AVG(GNDVI) AS GNDVI_mean,
  MAX(GNDVI) AS GNDVI_max,
  MIN(GNDVI) AS GNDVI_min,
  STDDEV(GNDVI) AS GNDVI_std,
  -- PSRI统计指标
  AVG(PSRI) AS PSRI_mean,
  MAX(PSRI) AS PSRI_max,
  MIN(PSRI) AS PSRI_min,
  STDDEV(PSRI) AS PSRI_std,
  -- GCI统计指标
  AVG(GCI) AS GCI_mean,
  MAX(GCI) AS GCI_max,
  MIN(GCI) AS GCI_min,
  STDDEV(GCI) AS GCI_std
FROM filtered_data
GROUP BY AGE, ESPAC
ORDER BY AGE, ESPAC;

说明 #

• 执行前将SQL中[PROJECT_ID]替换为实际的BigQuery项目ID即可
• 每个变量独立计算分组分位数阈值、独立过滤，区间外的数值会被置为NULL，聚合函数自动忽略NULL值，不会参与统计计算
• 最终输出每行对应一个AGE+ESPAC分组，包含15个目标变量各4个统计指标，完全符合需求

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