The field n of a certain database table is used for sorting, and the x field is an integer, sometimes with consecutive 0s.

Now we need to add a calculated column def, which requires the initial value of def to be 0; If the current row x>2, set def to 1; When encountering three consecutive x=0, reset the current def to 0; In other cases, keep def the same as the previous row.

SQL：

with cte as (
    select *
      ,(x > 2) exceeded_2
      ,(0 = all(array[     x
                      ,lag(x,1,0)over w1
                      ,lag(x,2,0)over w1
                     ]
                )
        ) as should_switch
    from have
    window w1 as (order by n) )
,cte2 as (
    select *,sum(should_switch::int)over(order by n) def_on_period 
    from cte
)
select n,x,(bool_or(exceeded_2) over w2)::int as def
from cte2
window w2 as (partition by def_on_period 
              order by n);

SQL requires multiple window functions and multiple subqueries to implement relative position calculation, and the code is complex and difficult to understand. SPL provides syntax for expressing relative positions:

https://try.esproc.com/splx?4OY

A1： Load data and add empty calculated column.

A2： Modify the calculated column, if the current row x>2, set def to 1; If three consecutive rows are 0, then def is set to 0; Otherwise, the def remains unchanged (set to the previous row's def). [-1] represents the previous row.

Question source：https://stackoverflow.com/questions/78128488/values-based-on-preceding-rows-when-x2-then-repeat-1-when-x-0-for-3-consecuti