使用dplyr在BigQuery提取5-95分位数内统计值时报错

报错原因 #

你遇到的语法报错是因为dplyr通过bigrquery连接BigQuery时采用懒执行机制：不会先把全量数据拉取到本地R内存，而是将你的R代码自动翻译为标准SQL后发送到BigQuery服务器执行。本地运行CSV数据时直接在R内存计算，所以自定义函数可以正常运行，但推送到服务器时，R原生的quantile()函数无法被dplyr的SQL翻译器正确识别，最终生成了存在语法错误的SQL语句，触发报错。

可行实现方案 #

方案1：使用dplyr兼容的BigQuery原生函数改写

直接调用BigQuery原生支持的PERCENTILE_CONT分位数函数，避免自定义函数的翻译问题：

# 预计算各分组下所有字段的5%、95%分位数
quantile_ref <- raw_spectra_CMPC %>%
  group_by(AGE, ESPAC) %>%
  summarise(across(B2:GCI,
                   list(
                     q05 = ~PERCENTILE_CONT(.x, 0.05) OVER(),
                     q95 = ~PERCENTILE_CONT(.x, 0.95) OVER()
                   ),
                   .names = "{.col}_{.fn}")) %>%
  ungroup() %>%
  distinct()

# 关联分位数表过滤后统计所需指标
data_Q95 <- raw_spectra_CMPC %>%
  left_join(quantile_ref, by = c("AGE", "ESPAC")) %>%
  filter(across(B2:GCI, ~.x > get(paste0(cur_column(), "_q05")) & .x < get(paste0(cur_column(), "_q95")))) %>%
  group_by(AGE, ESPAC) %>%
  summarise(across(B2:GCI,
                   list(
                     mean = ~mean(.x, na.rm = TRUE),
                     max = ~max(.x, na.rm = TRUE),
                     min = ~min(.x, na.rm = TRUE),
                     sd = ~sd(.x, na.rm = TRUE)
                   ))) %>%
  collect() # 将最终计算结果拉取到本地R环境

方案2：直接编写原生BigQuery SQL执行（更稳定、性能更高）

完全避免dplyr的SQL翻译兼容问题，直接写SQL发送到BigQuery执行：

# 编写查询SQL，字段较多时可通过R批量生成重复的计算语句，无需手动逐字段编写
query_sql <- "
WITH quantile_cal AS (
  SELECT
    AGE, ESPAC,
    PERCENTILE_CONT(B2, 0.05) OVER(PARTITION BY AGE, ESPAC) AS B2_q05,
    PERCENTILE_CONT(B2, 0.95) OVER(PARTITION BY AGE, ESPAC) AS B2_q95,
    -- 此处补充B3到GCI的分位数计算逻辑，格式同上
    PERCENTILE_CONT(GCI, 0.05) OVER(PARTITION BY AGE, ESPAC) AS GCI_q05,
    PERCENTILE_CONT(GCI, 0.95) OVER(PARTITION BY AGE, ESPAC) AS GCI_q95
  FROM `helical-client-323918.spectra_calibration.CMPC`
),
filtered_data AS (
  SELECT t.*
  FROM `helical-client-323918.spectra_calibration.CMPC` t
  INNER JOIN quantile_cal q 
    ON t.AGE = q.AGE AND t.ESPAC = q.ESPAC
  WHERE
    t.B2 > q.B2_q05 AND t.B2 < q.B2_q95
    -- 此处补充B3到GCI的过滤逻辑，格式同上
    AND t.GCI > q.GCI_q05 AND t.GCI < q.GCI_q95
)
SELECT
  AGE, ESPAC,
  AVG(B2) AS B2_mean, MAX(B2) AS B2_max, MIN(B2) AS B2_min, STDDEV(B2) AS B2_sd,
  -- 此处补充B3到GCI的统计逻辑，格式同上
  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
"

# 执行SQL并拉取结果到本地
data_Q95 <- DBI::dbGetQuery(bq_conn, query_sql)

注意事项 #

如果需要处理的字段太多，可以用paste0()批量生成重复的SQL语句片段，无需手动逐字段编写，大幅提升效率。

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