12.5 Speed up computations of judging existence of non-foreign key matching
Based on association of two tables, get records through judging existence of non-foreign key matching while using an optimization method to speed up computation.
According to Sales table and Customer table, find the number of customers in each city who have orders in the year 2014.
| Sales |
|---|
| ID |
| CustomerID |
| Date |
| Amount |
| Customer |
|---|
| ID |
| Name |
| City |
| … |
When records are ordered by the distinct field, we can use @o option in groups() function to speed up computation.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Customer”) |
| 3 | =A1.query@x(“select * from Sales where year(Date)=2014 order by CustomerID”) |
| 4 | =A3.groups@o(CustomerID) |
| 5 | =A2.join@i(ID, A4:CustomerID) |
| 6 | =A5.groups(City; count(1):CustomerCount) |
A1 Connect to the database.
A2 Query Customer table.
A3 Select Sales records of the year 2014 and sort them by CustomerID.
A4 The groups() performs distinct on CustomerID; use @o option when records are ordered by the distinct field.
A5 The A.join@i() function performs filtering join.
A6 Group join result by City and count customers in each city.
Execution result:
| City | CustomerCount |
|---|---|
| Dongying | 6 |
| Tangshan | 7 |
| … | … |
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
