6.13 Group by segment: segment by field value
Group records by segment and perform count aggregation according to values of the specified field.
Group employees according to enumerated conditions, which are salary below 8000, between 8000 and 12000 and above 12000, according to employee salary table, and count employees in each group.
| ID | NAME | BIRTHDAY | SALARY |
|---|---|---|---|
| 1 | Rebecca | 1974-11-20 | 7000 |
| 2 | Ashley | 1980-07-19 | 11000 |
| 3 | Rachel | 1970-12-17 | 9000 |
| 4 | Emily | 1985-03-07 | 7000 |
| 5 | Ashley | 1975-05-13 | 16000 |
| … | … | … | … |
We can use pseg(x) function in the alignment function align(n,y) to divide records into multiple segments.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from EMPLOYEE”) |
| 3 | [0,8000,12000] |
| 4 | =A2.align@a(A3.len(),A3.pseg(SALARY)) |
| 5 | =A4.new(A3 (#):SALARY,~.count():COUNT) |
A1 Connect to the database.
A2 Query EMPLOYEE table.
A3 Define salary intervals.
A4 Use pseg function to get the interval a SALARY value belongs to.
A5 Find the number of employees in each group.
Execution result:
| SALARY | COUNT |
|---|---|
| 0 | 308 |
| 8000 | 153 |
| 12000 | 39 |
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
