5.3 When data is unordered, find difference between the specified column and the column of the previous date in original order
There is a SAMPLE table in Sybase database. Its data is as follows:
| SECURITY_ID | DATE | REP |
|---|---|---|
| 2256 | 2020-01-01 | 0 |
| 2257 | 2020-01-02 | 1 |
| 2258 | 2020-01-03 | 2 |
| 2256 | 2020-01-02 | 3 |
| 2256 | 2020-01-03 | 5 |
With the order of records in the table unchanged, calculate the difference (DIFF) between a REP value and the value of the previous day for records having same SECURITY_ID value. Below is the desired result:
| SECURITY_ID | DATE | REP | DIFF |
|---|---|---|---|
| 2256 | 2020-01-01 | 0 | 0 |
| 2257 | 2020-01-02 | 1 | 1 |
| 2258 | 2020-01-03 | 2 | 2 |
| 2256 | 2020-01-02 | 3 | 3 |
| 2256 | 2020-01-03 | 5 | 2 |
The task is like this: in the original data order, calculate the difference between REP value of the current date and that of the previous date for records having same SECURITY_ID values.
SPL script:
| A | |
|---|---|
| 1 | =connect(“demo”) |
| 2 | =A1.query@x(“SELECT * FROM SAMPLE”).derive(null:DIFF) |
| 3 | >A2.group(SECURITY_ID).(~.run(DIFF=REP-REP[-1])) |
| 4 | return A2 |
A3 Group A2’s table sequence by SECURITY_ID and calculate the difference between REP value of the current date and that of the previous date. The grouped subsets contain references of records in the original table sequence. That is to say, values are actually written to the DIFF column of the original table sequence.
A4 Return the original table sequence, whose order is maintained.
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
