BigQuery增量物化视图：重复统计特定数据的实现方案求助

核心思路 #

利用UNNEST展开数组的方式，对符合product = 'B' AND sector = 'Y'的行生成两条记录（一条保留原product值，一条设为'B - target'），其他行仅生成一条原记录。这种方式只引用源表一次，规避了增量物化视图不能重复引用同表的限制，同时实现数据的双重统计。

具体实现代码 #

CREATE OR REPLACE MATERIALIZED VIEW `your-project.your-dataset.your-mv`
OPTIONS (
  refresh_interval_minutes = 60,
  enable_refresh = true
)
AS
SELECT
  date,
  subproduct,
  sector,
  product_entry AS product
FROM
  `your-project.your-dataset.source_table`,
  UNNEST(
    CASE
      WHEN product = 'B' AND sector = 'Y' THEN ['B', 'B - target']
      ELSE [product]
    END
  ) AS product_entry
-- 按需添加聚合逻辑，例如统计行数
GROUP BY date, subproduct, sector, product_entry

关键说明 #

• 单表扫描合规：整个查询仅对源表做一次扫描，完全符合BigQuery增量物化视图不能重复引用同表的限制。
• 条件生成多记录：通过CASE语句动态生成数组，满足条件时生成包含两个product值的数组，展开后得到两行数据；其他情况仅保留原product值的单元素数组，保持原记录数。
• 兼容聚合需求：如果需要做计数、求和等统计，直接在查询中添加GROUP BY和聚合函数即可，不会干扰增量刷新的逻辑。

效果验证 #

假设源表存在一行数据：product='B', date='2024-01-01', subproduct='SP1', sector='Y'，经过UNNEST处理后会生成两条记录：

1. product='B', date='2024-01-01', subproduct='SP1', sector='Y'
2. product='B - target', date='2024-01-01', subproduct='SP1', sector='Y'
   后续统计时，这两条记录会分别计入对应的product分组，实现预期的双重统计效果。

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