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!