6.11 Enumerated grouping: put non-matching members to a separate group
When grouping data according to enumerated conditions, there may be members that do not meet any of the condition and we can put them into a new group. This is fit for computing scenarios where we want to know information of matching members as well as of non-matching members.
Group employees according to different age groups and calculate average salary in each group. There will be three groups – below 35, below 45 and others. Here’s part data in the employee table:
| ID | NAME | BIRTHDAY | STATE | DEPT | SALARY |
|---|---|---|---|---|---|
| 1 | Rebecca | 1974/11/20 | California | R&D | 7000 |
| 2 | Ashley | 1980/07/19 | New York | Finance | 11000 |
| 3 | Rachel | 1970/12/17 | New Mexico | Sales | 9000 |
| 4 | Emily | 1985/03/07 | Texas | HR | 7000 |
| 5 | Ashley | 1975/05/13 | Texas | R&D | 16000 |
| … | … | … | … | … | … |
SPL has @n option to use in A.enum() function to place non-matching members in an enumerated grouping operation in a separate group.
SPL script:
| A | |
|---|---|
| 1 | =T(“Employee.csv”) |
| 2 | [?<35,?<45] |
| 3 | =A1.enum@n(A2, age(BIRTHDAY)) |
| 4 | [Below 35,Below 45,Others] |
| 5 | =A3.new(A4(#):AGE_AREA,~.avg(SALARY):AVG_SALARY) |
A1 Import Employee table.
A2 Define grouping conditions.
A3 Use A.enum() function to perform the enumerated grouping operation according to the grouping conditions; @n option enables putting non-matching members to a new group.
A4 Define names of groups.
A5 Calculate average salary of each employee according to result of the enumerated grouping operation.
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
