BigQuery如何动态识别月份列自动UNION ALL生成标准化结果表

BigQuery完全可以实现该自动化需求，无需每月新增列后手动调整SQL代码，核心通过系统元数据表自动识别列+动态SQL执行即可落地。

• 列自动识别：调用INFORMATION_SCHEMA.COLUMNS系统视图读取6张源表的全量字段元数据，通过正则匹配筛选出符合MMM_Date/MMM_Score/MMM_Activities命名规则的列，自动提取所有已存在的月份前缀，无需人工维护月份列表。
• 拼接逻辑自动生成：针对每张源表、每个识别到的有效月份，自动生成列映射片段，将对应月份的三个字段映射为Test_Date/Test_Score/Test_Activities，所有片段自动用UNION ALL连接。
• 自动覆盖结果：通过CREATE OR REPLACE TABLE语法搭配动态执行命令，每次运行直接覆盖目标结果表，自动整合所有历史月份+新增月份的全量数据。

将代码中的项目名、数据集名、源表名、目标表名替换为实际值即可直接使用：

EXECUTE IMMEDIATE FORMAT("""
CREATE OR REPLACE TABLE `你的项目ID.你的数据集名.整合后结果表` AS
%s
""", (
  SELECT STRING_AGG(per_table_per_month_sql, " UNION ALL ")
  FROM (
    SELECT
      FORMAT("""
        SELECT
          member_ID,
          %s_Date AS Test_Date,
          %s_Score AS Test_Score,
          %s_Activities AS Test_Activities
        FROM `%s`
      """, month_prefix, month_prefix, month_prefix, full_table_name) AS per_table_per_month_sql
    FROM (
      SELECT DISTINCT
        CONCAT(table_catalog, '.', table_schema, '.', table_name) AS full_table_name,
        SPLIT(column_name, '_')[OFFSET(0)] AS month_prefix
      FROM `你的项目ID.你的数据集名.INFORMATION_SCHEMA.COLUMNS`
      WHERE
        -- 填入需要处理的6张源表名
        table_name IN ('源表1','源表2','源表3','源表4','源表5','源表6')
        -- 正则匹配符合命名规则的列
        AND REGEXP_CONTAINS(
          column_name,
          r'^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)_(Date|Score|Activities)$'
        )
    )
  )
));

注意事项：如果后续新增同结构的源表，只需要把新表名加入上述代码的table_name IN (...)筛选列表即可，不需要调整其他逻辑；只要新增月份的列名保持约定的命名规则，代码运行时会自动识别纳入，无需人工修改映射关系。

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