7.23 Quick join query on small data table and large dimension table
Perform a quick join query on cursor generated from a relatively small data table and an ordered bin file.
Find names of customers whose order amount rank in top 3 in the year 2014 based on the associated Sales table and Customer table.
The cs.joinx() function is used to perform a join involving a segmented bin file. Use @q option to speed up the computation when data volume is not large.
SPL script:
| A | |
|---|---|
| 1 | =file(“Sales.btx”).cursor@b().select(year(Date)==2014) |
| 2 | =file(“Customer.btx”) |
| 3 | =A1.groups(CustomerID;sum(Amount):Amount) |
| 4 | =A3.top(-3;Amount) |
| 5 | =A4.joinx@q(CustomerID,A2:ID,Name:CustomerName).fetch() |
A1 Create cursor on bin file Sales and select sales records in the year 2014.
A2 Create bin file object Customer ordered by ID.
A3 Group A1’s records by CustomerID and calculate each customer’s total order amount.
A4 Get customers whose order amounts rank in top 3.
A5 Use cs.joinx() function to join Sales table and Customer table on customer ID, during which @q option is used to speed up computation as the data volume is not large.
Execution result:
| CustomerID | Amount | CustomerName |
|---|---|---|
| 71 | 130672.64 | SAVEA |
| 63 | 64238.0 | QUICK |
| 20 | 53467.38 | ERNSH |
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
