BigQuery使用_TABLE_SUFFIX查询各分组start_date前28天分区数据

实现逻辑 #

针对大分区表的批量查询场景，核心是优先锁定全局必要扫描分区范围触发自动分区裁剪，避免全表扫描，再通过配置关联完成多组数据的匹配提取，全程不需要遍历单组执行，一次查询即可输出所有分组结果，执行效率和单组查询基本持平。
具体执行步骤：

• 首先基于分组配置表计算两张分区表的全局扫描边界：
  • 用户表my_table_users_*扫描范围：所有分组start_date的最小值 ~ 所有分组end_date的最大值
  • 指标表my_metric_table*扫描范围：所有分组start_date往前推28天的最小值 ~ 所有分组start_date往前推1天的最大值
    这一步计算出的边界是静态值，查询引擎可以直接识别并触发分区裁剪，完全不会扫描范围外的无效分区。
• 其次在圈定的用户表扫描范围内，关联分组配置，过滤出每个分组在自身[start_date, end_date]区间内的去重用户，同时给每个用户绑定所属group_id、对应分组的指标查询起止日期标签。
• 最后在圈定的指标表扫描范围内，关联带标签的用户集合，过滤出每个用户落在所属分组前置28天区间内的指标数据，聚合输出即可。

可直接运行的SQL代码 #

假设你的分组配置表名为group_config，存储了示例中的group_id/start_date/end_date三个字段，固定实验ID为16709，代码如下：

WITH
-- 计算全局分区裁剪边界
global_partition_range AS (
  SELECT
    MIN(start_date) AS users_min_partition,
    MAX(end_date) AS users_max_partition,
    MIN(FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_SUB(PARSE_TIMESTAMP('%Y%m%d', start_date), INTERVAL 28 DAY))) AS metric_min_partition,
    MAX(FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_SUB(PARSE_TIMESTAMP('%Y%m%d', start_date), INTERVAL 1 DAY))) AS metric_max_partition
  FROM group_config
),
-- 提取各分组对应区间的去重用户，绑定分组指标查询范围
group_users AS (
  SELECT
    m.user_id,
    gc.group_id,
    gc.metric_start,
    gc.metric_end
  FROM `my_table_users_*` m
  CROSS JOIN global_partition_range gpr
  WHERE _TABLE_SUFFIX BETWEEN gpr.users_min_partition AND gpr.users_max_partition
    AND m.experiment_id = 16709
  JOIN (
    SELECT
      group_id,
      start_date,
      end_date,
      FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_SUB(PARSE_TIMESTAMP('%Y%m%d', start_date), INTERVAL 28 DAY)) AS metric_start,
      FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_SUB(PARSE_TIMESTAMP('%Y%m%d', start_date), INTERVAL 1 DAY)) AS metric_end
    FROM group_config
  ) gc
    ON m._TABLE_SUFFIX BETWEEN gc.start_date AND gc.end_date
  GROUP BY m.user_id, gc.group_id, gc.metric_start, gc.metric_end
),
-- 关联指标表提取目标数据
group_user_metrics AS (
  SELECT
    gu.group_id,
    mt._TABLE_SUFFIX AS date,
    mt.user_id,
    SUM(COALESCE(mt.metric, 0)) AS metric
  FROM `my_metric_table*` mt
  CROSS JOIN global_partition_range gpr
  WHERE mt._TABLE_SUFFIX BETWEEN gpr.metric_min_partition AND gpr.metric_max_partition
  JOIN group_users gu
    ON mt.user_id = gu.user_id
    AND mt._TABLE_SUFFIX BETWEEN gu.metric_start AND gu.metric_end
  GROUP BY gu.group_id, date, mt.user_id
)
SELECT * FROM group_user_metrics
ORDER BY group_id, date ASC

注意事项 #

• 如果存在一个用户同时属于多个分组的情况，SQL会自动按分组维度分别计算该用户在对应分组前置周期的指标，不需要额外去重处理。
• 后续新增分组时，只需要往group_config表中插入对应分组的配置记录，不需要修改SQL逻辑即可自动适配。
• 可以通过BigQuery查询预览里的「分区修剪」字段验证裁剪效果，正常情况下扫描的分区数和计算出的全局边界覆盖分区数完全一致，不会出现多余扫描。

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