bigquery lead window function is there any way to use a table value as the offset?

user_15945488 0
Viewed 94

I was wondering for the lead window function in bigquery standard sql if there is any workaround to use a table value instead of a literal or query parameter?

select 
artcl
,days
,lead(days,ofst)over(partition by artcl order by days) as tst
from(
    select 123 as artcl, 1 as days ,2 as ofst
    union all
    select 123 as artcl, 2 as days ,2 as ofst
    union all
    select 123 as artcl, 3 as days ,2 as ofst
)
google-bigquery
1 Answers

You can use below approach

select artcl, days, ofst,
  arr[safe_offset(row_number() over(partition by artcl order by days) + ofst - 1)] as tst
from `project.dataset.table`
join (
  select artcl, array_agg(days order by days) arr, 
  from `project.dataset.table`
  group by artcl
)
using(artcl)
user_52219440
Related