7.8 Association query: Align-join multiple tables on ordinal numbers
Join four associated table on ordinal number in alignment and group records to calculate average of each group.
Calculate the average evaluation score of each category of product in orders that do not have installment payments in the year 2014 based on the associated Order table, Payment table, Product table and Evaluation table.
The join() function works with @p option to perform a join on positions.
SPL script:
| A | |
|---|---|
| 1 | =connect(“demo”) |
| 2 | =A1.query(“select * from Order order by ID”) |
| 3 | =A1.query(“select * from Payment order by ID”) |
| 4 | =A1.query(“select * from Evaluation order by ID”) |
| 5 | =A1.query@x(“select * from Product”) |
| 6 | =A2.switch(ProductID, A5:ID) |
| 7 | =join@p(A6:Order;A3:Payment;A4:Evaluation) |
| 8 | =A7.select(year(Order.Date)==2014 && !Payment.Instalments) |
| 9 | =A8.groups(Order.ProductID.Category; avg(Evaluation.Score):Score) |
A1 Connect to the data source.
A2 Query Order table.
A3 Query Payment table.
A4 Query Evaluation table.
A5 Query Product table.
A6 The switch function converts ProductID field values in Order table to corresponding records of Product table.
A7 The join@p() function joins Order table, Payment table and Evaluation table on positions.
A8 Select records of orders that do not use installment payments in the year 2014.
A9 Group joined records by Category and calculate average evaluation score in each group.
Execution result:
| Category | Score |
|---|---|
| Electric appliance | 3.98 |
| Fruits | 3.86 |
| … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
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
