如何在BigQuery中使用数组变量实现表透视？

BigQuery的PIVOT语法里，FOR ... IN子句要求传入静态的字面量值列表，不能直接使用数组变量或UNNEST()动态展开的值，这是你代码执行失败的核心原因。要实现基于数组变量的动态透视，需要借助**动态SQL（EXECUTE IMMEDIATE）**来构造并执行符合要求的透视语句。

解决方案代码示例 #

-- 1. 声明目标年份数组变量
DECLARE years ARRAY<INT64> DEFAULT [2020, 2021];

-- 2. 构造动态透视SQL：将数组元素拼接为PIVOT所需的静态值列表
DECLARE pivot_sql STRING;
SET pivot_sql = FORMAT("""
  SELECT * 
  FROM tmp.Produce
  PIVOT(SUM(sales) yearly_sales FOR year IN (%s))
""", STRING_AGG(CAST(year AS STRING), ', ') UNNEST(years));

-- 3. 执行动态生成的SQL语句
EXECUTE IMMEDIATE pivot_sql;

代码说明 #

• STRING_AGG(CAST(year AS STRING), ', ') UNNEST(years)：将数组中的年份元素转换为字符串并拼接成2020, 2021格式的字符串，刚好匹配PIVOT的IN子句要求。
• FORMAT()函数：将拼接好的年份字符串插入到预设的SQL模板中，生成完整的透视语句。
• EXECUTE IMMEDIATE：执行动态生成的SQL，完成基于数组变量的透视操作。

扩展：处理空数组情况 #

如果需要避免数组为空时的执行错误，可以添加判断逻辑：

DECLARE years ARRAY<INT64> DEFAULT [2020, 2021];

IF ARRAY_LENGTH(years) = 0 THEN
  SELECT '数组为空，无法执行透视操作' AS提示信息;
ELSE
  DECLARE pivot_sql STRING;
  SET pivot_sql = FORMAT("""
    SELECT * 
    FROM tmp.Produce
    PIVOT(SUM(sales) yearly_sales FOR year IN (%s))
  """, STRING_AGG(CAST(year AS STRING), ', ') UNNEST(years));
  
  EXECUTE IMMEDIATE pivot_sql;
END IF;

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