BigQuery中分组与聚合的实现语法咨询

要实现按user统计url数量，并对数量可变的val字段批量求平均，可以利用BigQuery的元数据查询结合动态SQL来完成，避免手动逐个编写字段聚合逻辑：

DECLARE val_columns STRING;

-- 从元数据中提取所有val开头的字段，拼接成AVG聚合语句
SET val_columns = (
  SELECT STRING_AGG(
    FORMAT('AVG(`%s`) AS `%s`', column_name, column_name),
    ', '
  )
  FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your-source-table'
    AND column_name LIKE 'val%'
);

-- 动态执行创建新表的聚合查询
EXECUTE IMMEDIATE FORMAT("""
  CREATE OR REPLACE TABLE `your-project.your-dataset.target-table` AS
  SELECT
    user,
    COUNT(url) AS nb_url,
    %s
  FROM `your-project.your-dataset.your-source-table`
  GROUP BY user
""", val_columns);

关键说明： #

• 替换代码中的your-project、your-dataset、your-source-table和target-table为你的实际项目、数据集、源表和目标表名称。
• 元数据查询会自动匹配所有以val开头的字段，无论数量多少，都能生成对应的平均值聚合逻辑。
• 如果val字段的命名规则不同，只需调整column_name LIKE 'val%'的匹配条件即可适配。

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