2

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.

  • Which version of Redshift? Does it have window functions? – ypercubeᵀᴹ Sep 3 at 15:29
  • Yes it does. PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.8187 – user3348557 Sep 3 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.