BigQuery编写查询时如何动态引用名称含中间可变日期的表

方案1：通配符表匹配（原生支持，性能最优） #

BigQuery通配符表的*匹配符并非只能放在表名末尾，可放置在表名任意位置，完全适配numberedlanding_YYYYMMDD_storefront的表名规则。
匹配T-1日目标表的写法如下，通过_TABLE_SUFFIX系统伪列（存储*位置匹配到的字符串）过滤指定日期的表，不会扫描多余日期的数据，无额外开销：

WITH df1 AS (
  SELECT * 
  FROM `你的项目名.你的数据集名.numberedlanding_*_storefront`
  WHERE 
    -- 过滤T-1日对应的表，和原有日期计算逻辑完全一致
    _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND col1 > 0
),
df2 AS (
  SELECT *, col1/col2 AS test FROM df1
)
SELECT * FROM df1 a JOIN df2 b ON a.name = b.name

注意：该方案要求所有匹配numberedlanding_*_storefront格式的表同名字段类型完全一致，否则会出现字段兼容报错。

方案2：EXECUTE IMMEDIATE动态执行SQL #

如果存在表结构不统一、或者需要更灵活的表名生成逻辑，可以用BigQuery原生动态SQL能力，直接拼接表名后执行，解决临时函数只能返回字符串、无法作为表引用的问题：

-- 声明变量计算目标表全名
DECLARE target_table STRING DEFAULT CONCAT(
  '`你的项目名.你的数据集名.numberedlanding_',
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),
  '_storefront`'
);

-- 拼接业务逻辑SQL并执行
EXECUTE IMMEDIATE FORMAT("""
WITH df1 AS (
  SELECT * FROM %s WHERE col1>0
),
df2 AS (
  SELECT *,col1/col2 as test from df1
)
SELECT * FROM df1 a JOIN df2 b on a.name=b.name
""", target_table);

• 该方案不依赖通配符匹配规则，表名生成逻辑完全自定义，适配复杂表名规则场景
• 执行时会先计算出目标表名，再执行业务查询，执行效率和直接写死表名无差异

• 若所有按日生成的表结构完全统一，优先选择方案1，写法更简洁，后续要做多日聚合统计时，仅需调整_TABLE_SUFFIX的过滤范围即可，无需修改核心业务逻辑
• 若表结构变动频繁、或后续需要扩展多表动态关联等复杂逻辑，选择方案2灵活度更高

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