Generate Calculated Columns Based on Continuous Values — From SQL to SPL #29

Problem description & analysis:

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

Task: 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.

Code comparisons:

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: SPL provides the syntax for expressing relative positions:

👉🏻Try.DEMO

A1： Load data and add an 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.

✨ esProc SPL FREE Download — Free Trial Available, Download Now!