Search code examples
sqlprestotrino

How to take previous non-null value in sequence?

DB is Presto. Say I have a table {id dim1, dim2, seq, value}. seq is the sequence number for for value, and value contains NULLs. I have:

select
id,
dim1,
dim2,
seq,
value,
"value_backfilled" -- Want to compute this
from table
group by id, dim1, dim2
order by id, dim1, dim2, seq

In the select I want "backfill" value with the last non-null value in the sequence. For example, in a particular grouping, the values are [1, null, null, null, 2, null, null, 3, null, null, null], I want it to be [1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3]

Solution

  • Presto supports IGNORE NULLS, so you seem to want:

    select lag(value ignore nulls) over (order by id)

    If the value is always increasing, you can also use max():

    select max(value) over (order by id)

    Note: If you want this per dimension combinations, then you would add partition by.