"Group a data table by a certain field and perform an inter-row computation in each group. One ta .."

mars RaqForum 25 No.
329 View • 1 Years ago

6.23 Grouped subsets: perform inter-row computations in each grouped subset

Group a data table by a certain field and perform an inter-row computation in each group.
One task is to calculate difference of each user’s last sum of payment amount and the previous sum according to the user payment table.
Below is part of the table:

imagepng

SPL script:

	A
1	=db.query@x(“SELECT * FROM USERPAY”)
2	=A1.group@u(USERID)
3	=A2.(~.top(-2;PAYTIME))
4	=A3.new(~.USERID,if(~.count()<2,0,(~(1).PAYAMOUNT-~(2).PAYAMOUNT)):BALANCE)

A2 Group payment records by user without sorting them.
A3 Sort records in each group by payment time and get the last two records.
A4 Calculate difference between the last sum of payment and the second last sum.

Execution result:

imagepng