pyspark - SQL using lag function

Question

What i want to do is to create a column 'ID_DESLIG' with the following rules:

• when STATE is 'LIGADO' then ID_DESLIG will be the id of the previous STATE is 'DESLIGADO', for the same panel;

• when STATE is 'DESLIGADO' then ID_DESLIG will be the id of the current row;

An example of what i want

Thanks in advance for the help

Answer 1

I think you want:

select t.*,
       (case when state = 'DESLIGADO' then id
             else max(case when state = 'DESLIGADO' then id end) over (order by id)
        end) as desligado_id
from t;

In turn, this can be simplified to:

max(case when state = 'DESLIGADO' then id end) over (order by id)

You could phrase this using lag() but only if you know that the states are always interleaved.

In standard SQL (and some databases), this could also be expressed as:

max(id) filter (where state = 'DESLIGADO') over (order by id)