1.15 Grouping: get top/bottom N without keeping the grouped subsets
groups() function groups records and performs cumulative aggregation during which no grouped subsets are generated. This type of operation is more efficient when grouped subsets are not needed the second time.
Take the Employee table as an example. Find information of employees in each department whose salaries rank in top 3.
| EID | NAME | DEPT | SALARY |
|---|---|---|---|
| 1 | Rebecca | R&D | 7000 |
| 2 | Ashley | Finance | 11000 |
| 3 | Rachel | Sales | 9000 |
| 4 | Emily | HR | 7000 |
| 5 | Ryan | R&D | 13000 |
| … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from EMPLOYEE”) |
| 3 | =A2.groups(DEPT; top(-3;SALARY):TopSalary) |
| 4 | =A3.(TopSalary).conj() |
A1 Connect to the database;
A2 Query EMPLOYEE table;
A3 Group A2’s records by department and from each group get records where salaries rank in top 3;
A4 Concatenate eligible records in all department.
Execution result:
| EID | NAME | DEPT | SALARY |
|---|---|---|---|
| 20 | Alexis | Administration | 16000 |
| 42 | Michael | Administration | 12000 |
| 18 | Jonathan | Administration | 7000 |
| 2 | Ashley | Finance | 11000 |
| 32 | Andrew | Finance | 11000 |
| … | … | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
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
