如何创建接收查询为参数的BigQuery UDF实现数值列统计计算

原有方案的问题根源 #

你之前的实现方式触发限制的核心原因是将整列数据打包为数组传入JS UDF，不仅存在数组长度上限，JS运行时的内存也无法支撑大表全量数据加载，性能极低。
BigQuery完全支持这类统计需求，推荐用原生SQL实现，不需要依赖JS UDF，以下是两个可行方案：

方案1：原生SQL表UDF（性能最优，适合固定列场景） #

全程使用BigQuery原生聚合函数，无数组大小限制，支持PB级大表计算，性能比JS UDF高10~100倍，可同时返回多列的统计指标：

-- 创建临时表UDF，输入为表的行数据，返回结构化的统计结果
CREATE TEMP FUNCTION get_numeric_stats(input_row ANY TYPE)
AS (
  STRUCT(
    -- 按需求新增数值列的统计逻辑即可，此处以salary、age两列为例
    MIN(input_row.salary) AS salary_min,
    MAX(input_row.salary) AS salary_max,
    STDDEV(input_row.salary) AS salary_std_dv,
    COUNTIF(input_row.salary IS NULL) AS salary_null_count,
    MIN(input_row.age) AS age_min,
    MAX(input_row.age) AS age_max,
    STDDEV(input_row.age) AS age_std_dv,
    COUNTIF(input_row.age IS NULL) AS age_null_count
  )
);

-- 调用方式：直接传入目标表即可，不需要提前转数组
SELECT get_numeric_stats(t) AS all_stats
FROM `你的项目名.你的数据集名.你的表名` t;

方案2：动态SQL通用方案（自动识别所有数值列） #

如果需要适配任意表、不需要提前知道列名，可以用动态SQL实现全自动化统计，自动识别表内所有数值类型字段计算指标：

-- 创建通用统计存储过程
CREATE OR REPLACE PROCEDURE calc_all_numeric_cols_stats(
  project_id STRING,
  dataset_id STRING,
  table_id STRING,
  OUT stats_result ARRAY<STRUCT<col_name STRING, min_val FLOAT64, max_val FLOAT64, std_dv FLOAT64, null_count INT64>>
)
BEGIN
  -- 第一步：获取目标表所有数值类型的列名
  EXECUTE IMMEDIATE format("""
    SELECT ARRAY_AGG(column_name)
    FROM `%s.%s.INFORMATION_SCHEMA.COLUMNS`
    WHERE table_name = '%s'
      AND data_type IN ('INT64', 'FLOAT64', 'NUMERIC', 'BIGNUMERIC')
  """, project_id, dataset_id, table_id) INTO @col_names;

  -- 第二步：生成每个列的统计逻辑
  SET @stats_sql = (
    SELECT STRING_AGG(
      format("(SELECT AS STRUCT '%s' AS col_name, MIN(%s) AS min_val, MAX(%s) AS max_val, STDDEV(%s) AS std_dv, COUNTIF(%s IS NULL) AS null_count)",
        col, col, col, col, col)
    )
    FROM UNNEST(@col_names) col
  );

  -- 第三步：执行统计返回结果
  EXECUTE IMMEDIATE format("SELECT ARRAY[%s] FROM `%s.%s.%s`", @stats_sql, project_id, dataset_id, table_id) INTO stats_result;
END;

-- 调用示例
DECLARE final_stats ARRAY<STRUCT<col_name STRING, min_val FLOAT64, max_val FLOAT64, std_dv FLOAT64, null_count INT64>>;
CALL calc_all_numeric_cols_stats('你的项目名', '你的数据集名', '你的表名', final_stats);
-- 查看统计结果
SELECT * FROM UNNEST(final_stats);

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