RaqForum 41 No.
19 View  • 
     ...

From SQL to SPL: Segmented accumulation by condition

 

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.

id

logic

val

1

true

1

2

false

2

3

false

3

4

false

4

5

true

0

6

false

1

7

false

2

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.

id

logic

val

output

1

true

1

1

2

false

2

3

3

false

3

6

4

false

4

10

5

true

0

1

6

false

1

2

7

false

2

4

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 provides syntax of relative positions that allows for direct accumulation:

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

　A

1

$select * from example_data.txt order by id

2

=A1.derive(if(logic,1,output[-1]+val):output)

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.

Question source：https://stackoverflow.com/questions/78113688/create-a-counter-in-sql-with-the-recursive-addition-of-row-value-based-on-a-cond


 
 •  19 View  •