Order-based Calculations – Find Difference between Two Columns Recursively
【Question】
Id name nl nl1 diff
1 a 5 0 5
2 a 4 2 7
3 a 1 8 0
1 b 2 0 2
2 b 2 1 3
I need to calculate values for diff field, which is (nl-nl1). For example, for records with same name values, like a, the diff value in the second row is (5+4) – (2+0), etc. The number of rows with same name values is indefinite.
【Answer】
The computing logic is simple. But, it’s roundabout to do it in SQL because the language doesn’t support ordered sets. If the environment is JAVA+SQL, you can handle it in SPL (Structured Process Language) in any database environment. Below is the SPL script:
A |
|
1 |
$select * from tb order by name ,id |
2 |
=A1.run(diff=nl-nl1+if(name==name[-1],diff[-1])) |
A1: Retrieve data ordered by name and id in SQL;
A2: For records with same name values, the diff value in the current record is (nl-nl1+the previous record’s diff value).
You can call a SPL script in a Java application. For details, refer to How to Call an SPL Script in Java.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL