Segmented Accumulation by Condition — From SQL to SPL #30

Problem description & analysis:

The ID field of a certain database table is used for sorting, the logic field is used for conditional judgment, and val is used for segmented accumulation.

Task: Now we need to add a calculated column output. When logic==true, output is set to 1. Otherwise, output is accumulated and the value is the output of the previous row + val.

Code comparisons:

SQL：

with table1 as (
SELECT *, countif(logic) over win1 as logic_run
FROM example_data
window win1 as (order by id rows between unbounded preceding and current row)
)
SELECT *,
sum(val) over win2 as sum_over,
sum(if(logic,1,val)) over win2 as output
from table1
window win2 as (partition by logic_run order by id rows between unbounded preceding and current row)

SQL requires multiple window functions and subqueries to indirectly implement cumulative calculations, which is cumbersome in terms of code.

SPL: SPL provides syntax of relative positions that allows for direct accumulation.

✍🏻 Try.DEMO

A1： Load data.

A2： Add a calculated column, if logic is true, set it to 1; Otherwise, set it as the output of the previous row + val. [-1] represents the previous row.

Experience esProc SPL FREE Download — Free Trial, No Hassle!