无法使用_TABLE_SUFFIX时，如何在BigQuery中按年份跨多表查询？

问题根源梳理 #

1. 后缀通配符无效：BigQuery仅支持前缀通配符（通配符必须放在表名末尾，如dataset.prefix_*），*_2021这种后缀匹配写法不被识别，因此报错表不存在。
2. _TABLE_SUFFIX筛选逻辑错误：使用全表通配符dataset.*时，_TABLE_SUFFIX返回的是完整表名（如01_2021），若直接用字符串排序筛选（如_TABLE_SUFFIX >= '2021'），会因字符串字典序规则（01_2021 < 2021）漏掉目标表，或误包含非目标数据。
3. _TABLE_DATE_RANGE不适用：该函数要求表名包含标准日期格式（如yyyyMMdd），而你的表名是mm_yyyy，不符合函数的格式要求，因此报错。

可行解决方案 #

1. 精准筛选目标表并按年份汇总

使用全表通配符dataset.*，通过解析_TABLE_SUFFIX（完整表名）提取月份和年份，转换为日期后筛选时间范围，再按年份汇总：

SELECT
  -- 从表名中提取年份作为分组字段
  SPLIT(_TABLE_SUFFIX, '_')[OFFSET(1)] AS year,
  -- 替换为你的业务汇总逻辑，示例为求和与计数
  SUM(transaction_amount) AS total_transaction,
  COUNT(*) AS record_count
FROM `your-project.your-dataset.*`
WHERE
  -- 将表名转换为日期，精准筛选2020年4月至2024年8月的表
  PARSE_DATE('%m_%Y', _TABLE_SUFFIX) BETWEEN DATE('2020-04-01') AND DATE('2024-08-01')
GROUP BY year

2. 自动创建对应年份的独立汇总表

使用BigQuery脚本的循环逻辑，自动为每个目标年份生成单独的汇总表：

-- 先获取所有符合时间范围的目标年份
WITH target_years AS (
  SELECT DISTINCT SPLIT(_TABLE_SUFFIX, '_')[OFFSET(1)] AS year
  FROM `your-project.your-dataset.*`
  WHERE PARSE_DATE('%m_%Y', _TABLE_SUFFIX) BETWEEN DATE('2020-04-01') AND DATE('2024-08-01')
)
-- 循环创建每个年份的汇总表
FOR year_record IN (SELECT year FROM target_years) DO
  EXECUTE IMMEDIATE FORMAT("""
    CREATE TABLE `your-project.your-dataset.year_summary_%s` AS
    SELECT
      -- 替换为你的业务字段与汇总逻辑
      SUM(order_amount) AS total_order,
      AVG(order_amount) AS avg_order
    FROM `your-project.your-dataset.*`
    WHERE
      PARSE_DATE('%%m_%%Y', _TABLE_SUFFIX) BETWEEN DATE('2020-04-01') AND DATE('2024-08-01')
      AND SPLIT(_TABLE_SUFFIX, '_')[OFFSET(1)] = '%s'
  """, year_record.year, year_record.year);
END FOR;

注意事项 #

• 确保所有目标表的Schema完全一致，否则跨表查询会因字段不匹配报错。
• 如果涉及表数量较多，建议开启BigQuery查询缓存或使用分区表优化查询性能。

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