Snowflake fill null values

user_2754071 0
Viewed 719

I have a table like this in Snowflake, where I have a "class" column, where all rows with level 1 have a value. I would like to have a new column "WANTED_OUTPUT", where the value from class are filled until new value occurs, then fill with that value.

I have been looking at first_value and last_value function, but I miss something there can "group" all the rows from level 1 until next level 1 together before I can use the first_value and the partition over that.

Any suggestion?

+----+-------+-------+-------+---------------+
| id | col_c | level | class | WANTED_OUTPUT |
+----+-------+-------+-------+---------------+
| a  | q1    | 1     | c99   | c99           |
+----+-------+-------+-------+---------------+
| a  | w2    | 2     | NULL  | c99           |
+----+-------+-------+-------+---------------+
| a  | g6    | 2     | NULL  | c99           |
+----+-------+-------+-------+---------------+
| a  | j5    | 3     | NULL  | c99           |
+----+-------+-------+-------+---------------+
| a  | x8    | 1     | c3    | c3            |
+----+-------+-------+-------+---------------+
| a  | x9    | 2     | NULL  | c3            |
+----+-------+-------+-------+---------------+
| a  | h5    | 1     | c67   | c67           |
+----+-------+-------+-------+---------------+
snowflake-cloud-data-platform
1 Answers

Using FIRST/LAST_VALUE:

SELECT *,
   FIRST_VALUE(class) IGNORE NULLS OVER (PARTITION BY id ORDER BY level) AS wanted
FROM tab;

Tables are unordered sets by design so stable sort is required. Level may not be enough based on provided input.

Suggestion: Adding explicit timestamp or seq id column to provide stable sort column would be preferred.

SELECT tab.*,    
   LAST_VALUE(class) IGNORE NULLS OVER (PARTITION BY id 
                     ORDER BY rn
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS wanted 
FROM tab;

db<>fiddle demo

user_50708790
Related