如何让含ARRAY_AGG的BigQuery视图利用日期分区特性？

问题根源 #

当你用ARRAY_AGG(t ORDER BY updatedTime DESC LIMIT 1)[OFFSET(0)]按ID全局分组时，BigQuery优化器无法确定某个ID的最新记录是否仅存在于你过滤的createDt分区中，因此会默认扫描全表来确保找到每个ID的全局最新记录。而普通视图因为没有跨分区的分组逻辑，过滤条件可以直接下推到基表的分区扫描。

针对性解决方案 #

1. 用窗口函数改写（适用于仅需分区内最新记录的场景） #

如果你的业务逻辑是只获取指定createDt分区内每个ID的最新记录（不关心该ID在其他分区的历史数据），可以用窗口函数替代ARRAY_AGG，让过滤条件能被优化器下推：

CREATE OR REPLACE VIEW my_dataset.my_view AS
SELECT * EXCEPT(rn)
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY updatedTime DESC) AS rn
  FROM my_dataset.base_table
)
WHERE rn = 1

查询视图时添加分区过滤：

SELECT * FROM my_dataset.my_view WHERE createDt = '2024-05-20'

此时BigQuery会先扫描createDt='2024-05-20'的分区，再在该分区内计算每个ID的最新记录，不会扫描全表。

2. 先过滤分区再聚合（适用于需全局最新但限定时间范围的场景） #

如果你需要全局最新记录，但查询时仅关心某个时间范围内的ID，可以在查询时先过滤基表分区，再执行聚合：

SELECT 
  ARRAY_AGG(t ORDER BY updatedTime DESC LIMIT 1)[OFFSET(0)].*
FROM my_dataset.base_table t
WHERE createDt BETWEEN '2024-05-14' AND '2024-05-20'
GROUP BY ID

如果要封装成可复用的视图，可创建参数化视图：

CREATE OR REPLACE VIEW my_dataset.my_param_view(createDt_start, createDt_end) AS
SELECT 
  ARRAY_AGG(t ORDER BY updatedTime DESC LIMIT 1)[OFFSET(0)].*
FROM my_dataset.base_table t
WHERE createDt >= createDt_start AND createDt <= createDt_end
GROUP BY ID

查询时传入时间参数：

SELECT * FROM my_dataset.my_param_view('2024-05-14', '2024-05-20')

这种写法会强制先扫描指定分区范围，再执行聚合逻辑。

3. 用具体化视图（适用于数据更新频率低的场景） #

如果你的基表数据更新不频繁，可以创建按createDt分区的具体化视图，预先完成聚合计算：

CREATE OR REPLACE MATERIALIZED VIEW my_dataset.my_mv
PARTITION BY createDt
AS
SELECT 
  ID,
  ARRAY_AGG(t ORDER BY updatedTime DESC LIMIT 1)[OFFSET(0)].* EXCEPT(ID)
FROM my_dataset.base_table t
GROUP BY ID, createDt

查询时直接过滤createDt即可扫描对应分区，且具体化视图会自动同步基表更新（有轻微延迟），查询性能更优。注意：此方案是按「分区+ID」聚合，若需全局ID的最新记录，不适用该方法。

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