BigQuery中替代join lateral实现子查询引用值的方案

我有一张BigQuery追加式（append-only）数据表，每次实体更新时都会插入其新版本，每个实体有唯一ID，每条记录包含插入时间戳。查询实体最新版本时，我会按id分区、按排名排序并选取最新版本。

我希望基于此绘制实体随时间的变化趋势，例如生成自1月1日起每天一行的记录，展示当日实体的状态汇总。在PostgreSQL中，我会使用如下写法：

select
  ...
from generate_series('2022-01-01'::timestamp, '2022-09-01'::timestamp, '1 day'::interval) query_date
left join lateral (
  select *
  from (
    with snapshot as (
      select distinct on (id) *
      from table
      where "createdOn" <= query_date
      order by id, "createdOn" desc
    )

这种写法类似循环遍历，每个子查询对应一个query_date（此处为每天），并在where子句中引用该日期，筛选出截至该时间点的数据。

我知道可以将子查询逻辑保存为查询并按计划每日预填充，但我想了解如何编写探索性查询。

关联多表时的报错问题 #

使用关联子查询有一定效果，但当子查询需要关联另一张追加式数据表（存储关联实体）时会失效。

如下单表关联的写法可行：

select
  day
  , (
    select count(*)
    from `table` t
    where date(createdOn) < day
  )
from unnest((select generate_date_array(date('2022-01-01'), current_date(), interval 1 day) as day)) day
order by day desc

但如果子查询需要关联另一张表，例如：

select
  day
  , (
    select as struct *
    from (
      select
        id
        , status
        , rank() over (partition by id order by createdOn desc) as rank
      from `table1`
      where date(createdOn) < day
      qualify rank = 1
    ) t1
    left join (
      select
        id
        , other
        , rank() over (partition by id order by createdOn desc) as rank
      from `table2`
      where date(createdOn) < day
      qualify rank = 1
    ) t2 on t2.other = t1.id
  )
from unnest((select generate_date_array(date('2022-01-01'), current_date(), interval 1 day) as day)) day
order by day desc

会报错：Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN。Stack Overflow上的相关问题解决方案是将关联查询移至顶层JOIN，但这不符合我的需求。

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