7.15 Judge data existence through foreign key mapping
Select records according to existence of foreign key mapping and perform grouping & aggregation based on two associated tables.
Calculate total order amount of each customer in Beijing in the year 2014 according to the associated Sales table and Customer table.
A.switch() function works with @i function to delete a record that cannot find the foreign key map.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Sales where year(Date)=2014”) |
| 3 | =A1.query@x(“select * from Customer where City=‘Beijing’”) |
| 4 | =A2.switch@i(CustomerID, A3:ID) |
| 5 | =A4.groups(CustomerID.Name:Name; sum(Amount):Amount).sort@z(Amount) |
A1 Connect to the database
A2 Get records of 2014 from Sales table.
A3 Get records of Beijing customers from Customer table.
A4 Switch function works with @i function to get records where the customer is in Beijing.
A5 Group A4’s records by customer, calculate their total order amount, and arrange records by amount in descending order.
Execution result:
| Name | Amount |
|---|---|
| SAVEA | 130672.64 |
| HUN | 23959.05 |
| … | … |
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
