From SQL to SPL: Number non null values in order within the group

After sorting a table in the Oracle database according to the first and second columns, the third column has non null values.

CP ROK DOPIS_C
6059150790 2014 C
6059150790 2015 C
6059150790 2016 C
6059150790 2017
6059150790 2018 C
6059150790 2019
6059150790 2020 C
6059150790 2021 C
6059150790 2022 C
6059150790 2023
6059150790 2024
6059150791 2014 C
6059150791 2015 C
6059150792 2014
6059150792 2015
6059150792 2016 C
6059150792 2017
6059150792 2018 C

Now we need to add a calculated column RN_C1: within a group of data with the same CP, if DOPIS_C has consecutive non null values, then start from 1 and fill in the numbers in order; If DOPIS_C is null, fill in the null value in RN_C1 and renumber from the next non null value.

CP ROK DOPIS_C RN_C1
6059150790 2014 C 1
6059150790 2015 C 2
6059150790 2016 C 3
6059150790 2017
6059150790 2018 C 1
6059150790 2019
6059150790 2020 C 1
6059150790 2021 C 2
6059150790 2022 C 3
6059150790 2023
6059150790 2024
6059150791 2014 C 1
6059150791 2015 C 2
6059150792 2014
6059150792 2015
6059150792 2016 C 1
6059150792 2017
6059150792 2018 C 1

When using relative position to reference set members in SQL, it is necessary to use tedious window functions, often accompanied by nested subqueries; The logical judgment of the subsequent numbering calculation is also quite complex, with multiple layers of case when, and the code is very cumbersome. The sequence numbers need to be accumulated within a specified interval, which requires assembling these intervals into some kind of grouping, and the idea is very convoluted.

Of course, it is also possible to reference set members at relative positions in a loop and make logical judgments to avoid "detours", but a single statement SQL cannot write a loop structure, which can be implemented using stored procedures. However, the code is still cumbersome and the framework is also complex.

SPL supports a complete procedural syntax that can handle complex business logic in loops, and can also conveniently reference set members in relative positions to simplify code.


 A
1 =orclJDBC.query("select *, null as RN_C1 from tb order by CP,ROK”)
2 =A1.run(RN_C1=if(DOPIS_C ,if(CP==CP[-1],t+=1,t=1),t=null) )

A1: Load data through JDBC, sort by CP and ROK, and add a null calculated column RN_C1.

A2: Modify RN_C1 for each record: When DOPIS_C is not null, if the CP of the current record is the same as the CP of the previous record, that is, within the same group, return t+1; If cross over group, set t to 1 and return. If DOPIS_C is null, set t to null and return. Note that the initial default value of variable t is null.

Question sourcehttps://stackoverflow.com/questions/78341522/cumulative-totals-in-oracle-sql