如何在BigQuery中对多列批量计算加权平均值？

问题背景 #

我有一张BigQuery表product_tbl，结构和数据如下：

product  |  weight   |  col1   |    col2   |   col3   | ...  |   col100
productA |  1        |  1      |    2      |   3      | ...  |   100
productB |  1        |  1      |    2      |   3      | ...  |   100
productA |  2        |  0.5    |    20     |   3      | ...  |   200
productB |  3        |  0.5    |    20     |   3      | ...  |   200

需要按product分组，以weight列为权重，计算col1到col100每一列的加权平均值。单列计算的SQL是这样的：

SELECT 
     product,
     SUM(weight*col1)/SUM(weight) OVER(partition by product) AS weighted_average_col1
FROM product_tbl

但手动写100次这种计算语句太麻烦，想找个批量处理的方法，最终得到如下格式的结果：

product  |  weighted_average_col1   |  weighted_average_col2   |    weighted_average_col3   ...  |   weighted_average_col100
productA |  0.33                   |  x                      |    y                           |   z
productB |  0.37                   |  n                      |    m                           |   l

解决方案：用动态SQL自动生成计算逻辑 #

在BigQuery里可以通过EXECUTE IMMEDIATE结合系统表INFORMATION_SCHEMA.COLUMNS自动获取目标列名，批量生成加权平均的计算语句，不用手动重复编写。

具体代码 #

DECLARE sql_query STRING;

-- 生成所有col列的加权平均计算表达式
SET sql_query = (
  SELECT STRING_AGG(
    FORMAT(
      'SUM(weight*%s)/SUM(weight) AS weighted_average_%s', 
      column_name, column_name
    ), 
    ', ' ORDER BY column_name
  )
  FROM `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'product_tbl'
    AND column_name LIKE 'col%'
    -- 只包含col1到col100的列
    AND SAFE_CAST(REGEXP_EXTRACT(column_name, r'col(\d+)') AS INT64) BETWEEN 1 AND 100
);

-- 拼接完整查询语句并执行
SET sql_query = FORMAT(
  'SELECT product, %s FROM product_tbl GROUP BY product',
  sql_query
);

EXECUTE IMMEDIATE sql_query;

代码解释 #

1. 筛选目标列：通过查询INFORMATION_SCHEMA.COLUMNS，筛选出product_tbl中所有以col开头、编号在1到100之间的列。
2. 生成计算语句：用STRING_AGG把每个列对应的加权平均计算式拼接起来，每个式子的格式是SUM(weight*colN)/SUM(weight) AS weighted_average_colN。
3. 执行动态SQL：把生成的计算式拼接到完整的SELECT语句中，用EXECUTE IMMEDIATE执行最终查询。

注意事项 #

• 记得把代码里的你的项目ID.你的数据集ID换成你实际的项目和数据集名称。
• 如果你的col列命名规则不是严格的col1到col100，需要调整WHERE子句里的筛选条件来匹配实际列名。

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