BigQuery动态多类型列转字符串并Unpivot问题求助

问题根源 #

BigQuery的UNPIVOT要求IN子句中所有列的数据类型必须完全一致，你的表中列类型混杂（INT64、String、Datetime等），直接执行逆透视会触发类型不匹配报错。同时需要保留Null值，不能通过丢弃Null来规避问题。

解决思路 #

先通过动态SQL将所有非ID列统一转换为STRING类型，再对转换后的结果执行逆透视操作，既解决类型不匹配问题，又保留Null值。

完整代码 #

declare my_cast_columns string;
declare my_unpivot_columns string;
declare target_id string default '你的目标ID'; -- 替换为需要查询的指定ID

-- 生成所有非ID列的类型转换语句（转为STRING）
set my_cast_columns = (
  select string_agg(format('cast(`%s` as string) as `%s`', column_name, column_name))
  from `abc-def-bigqueryghi.dataset_info.INFORMATION_SCHEMA.COLUMNS` 
  where table_name='table_1' 
    and column_name != 'id'
);

-- 生成逆透视所需的列名列表
set my_unpivot_columns = (
  select string_agg(format('`%s`', column_name))
  from `abc-def-bigqueryghi.dataset_info.INFORMATION_SCHEMA.COLUMNS` 
  where table_name='table_1' 
    and column_name != 'id'
);

-- 执行动态SQL：先转换类型，再逆透视，同时过滤指定ID
execute immediate format("""
select id, column_name, values
from (
  select id, %s
  from `abc-def-bigquery-ghi.dataset_info.table_1`
  where id = '%s'
)
unpivot (
  values for column_name in (%s)
)
""", my_cast_columns, target_id, my_unpivot_columns);

关键细节说明 #

• 类型统一：通过cast(column_name as string)将所有列转为STRING，确保UNPIVOT时类型一致，同时Null值会被保留为STRING类型的Null，符合Data Studio可视化需求。
• 动态生成语句：用string_agg自动拼接数百列的转换语句，无需手动逐个编写，避免重复劳动。
• 列名兼容：用反引号`包裹列名，兼容包含特殊字符（空格、下划线以外的符号）的列名，避免语法错误。
• 性能优化：在子查询中加入where id = '指定ID'，只处理目标ID的数据，减少数百万行数据的处理量，提升查询效率。

多表适配扩展 #

如果需要处理多张不同Schema的表，可以将表名设为变量，进一步动态化：

declare target_table string default 'table_1'; -- 替换为目标表名
declare target_id string default '你的目标ID';
declare my_cast_columns string;
declare my_unpivot_columns string;

set my_cast_columns = (
  select string_agg(format('cast(`%s` as string) as `%s`', column_name, column_name))
  from `abc-def-bigqueryghi.dataset_info.INFORMATION_SCHEMA.COLUMNS` 
  where table_name = target_table
    and column_name != 'id'
);

set my_unpivot_columns = (
  select string_agg(format('`%s`', column_name))
  from `abc-def-bigqueryghi.dataset_info.INFORMATION_SCHEMA.COLUMNS` 
  where table_name = target_table
    and column_name != 'id'
);

execute immediate format("""
select id, column_name, values
from (
  select id, %s
  from `abc-def-bigquery-ghi.dataset_info.%s`
  where id = '%s'
)
unpivot (
  values for column_name in (%s)
)
""", my_cast_columns, target_table, target_id, my_unpivot_columns);

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