7.17 Merge-join tables of same order
Join two tables having same order using the merge algorithm, and group and summarize the joining result.
Calculate total order amount of each customer in the year 2014 based on the associated Order table and Detail table.
The join() function uses @m option to merge-join two associated tables in order.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Order where year(Date)=2014 order by ID”) |
| 3 | =A1.query@x(“select * from Detail order by ID”) |
| 4 | =join@m(A2:Order,ID;A3:Detail,ID) |
| 5 | =A4.groups(Order.CustomerID:CustomerID; sum(Detail.Amount):Amount) |
A1 Connect to the database.
A2 Get Orders records in the year 2014 and sort them by ID.
A3 Query Detail table and sort it by ID.
A4 Use join@m function to join two tables using the merge algorithm.
A5 Group the merged records by customer and calculate total order amount of each customer.
Execution result:
| CustomerID | Amount |
|---|---|
| ALFKI | 14848.0 |
| ANTON | 4041.0 |
| … | … |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
