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