12.7 An example of simplified self-join
Based on a single table, judge and get records through the complex self-join.
Get total amount of detail orders under each order that spans over one year according to order detail table.
| ID | NUMBER | AMOUNT | DELIVERDATE | ARRIVALDATE |
|---|---|---|---|---|
| 10814 | 1 | 408.0 | 2014/01/05 | 2014/04/18 |
| 10814 | 2 | 204.0 | 2014/02/21 | 2014/04/05 |
| 10814 | 3 | 102.0 | 2014/03/14 | 2014/04/06 |
| 10814 | 4 | 102.0 | 2014/04/09 | 2014/04/27 |
| 10814 | 5 | 102.0 | 2014/05/04 | 2014/07/04 |
| 10848 | 1 | 873.0 | 2014/01/06 | 2014/04/21 |
| … | … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Detail”) |
| 3 | =A2.group(ID) |
| 4 | =A3.select(interval(~.min(DELIVERDATE), ~.max(ARRIVALDATE)) > 365) |
| 5 | =A4.new(ID, ~.sum(AMOUNT):Amount) |
A1 Connect to the database.
A2 Query Detail table.
A3 Group Detail records by ID.
A4 Get detail records under same order that spans over 365 days.
A5 Create a new table sequence and calculate total amount of each order.
Execution result:
| ID | Amount |
|---|---|
| 10998 | 6800.0 |
| 11013 | 4560.0 |
| 11032 | 20615.0 |
| … | … |
SPL Official Website 👉 https://www.esproc.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.esproc.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/sxd59A8F2W
Youtube 👉 https://www.youtube.com/@esProc_SPL
