GBQ的PIVOT语句中动态设置整数类型IN条件的实现方法

BigQuery 原生的 PIVOT 语法不支持直接在 IN 子句中嵌套查询动态获取枚举值，可以通过动态SQL拼接的方式简洁实现需求，总共两步即可： #

步骤1：拼接生成动态PIVOT的IN取值列表 #

先从ages_table中拉取所有Age值，拼接成符合PIVOT语法要求的逗号分隔格式：

DECLARE pivot_age_list STRING;
SET pivot_age_list = (
  SELECT STRING_AGG(CAST(Age AS STRING)) 
  FROM ages_table
);

如果需要给每个聚合列加别名前缀，也可以调整拼接逻辑，比如生成15 AS age_15,45 AS age_45这种格式：

SET pivot_age_list = (
  SELECT STRING_AGG(FORMAT("%d AS age_%d", Age, Age)) 
  FROM ages_table
);

步骤2：执行动态拼接后的完整PIVOT查询 #

用EXECUTE IMMEDIATE执行拼接好的完整SQL语句即可：

EXECUTE IMMEDIATE format("""
SELECT * FROM
(
  SELECT 
    Customer,
    Age
  FROM customer_table
)
PIVOT
(
  COUNT(*) AS Count_Ages
  FOR Age in (%s)
)
""", pivot_age_list);

注意事项 #

• 如果ages_table中的Age存在重复值，记得在拼接前加DISTINCT去重，避免PIVOT语法报错：SELECT STRING_AGG(DISTINCT CAST(Age AS STRING)) FROM ages_table
• Age值如果有特殊格式需要处理，调整STRING_AGG内的转换逻辑即可适配。

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