6.5 Alignment grouping: keep all matching members for each group
Group data in order of the specified field in the base table and keep all matching members for each group. This type of alignment grouping is fit for computing scenarios where we want to know information of members in each group or where we need to use the member records to perform further statistical analysis.
【Example 1】 Based on the associated EMPLOYEE table and DEPARTMENT table, count employees in each department in the order of departments in the department table. Below shows the relationship between the two tables:
| EMPLOYEE |
|---|
| ID |
| NAME |
| DEPT |
| STATE |
| … |
| DEPARTMENT |
|---|
| NAME |
| MANAGER |
| … |
SPL offers @a option to work with A.align() function to keep all matching members for each group during an alignment grouping operation.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from EMPLOYEE”) |
| 3 | =A1.query@x(“select * from DEPARTMENT”) |
| 4 | =A2.align@a(A3:DEPT, DEPT) |
| 5 | =A3.new(DEPT, A4(#).count():COUNT) |
A1 Connect to the database.
A2 Query EMPLOYEE table.
A3 Query DEPARTMENT table.
A4 Use A.align@a function to group employee records by aligning them to the order of departments; @a option enables returning all matching members for each group.
A5: Create result sequence according to the order of departments in DEPARTMENT table, and count records in in group based on the result of A4’s alignment grouping to get the number of employees in each department.
Execution result:
| DEPT | COUNT |
|---|---|
| Administration | 4 |
| Finance | 24 |
| HR | 19 |
| … | … |
It is possible that an alignment grouping operation returns one or more empty groups that no members belong to.
【Example 2】 Based on the associated SelectCourse table and Course table, find the number of students who select each course according to the order of COURSE table. Below is the relationship between the two tables:
| SelectCourse |
|---|
| ID |
| CourseID |
| StudentID |
| … |
| Course |
|---|
| ID |
| Name |
| TeacherID |
| … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from SELECT_COURSE”) |
| 3 | =A1.query@x(“select * from COURSE”) |
| 4 | =A2.align@a(A3:ID,COURSEID) |
| 5 | =A3.new(ID, A4(#).len():COUNT) |
A1 Connect to the database.
A2 Query SelectCourse table.
A3 Query Course table.
A4 Use A.align@a() function to align records of SelectCourse table to ID field in the Course table, and keep all matching members for each group.
The result of an alignment grouping operation may contain one ore more empty groups, which means no students select the corresponding courses:
| Member |
|---|
| [] |
| [[13,2,7],[15,2,50],…] |
| [[7,3,41],[11,3,5],…] |
| [[45,4,28],[51,4,18],…] |
| [[3,5,52],[4,5,44],…] |
| … |
A5 Create the result sequence according to the order of ID fields in the Course table, and count records in each group based on the result of A4’s alignment grouping result to get the number of students who select the corresponding course.
Execution result:
| ID | COUNT |
|---|---|
| 1 | 0 |
| 2 | 6 |
| 3 | 5 |
| 4 | 4 |
| 5 | 11 |
| … | … |
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
