3.26 Aggregation on sequences: union and difference
Perform aggregation on a sequence of sequences through calculating union or difference.
Find courses that no one selects based on the following Course table and SelectCourse table.
Course:
| ID | NAME | TEACHERID |
|---|---|---|
| 1 | Environmental protection and … | 5 |
| 2 | Mental health of College Students | 1 |
| 3 | Computer language Matlab | 8 |
| 4 | Electromechanical basic practice | 7 |
| 5 | Introduction to modern life science | 3 |
| 6 | Modern wireless communication system | 14 |
| … | … | … |
SelectCourse:
| ID | STUDENTID | COURSE |
|---|---|---|
| 1 | 59 | 2,7 |
| 2 | 43 | 1,8 |
| 3 | 52 | 2,7,10 |
| 4 | 44 | 1,10 |
| 5 | 37 | 5,6 |
| 6 | 57 | 3 |
| … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Course”) |
| 3 | =A1.query@x(“select * from SelectCourse”) |
| 4 | =A3.union(COURSE.split@cp()) |
| 5 | =A2.(ID) |
| 6 | =A2(A5.pos([A5,A4].diff())) |
A1 Connect to the database.
A2 Retrieve Course table.
A3 Retrieve SelectCourse table.
A4 Split each Course field value in SelectCourse table by comma and use union() function to get union of sequences of courses.
A5 Get IDs of all courses.
A6 Use diff() function to get difference between Course table and course IDs in SelectCourse table, which are courses that no students select. Locate a course in A5 and select corresponding records in A2.
Execution result:
| ID | NAME | TEACHERID |
|---|---|---|
| 1 | Fundamentals of economic management | 21 |
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
