6.14 Group by segment: segment by expression
Group records by segment and calculate average value in each group according to the result of computing the specified expression.
Group employee table according to the conditions that the hire duration is below 10 years, between 10 to 20 years and above 20 years, and calculate average salary in each group.
| ID | NAME | HIREDATE | SALARY |
|---|---|---|---|
| 1 | Rebecca | 2005-03-11 | 7000 |
| 2 | Ashley | 2008-03-16 | 11000 |
| 3 | Rachel | 2010-12-01 | 9000 |
| 4 | Emily | 2006-08-15 | 7000 |
| 5 | Ashley | 2004-07-30 | 16000 |
| … | … | … | … |
We can use pseg(x,y) function in the algin(n,y) function to group records by segment.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from EMPLOYEE”) |
| 3 | [0,10,20] |
| 4 | =now() |
| 5 | =A2.align@a(A3.len(),A3.pseg(elapse@y(A4,-~), HIREDATE)) |
| 6 | =A5.new(A3(#):EntryYears,~.avg(SALARY):AvgSalary) |
A1 Connect to the database.
A2 Query EMPLOYEE table.
A3 Define hire duration intervals.
A4 Get the current datetime.
A5 Use pseg function to get the interval a hire date belongs to.
A6 Calculate the average salary for each group.
Execution result:
| EntryYears | AvgSalary |
|---|---|
| 0 | 6777.78 |
| 10 | 7445.53 |
| 20 | 6928.57 |
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
