10.6 Calculate age
Calculate ages according to birth dates.
Find average age of employees in each department. Below is Employee table:
| ID | NAME | BIRTHDAY | HIREDATE | 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 |
| … | … | … | … | … | … |
SPL provides age(x) to calculate the number of years between the specified date x and the current time.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Employee”) |
| 3 | =A1.groups(DEPT; avg(age(BIRTHDAY)):AvgAge) |
A1 Connect to database.
A2 Import Employee table.
A3 Group the table by department and calculate average age in each department, during which age() function is used to get age of each employee.
Execution result:
| DEPT | AvgAge |
|---|---|
| Administration | 43.5 |
| Finance | 38.83 |
| HR | 41.05 |
| … | … |
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
