I am trying to calculate a column that performs a running count of consecutive null values, but the running count will reset upon non-null values. Currently I am trying to do it with a window function
ROW_NUMBER() OVER (PARTITION BY ID, VAL ORDER BY VAL ROWS UNBOUNDED PRECEDING)
But this is just giving me an incrementing number that does not reset or break upon seeing non-null values.
For example if I had a dataset like this:
id | date | val
----+-------+-------
1 | 1/1 | NULL
1 | 1/2 | NULL
1 | 1/3 | NULL
1 | 1/4 | 1
1 | 1/5 | NULL
1 | 1/6 | NULL
1 | 1/7 | 1
2 | 1/8 | 2
2 | 1/9 | NULL
2 | 1/1 | NULL
2 | 1/2 | 1
2 | 1/3 | NULL
2 | 1/4 | 0
2 | 1/5 | NULL
2 | 1/6 | NULL
I would like the output to look like this:
id | date | val | foo
----+-------+-------+-------
1 | 1/1 | NULL | 1
1 | 1/2 | NULL | 2
1 | 1/3 | NULL | 3
1 | 1/4 | 1 |
1 | 1/5 | NULL | 1
1 | 1/6 | NULL | 2
1 | 1/7 | 1 |
2 | 1/8 | 2 |
2 | 1/9 | NULL | 1
2 | 1/1 | NULL | 2
2 | 1/2 | 1 |
2 | 1/3 | NULL | 1
2 | 1/4 | 0 |
2 | 1/5 | NULL | 1
2 | 1/6 | NULL | 2
But right now the foo column would just keep incrementing the nulls for each user.