I have the following table (suppose the Name column is ascending):

Name    Tag
'a'     200
'b'     400
'c'     null
'd'     null
'e'     null
'f'     100
'g'     null
'h'     null
'i'     null
'j'     500

I want to write a SELECT query in Snowflake to return the null values with the least nearest value, like this:

Name    Tag
'a'     200
'b'     400
'c'     400
'd'     400
'e'     400
'f'     100
'g'     100
'h'     100
'i'     100
'j'     500

If I write the following query, it only fills the first null value for each consequent null group.

Select Name, coalesce(Tag, lag(Tag) over (order by Name)) as Tag

it returns:

Name    Tag
'a'     200
'b'     400
'c'     400
'd'     null
'e'     null
'f'     100
'g'     100
'h'     null
'i'     null
'j'     500

Any idea?

CodePudding user response：

Snowflake supports the IGNORE NULLS clause: https://docs.snowflake.com/en/sql-reference/functions/lag.html

So, your query would be:

Select Name, tag as tag_original, coalesce(Tag, lag(Tag) IGNORE NULLS over (order by Name)) as Tag from tags;

Oracle supports IGNORE NULLS as well: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=d108ff84755240ec3b488aed08d80dc5