From SQL to SPL：Generate calculated columns based on continuous values

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

n	x
1	0
2	1
3	2
4	3
5	4
6	2
7	1
8	0
9	1
10	0
11	0
12	0
13	0
14	1
15	2
16	3
17	4

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.

n	x	def
1	0	0
2	1	0
3	2	0
4	3	1
5	4	1
6	2	1
7	1	1
8	0	1
9	1	1
10	0	1
11	0	1
12	0	0
13	0	0
14	1	0
15	2	0
16	3	1
17	4	1

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

	A
1	$select n, x, null as def from have.txt order by n
2	=A1.run(def=if( x>2:1, x[-2]+x[-1]+x==0:0; def[-1] ))

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