6.19 Order-based grouping: by ordinal numbers of groups
On some occasions we can obtain the ordinal number of each group (which group a member should be put in) in a direct or indirect way so that we can group members by ordinal numbers of the corresponding groups.
Divide employees evenly into three groups according to their hire dates (put extra members to a separate group in their order if there is a remainder), and calculate average salary in each group. Below is part of the employee data:
| ID | NAME | BIRTHDAY | ENTRYDATE | DEPT | SALARY |
|---|---|---|---|---|---|
| 1 | Rebecca | 1974/11/20 | 2005/03/11 | R&D | 7000 |
| 2 | Ashley | 1980/07/19 | 2008/03/16 | Finance | 11000 |
| 3 | Rachel | 1970/12/17 | 2010/12/01 | Sales | 9000 |
| 4 | Emily | 1985/03/07 | 2006/08/15 | HR | 7000 |
| 5 | Ashley | 1975/05/13 | 2004/07/30 | R&D | 16000 |
| … | … | … | … | … | … |
A.group()function can work with @n option to group members by ordinal numbers of groups, during which those corresponding to same ordinal number will be placed into to same group (For example, members matching ordinal number N will be put into the Nth group; N starts from 1).
SPL script:
| A | |
|---|---|
| 1 | =T(“Employee.csv”).sort(ENTRYDATE) |
| 2 | =A1.group@n((#-1)*3\A1.len()+ 1) |
| 3 | =A2.new(#:GROUP_NO, ~.avg(SALARY):AVG_SALARY) |
A1 Import Employee table and sort it by hire date.
A2 Get the ordinal number of group to which a record belongs to according to the row number after sorting, and group records by ordinal numbers.
A3 Calculate average salary in each group.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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
