RaqForum 25 No.
375 View  • 
     ...

6.11 Enumerated grouping: put non-matching members to a separate group

 
spl-cookbook(224)

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-cookbook(224)
 
 •  375 View  •