3.10 Belong to & Contain: check whether an object is a member of a set
Suppose there are set A and set B, if any member of set A is also a member of set B, set B is said to contain set A. The “belongs to” relationship exist between an element and a set. If member x exists in set A, the former is said to belong to the latter. For instance, [1,2,3] contains [1,2] and 1 belongs to [1,2,3].
Based on Employee table, calculate average salary in states of California, New York, Texas and Washington respectively. Below is part of Employee table:
| ID | NAME | SURNAME | STATE | DEPT | SALARY |
|---|---|---|---|---|---|
| 1 | Rebecca | Moore | California | R&D | 7000 |
| 2 | Ashley | Wilson | New York | Finance | 11000 |
| 3 | Rachel | Johnson | New Mexico | Sales | 9000 |
| 4 | Emily | Smith | Texas | HR | 7000 |
| 5 | Ashley | Smith | Texas | R&D | 16000 |
| … | … | … | … | … | … |
SPL uses A.contain(x) function to check whether x belongs to set A.
SPL script:
| A | |
|---|---|
| 1 | =T(“Employee.xlsx”) |
| 2 | [California,New York,Texas,Washington] |
| 3 | =A1.select(A2.contain(STATE)) |
| 4 | =A3.groups(DEPT; avg(SALARY):SALARY) |
A1: Import Employee table from the original file.
A2: Define a set of State constants.
A3: Get records from A1’s Employee table where states belong to A2’s set.
A4: Group A3’s records by department and calculate average salary in each department.
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
