CODEWITHSUNDEEP
sqlamazon-redshift
rodrigocf
rodrigocf

Reputation: 2099

Apply condition to window function in redshift

Say that I have this data:

status   |  source |  communication
-------------------|----------------
start    |  1      |  1
changed  |  2      |  1
changed  |  2      |  1
changed  |  2      |  1
end      |  1      |  1

I know that I can use the lag function partitioning on the communication column and ordering by a timestamp column (not shown here) to get the previous source.

However, I how do I get the previous source where the status is not 'Changed' while retaining all the rows? Like this:

status   |  source |  communication | prev_source
-------------------|----------------|-----------
start    |  1      |  1             | null
changed  |  2      |  1             | 1
changed  |  2      |  1             | 1
changed  |  2      |  1             | 1
end      |  1      |  1             | 1

Upvotes: 0

Views: 1446

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do this using ignore nulls:

select t.*,
       lag(case when status <> 'changed' then source end ignore nulls) over
           (order by ??) as prev_source
from t;

You also need a column to specify the ordering of the rows. That is what the ?? is for.

Upvotes: 3

Related Questions