如何在 BigQuery 中创建循环语句并添加自同一循环中每条记录的开始记录以来的天数差异？

尝试以下方法（注意使用最近在预览中引入的 - QUALIFY子句）

select first_row.id as new_id, 
  id, domain, type, timestamp, 
  timestamp_diff(timestamp, first_row.timestamp, day) as days, 
  coll1, coll2
from (
  select *, 
    first_value(t) over(partition by domain, grp order by timestamp) first_row
  from (
    select *, 
      countif(type = 'Start') over(partition by domain order by timestamp) grp
    from `project.dataset.table`
  ) t
  where grp > 0 
  qualify timestamp_diff(timestamp, first_row.timestamp, day) < 2 * 365
) 
-- order by domain, timestamp              

如果应用于您问题中的样本数据 - 输出是

UPDATE: in my initial answer above I've missed this part of OP's requirements: Also if there is another Start-record in this period that also have the same Domain. In those cases the same record can be added multiple times. Below answer addresses all OP's requirements

with starts as (
  select * from `project.dataset.table` where type = 'Start'
), others as (
  select * from `project.dataset.table` where type != 'Start'
)
select s.id as new_id, o.id, o.domain, o.type, o.timestamp, 
  timestamp_diff(o.timestamp, s.timestamp, day) as days, 
  o.coll1, o.coll2
from others o 
join starts s 
on s.domain = o.domain 
and o.timestamp >= s.timestamp
and timestamp_diff(o.timestamp, s.timestamp, day) < 2 * 365
union all 
select id as new_id, id, domain, type, timestamp, 0 as days, coll1, coll2
from starts
-- order by domain, new_id, timestamp 

If applied to sample data in the question - output is