12.1 Judge belongingness of sets
Get records from a table through belongingness of sets.
Calculate average salary of each department in first-tier cities based on the following employee table.
| ID | NAME | CITY | SALARY |
|---|---|---|---|
| 1 | Rebecca | Tianjin | 7000 |
| 2 | Ashley | Tianjin | 11000 |
| 3 | Rachel | Shijiazhuang | 9000 |
| 4 | Emily | Shenzhen | 7000 |
| 5 | Ashley | Nanjing | 16000 |
| … | … | … | … |
When there are no more than 9 terms in the specified constant sequence, we can use A.contain() function to perform filtering.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”).query@x(“select * from Employee”) |
| 2 | [Beijing, Shanghai, Guangzhou, Shenzhen] |
| 3 | =A1.select(A2.contain(CITY)) |
| 4 | =A3.groups(DEPT; avg(SALARY):SALARY) |
A1 Connect to the database and query Employee table.
A2 Create a sequence of constants, where members are first-tier cities.
A3 Get records whose cities are contained in the sequence of first-tier cities.
A4 Group selected records by department and calculate average salary in each department.
Execution result:
| DEPT | SALARY |
|---|---|
| Finance | 7833.33 |
| HR | 7187.5 |
| Marketing | 7977.27 |
| … | … |
SPL Official Website 👉 https://www.esproc.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.esproc.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/sxd59A8F2W
Youtube 👉 https://www.youtube.com/@esProc_SPL
