4.8 External memory dimension table
SQL
SELECT Customers.CustomerID,CustomerName,Region,City,Amount
FROM Customers
INNER JOIN Orders ON Orders.CustomerID=Customers.CustomerID
WHERE OrderDate<='2021-12-31' and OrderDate>='2021-01-01'
ORDER BY Amount DESC
LIMIT 1,100
SPL
| A | |
|---|---|
| 1 | >st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et) |
| 2 | =file(“Orders_Time.ctx”).open().cursor@x(Amount,CustomerID;OrderDate>=start && OrderDate<=end) |
| 3 | =A2.groups(;top(100;-Amount)).#1 |
| 4 | =A3.joinx@q(CustomerID,file(“Customers.ctx”).open():CustomerID,CustomerName,Region,City) |
A4 When the fact table is relatively small and can be wholly loaded into the memory but the dimension table is rather large, we use joinx@q function to quickly locate the corresponding record and achieve association with the dimension table. This avoids full traversal of the dimension table and increases efficiency.
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
