blackduckie RaqForum 28 No.
463 View • 5 Years ago

Inter-row Calculations – Record Reference

SQL(179)

【Question】

I have a table as shown below:
ID   Title   Number
1    A     200
2    A    300
3    B    400
4    C     500
Expected result:
1   A    500
2   A    300
3    B    800
4    C    800
All Number values are 800 after rows are grouped by Title. For same Title values, change the smaller value to make the sum of their sum 800. As the expected result shows, to make the sum of two As 800, add 300 to the original Number value (Number=200) for ID 1. Both Number values for B and C are 800. Is there any idea about how to achieve it in SQL?

【Answer】

SQL doesn’t support ordered set, so you need to create sequence numbers for the rows using a subquery to perform the inter-row ordered-based calculation. Though window functions have helped make this much simpler, the code is still difficult to understand. Within a Java application, you can do it in SPL (Structured Process Language). An SPL script is intuitive and easy to understand and won’t be limited by the window function uses ruled by the database. Here’s the SPL script:

	A
1	$SELECT ID,Title,Number FROM TB ORDER BY Title,Number
2	>A1.group(Title).run(~(1).Number=(800-~.sum(Number)+~(1).Number))

A1: Retrieve rows ordered by Title and Number in SQL.

A2: Group rows by Title again (SPL supports retaining detailed data in each group), loop over each group to modify the first Number value. Below is the modified A1:

undefined