如何在BigQuery中计算超100列、145万条记录数据集的列统计量

你的代码核心问题是直接用变量名作为列名引用，BigQuery会把column_name当成字符串值而非表中的列，导致语法和逻辑错误。另外你还需要补充标准差、百分位数的计算逻辑。下面提供两种可行方案：

方案1：动态SQL循环（贴合你的原始思路） #

使用EXECUTE IMMEDIATE执行动态生成的SQL，让变量正确引用列名：

DECLARE column_name STRING;
DECLARE column_names ARRAY<STRING>;

-- 获取表中所有列名
SET column_names = (
  SELECT ARRAY_AGG(column_name)
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table_name'
);

-- 创建临时表存储统计结果
CREATE TEMP TABLE column_stats (
  column_name STRING,
  max_value FLOAT64,
  min_value FLOAT64,
  count_value INT64,
  std_dev FLOAT64,
  p50 FLOAT64,
  p95 FLOAT64
);

-- 循环遍历每一列计算统计量
FOR column_name IN (SELECT column_name FROM UNNEST(column_names))
DO
  -- 动态生成SQL并执行，用反引号包裹列名避免关键字冲突
  EXECUTE IMMEDIATE FORMAT("""
    INSERT INTO column_stats
    SELECT 
      '%s' AS column_name,
      MAX(CAST(`%s` AS FLOAT64)) AS max_value,
      MIN(CAST(`%s` AS FLOAT64)) AS min_value,
      COUNT(`%s`) AS count_value,
      STDDEV(CAST(`%s` AS FLOAT64)) AS std_dev,
      PERCENTILE_CONT(0.5) OVER() AS p50,
      PERCENTILE_CONT(0.95) OVER() AS p95
    FROM `your_project.your_dataset.your_table_name`
  """, column_name, column_name, column_name, column_name, column_name);
END FOR;

-- 查看结果
SELECT * FROM column_stats;

方案2：UNPIVOT批量处理（更高效，推荐） #

BigQuery擅长批量数据处理，用UNPIVOT将列转成行，一次扫描表完成所有统计，避免循环多次扫描表，效率更高：

-- 先获取所有数值类型列名，生成UNPIVOT需要的列列表
DECLARE unpivot_columns STRING;
SET unpivot_columns = (
  SELECT STRING_AGG(CONCAT('`', column_name, '`'))
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table_name'
    AND data_type IN ('INT64', 'FLOAT64', 'NUMERIC', 'BIGNUMERIC')
);

-- 执行批量统计
EXECUTE IMMEDIATE FORMAT("""
WITH unpivoted_data AS (
  SELECT
    column_name,
    CAST(value AS FLOAT64) AS value
  FROM `your_project.your_dataset.your_table_name`
  UNPIVOT(value FOR column_name IN (%s))
)
SELECT
  column_name,
  MAX(value) AS max_value,
  MIN(value) AS min_value,
  COUNT(value) AS count_value,
  STDDEV(value) AS std_dev,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS p50,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) AS p95
FROM unpivoted_data
GROUP BY column_name;
""", unpivot_columns);

注意事项： #

• 替换代码中的your_project、your_dataset、your_table_name为实际信息。
• 如果表中有非数值类型列，需通过INFORMATION_SCHEMA过滤，避免CAST转换失败。
• 百分位数可根据需求选择PERCENTILE_CONT（连续型插值）或PERCENTILE_DISC（离散型取实际值）。

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