3.8 Union all set members in a sequence
Get two sets of records from a table and union them.
One task is to select employees who have been in the company for less than one year and those whose sales performance rank in bottom 10% to participate in training.
| Sales |
|---|
| ID |
| CustomerID |
| EmpID |
| Amount |
| Employee |
|---|
| ID |
| Name |
| EntryDate |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Employee”) |
| 3 | =A1.query@x(“select * from Sales”) |
| 4 | =A2.select(age(EntryDate)<1) |
| 5 | =A3.groups(EmpID; sum(Amount):Amount) |
| 6 | =A5.top(A5.len()/10; Amount) |
| 7 | =A2.join@i(ID,A6:EmpID) |
| 8 | =[A4,A7].union() |
A1 Connect to the database.
A2 Import Employee table.
A3 Import Sales table.
A4 Get employees who joined the company less than one year ago.
A5 Group Sales table by EmpID and calculate the total sales amount for each salesperson.
A6 Get sales records where amounts rank in bottom 10%.
A7 Join Employee table and A6’s result set to get eligible records.
A8 Use union() function to union A4 and A7 and return all eligible employee records.
Execution result:
| ID | Name | EntryDate |
|---|---|---|
| 89 | Emily | 2020/02/01 |
| 241 | Samantha | 2020/01/01 |
| … | … | … |
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
