Virtuoso Aggregate Query
Virtuoso is an open source Graph database that stores and queries based on the RDF data model. For aggregate queries, Virtuoso supports common aggregate functions such as COUNT, SUM, AVG, MIN, MAX, etc. At the same time, it also supports the GROUP BY clause for grouping. The following is an example that introduces table structure, sample data, and various aggregation queries:
Table structure and sample data:
Suppose we have a Graph database that stores information about people and their cities. The following are examples of related RDF triples stored in the Graph database:
- `<person1> <name> "John" .`
- `<person1> <city> "New York" .`
- `<person2> <name> "Jane" .`
- `<person2> <city> "Los Angeles" .`
- `<person3> <name> "Bob" .`
- `<person3> <city> "New York" .`
- `<person4> <name> "Alice" .`
- `<person4> <city> "Chicago" .`
1. COUNT aggregation query:
Query the total number of people in the database.
sql
SELECT COUNT(?person) AS ?totalPersons
WHERE {
?person <name> ?name .
}
Result:
totalPersons
-----------------------
4
2. SUM aggregation query:
Query the total number of people with the same city.
sql
SELECT ?city, COUNT(?person) AS ?totalPersons
WHERE {
?person <name> ?name .
?person <city> ?city .
}
GROUP BY ?city
Result:
City | totalPersons
---------------------------------
New York | 2
Los Angeles | 1
Chicago | 1
3. AVG aggregation query:
Query the average number of people in each city.
sql
SELECT ?city, AVG(?personCount) AS ?avgPersons
WHERE {
{
SELECT ?city, COUNT(?person) AS ?personCount
WHERE {
?person <name> ?name .
?person <city> ?city .
}
GROUP BY ?city
}
}
GROUP BY ?city
Result:
City | avgPersons
---------------------------------
New York | 1.0
Los Angeles | 1.0
Chicago | 1.0
4. MIN aggregation query:
Search for the person with the smallest name.
sql
SELECT ?person, ?name
WHERE {
?person <name> ?name .
}
ORDER BY ?name
LIMIT 1
Result:
Person | name
------------------------
<person4>| "Alice"
5. MAX aggregation query:
Search for the person with the largest name.
sql
SELECT ?person, ?name
WHERE {
?person <name> ?name .
}
ORDER BY DESC(?name)
LIMIT 1
Result:
Person | name
------------------------
| "John"
Here are some simple examples of aggregated queries in Virtuoso, where you can use more complex queries and patterns as needed.
