BigQuery CMPC表按AGE、ESPAC分组计算5-95分位数内统计量SQL编写

以下为满足需求的正确SQL语句，可直接扩展到全部15个变量：

WITH 
-- 计算每个AGE+ESPAC分组下各指标的5%、95%分位阈值
group_thresholds AS (
  SELECT
    AGE,
    ESPAC,
    -- B2分位阈值，其余变量按格式新增即可
    PERCENTILE_DISC(B2, 0.05) OVER(PARTITION BY AGE, ESPAC) AS p05_B2,
    PERCENTILE_DISC(B2, 0.95) OVER(PARTITION BY AGE, ESPAC) AS p95_B2
    -- 新增B3阈值示例：
    -- PERCENTILE_DISC(B3, 0.05) OVER(PARTITION BY AGE, ESPAC) AS p05_B3,
    -- PERCENTILE_DISC(B3, 0.95) OVER(PARTITION BY AGE, ESPAC) AS p95_B3
  FROM `[PROJECT_ID].spectra_calibration.CMPC`
  -- 每个分组仅保留一行阈值数据
  QUALIFY ROW_NUMBER() OVER(PARTITION BY AGE, ESPAC) = 1
),
-- 关联原表与阈值表，仅统计分位区间内的数值
group_stats AS (
  SELECT
    t.AGE,
    t.ESPAC,
    -- B2统计值，其余变量按格式新增即可
    MIN(IF(c.B2 > t.p05_B2 AND c.B2 < t.p95_B2, c.B2, NULL)) AS min_B2,
    AVG(IF(c.B2 > t.p05_B2 AND c.B2 < t.p95_B2, c.B2, NULL)) AS avg_B2,
    MAX(IF(c.B2 > t.p05_B2 AND c.B2 < t.p95_B2, c.B2, NULL)) AS max_B2,
    STDDEV(IF(c.B2 > t.p05_B2 AND c.B2 < t.p95_B2, c.B2, NULL)) AS stddev_B2
    -- 新增B3统计值示例：
    -- MIN(IF(c.B3 > t.p05_B3 AND c.B3 < t.p95_B3, c.B3, NULL)) AS min_B3,
    -- AVG(IF(c.B3 > t.p05_B3 AND c.B3 < t.p95_B3, c.B3, NULL)) AS avg_B3,
    -- MAX(IF(c.B3 > t.p05_B3 AND c.B3 < t.p95_B3, c.B3, NULL)) AS max_B3,
    -- STDDEV(IF(c.B3 > t.p05_B3 AND c.B3 < t.p95_B3, c.B3, NULL)) AS stddev_B3
  FROM `[PROJECT_ID].spectra_calibration.CMPC` c
  INNER JOIN group_thresholds t
    ON c.AGE = t.AGE AND c.ESPAC = t.ESPAC
  GROUP BY t.AGE, t.ESPAC
)
SELECT * FROM group_stats ORDER BY AGE, ESPAC;

• 剩余14个变量仅需按照注释中的示例，先在group_thresholds中添加对应变量的分位阈值计算逻辑
• 再在group_stats中添加对应变量的条件聚合统计逻辑即可，无需修改其他关联、分组规则
• 若需要包含分位边界值，将条件中的>/<替换为>=/<=即可

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