7.24 Quick join query on same-order data table and large dimension table
Perform a quick join query on two cursors and an ordered bin file. Each cursor should be ordered by the first joining field.
Calculate the total refund of each product in the year 2015 based on the associated Order table, Returns table and Product table.
SPL provides cs.joinx() function to perform such a join query. Use @c option to speed up computation when the cursor is ordered by the first joining field; @c option and the @q option can work together.
SPL script:
| A | |
|---|---|
| 1 | =file(“Returns.btx”).cursor@b().select(year(Date)==2015) |
| 2 | =file(“Order.btx”) |
| 3 | =file(“Product.btx”) |
| 4 | =A1.joinx@qc(OrderID,A2:ID,Amount;ProductID,A3:ID,Category) |
| 5 | =A4.groups(Category; sum(Amount)) |
A1 Create cursor on Returns table.
A2 Create bin file object Order.
A3 Create bin file object Product.
A4 Use cs.joinx() function to join Order table on OrderID and Product table on ProductID, during which @qc options are used to speed up computation.
A5 Group A4’s records by Category and calculate the total refund for each type of product.
Execution result:
| Category | Amount |
|---|---|
| Electric appliance | 1854.5 |
| Fruits | 251.5 |
| … | … |
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
