How to get the preceding values in Redshift based on Where condition?

Question

I have three columns a student_name, column_1, column_2. I want to print the preceding value wherever the 0's exist in column 2.

I want the output like the below one, I used lag function. Probably I might be using it the wrong way.

"preceding" implies that there is an ordering. SQL tables represent unordered (multi)sets. There is no ordering. unless a column specifes the ordering. Also, the logic for what you want to do is quite unclear. Where do the numbers in the expected column come from? — Gordon Linoff

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-03-17T22:09:08

From what I can tell, you want to count the number of 0 values up to and including each row. If this interpretation is correct, you would use a conditional cumulative sum:

select t.*,
       sum( (column1 = 0)::int ) over (partition by student
                                       order by <ordering column>
                                       rows between unbounded preceding and current row
                                      )
from t;

Note: This assumes that you have an ordering column which you have not included in the question.

How to get the preceding values in Redshift based on Where condition?

1 Answers