7.28 Complex applications of Apply operation
Associate and join data in three tables, generate a new table sequence and group and summarize it.
Award salespeople whose actual single order amount exceeds 1,000 a performance-based bonus of 5% of the order amount based on the associated Employee table, Order table and Detail table.
We use A.news() function to achieve the join and computation.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Order where year(Date)=2014”) |
| 3 | =A1.query(“select * from Detail”) |
| 4 | =A1.query@x(“select * from Employee”) |
| 5 | =A2.switch(EmployeeID,A4:ID) |
| 6 | =A3.group(ID) |
| 7 | =A6.news(A2.select(ID:A6.~.ID); EmployeeID,(s=sum(Amount*(1-Discount)), if(s>1000, s*1.05, s)):Amount) |
| 8 | =A7.groups(EmployeeID.Name:Name; sum(Amount):Amount) |
A1 Connect to the database.
A2 Get records of the year 2014 from Order table.
A3 Query Detail table.
A4 Query Employee table.
A5 Use switch() function to replace EmployeeID values of Order table with corresponding records of Employee table.
A6 Group records of Detail table by order ID.
A7 Use news() function to join Detail table and Order table on order ID and calculate the actual amount of each order.
A8 Group A7’s records by employee and calculate total sales of each employee.
Execution result:
| Name | Amount |
|---|---|
| Alexis | 358882.02 |
| Emily | 432435.85 |
| … | … |
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
