SPARQL AVG Aggregation Function: Calculating the Average Age of Managers

  

In previous examples, we explored aggregation functions such as COUNT, MIN, and MAX to summarize information stored in an RDF graph. Another powerful aggregation function provided by SPARQL is AVG, which computes the arithmetic mean of a set of numeric values.

 

In this post, we'll use a company employee dataset and answer the following question "What is the average age of all managers in the organization?".

 

employee.turtle

PREFIX : <http://example.org/company/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>


####################################################
# Locations
####################################################

:Chennai a :City ;
    :cityName "Chennai" .

:Bangalore a :City ;
    :cityName "Bangalore" .

:Hyderabad a :City ;
    :cityName "Hyderabad" .

####################################################
# Departments
####################################################

:Engineering a :Department ;
    :departmentName "Engineering" .

:QualityAssurance a :Department ;
    :departmentName "Quality Assurance" .

####################################################
# Employees
####################################################

:JohnSmith
    a :Employee ;
    :employeeId "E001" ;
    :name "John Smith" ;
    :role "Director" ;
    :age 55 ;
    :dateOfBirth "1971-04-15"^^xsd:date ;
    :joinedOn "2010-03-15"^^xsd:date ;
    :salary 180000 ;
    :worksInDepartment :Engineering ;
    :worksInCity :Bangalore .

:SarahJohnson
    a :Employee ;
    :employeeId "E002" ;
    :name "Sarah Johnson" ;
    :role "Manager" ;
    :age 42 ;
    :dateOfBirth "1984-08-22"^^xsd:date ;
    :joinedOn "2016-07-10"^^xsd:date ;
    :salary 125000 ;
    :worksInDepartment :Engineering ;
    :worksInCity :Chennai ;
    :reportsTo :JohnSmith .

:MichaelBrown
    a :Employee ;
    :employeeId "E003" ;
    :name "Michael Brown" ;
    :role "Manager" ;
    :age 48 ;
    :dateOfBirth "1978-02-11"^^xsd:date ;
    :joinedOn "2014-01-22"^^xsd:date ;
    :salary 135000 ;
    :worksInDepartment :Engineering ;
    :worksInCity :Bangalore ;
    :reportsTo :JohnSmith .

:EmmaWilson
    a :Employee ;
    :employeeId "E004" ;
    :name "Emma Wilson" ;
    :role "Developer" ;
    :age 30 ;
    :dateOfBirth "1996-06-08"^^xsd:date ;
    :joinedOn "2021-06-01"^^xsd:date ;
    :salary 90000 ;
    :worksInDepartment :Engineering ;
    :worksInCity :Chennai ;
    :reportsTo :SarahJohnson .

:DavidMiller
    a :Employee ;
    :employeeId "E005" ;
    :name "David Miller" ;
    :role "Developer" ;
    :age 34 ;
    :dateOfBirth "1992-09-17"^^xsd:date ;
    :joinedOn "2020-11-15"^^xsd:date ;
    :salary 95000 ;
    :worksInDepartment :Engineering ;
    :worksInCity :Chennai ;
    :reportsTo :SarahJohnson .

:SophiaDavis
    a :Employee ;
    :employeeId "E006" ;
    :name "Sophia Davis" ;
    :role "QA Engineer" ;
    :age 29 ;
    :dateOfBirth "1997-01-28"^^xsd:date ;
    :joinedOn "2022-02-10"^^xsd:date ;
    :salary 85000 ;
    :worksInDepartment :QualityAssurance ;
    :worksInCity :Hyderabad ;
    :reportsTo :MichaelBrown .

:JamesTaylor
    a :Employee ;
    :employeeId "E007" ;
    :name "James Taylor" ;
    :role "QA Engineer" ;
    :age 32 ;
    :dateOfBirth "1994-12-05"^^xsd:date ;
    :joinedOn "2023-01-05"^^xsd:date ;
    :salary 87000 ;
    :worksInDepartment :QualityAssurance ;
    :worksInCity :Hyderabad ;
    :reportsTo :MichaelBrown .

Understanding the Data

Our RDF graph stores information about employees, including:

 

·      Employee ID

·      Name

·      Role

·      Age

·      Date of Birth

·      Joining Date

·      Salary

·      Department

·      City

·      Reporting Hierarchy

 

For example, the graph contains the following manager records:

 

Employee	Role	Age
Sarah Johnson	Manager	42
Michael Brown	Manager	48

 

Our goal is to calculate the average of these age values. Mathematically: (42 + 48) / 2 = 45, therefore, we expect the result to be 45 years.

 

Writing the Query

First, let's find all the managers and their respecitve ages.

 

PREFIX : <http://example.org/company/>

SELECT ?name ?age
WHERE { 
  ?person a :Employee ;
          :name ?name ;
          :role "Manager" ;
          :age ?age
}

   

Output

name	age
Michael Brown	48
Sarah Johnson	42

 

Let's use AVG function to calculate the average age.

 

SELECT (AVG(?age) AS ?avergaeAgeOfManagers)

 

Updated Query:

 

PREFIX : <http://example.org/company/>

SELECT (AVG(?age) AS ?avergaeAgeOfManagers)
WHERE { 
  ?person a :Employee ;
          :role "Manager" ;
          :age ?age
}

   

Output

 

avergaeAgeOfManagers
45

   

Why Use AVG?

The AVG() function is useful whenever you need to answer questions such as:

 

·      What is the average age of employees?

·      What is the average salary of managers?

·      What is the average tenure of employees?

·      What is the average project duration?

·      What is the average customer rating?

 

Instead of exporting data to another system and performing calculations manually, SPARQL allows these computations to be performed directly against the RDF graph.

 

Another Example: Average Salary of Managers

Using the same dataset, calculating the average manager salary becomes straightforward:

 

PREFIX : <http://example.org/company/>

SELECT (AVG(?salary) AS ?avergaeSalaryOfEmployees)
WHERE { 
  ?person a :Employee ;
          :role "Manager" ;
          :salary ?salary .
}

   

Output

 

avergaeSalaryOfEmployees
130000

   

In summray, The AVG() aggregation function calculates the arithmetic mean of numeric values returned by a query.

 

General syntax:

 

SELECT (AVG(?variable) AS ?average)
WHERE {
    ...
}

Along with COUNT(), SUM(), MIN(), and MAX(), AVG() is one of the most frequently used aggregation functions in SPARQL and is extremely useful for generating analytical insights directly from RDF data.

Previous                                                    Next                                                    Home