...

Order-based Calculations – Find Difference between Two Columns Recursively

 
SQL(179)

【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.

 


SQL(179)
 
 •  402 View  •