How to get grand total in a sparql group by query

2019-08-19 05:02发布

问题:

I follow up on query where the schema.org database is used to find the number of children of a class. The answer gives for each class the number of children. In my application I need the grand total of all children (i.e. the sum of the counts for each group) in order to compute for each group the percentage of the total number of children. The query I got from the previous question is:

prefix schema:  <http://schema.org/>
prefix rdfs:    <http://www.w3.org/2000/01/rdf-schema#>

select   ?child (count(?grandchild) as ?nGrandchildren) 
from <http://localhost:3030/testDB/data/schemaOrg>
where {
  ?child rdfs:subClassOf schema:Event .
  optional { 
    ?grandchild rdfs:subClassOf ?child
  }
}  group by ?child

which gives the expected answer (events and number of children). How to get the total number? I tried a nested query as:

prefix schema:  <http://schema.org/>
prefix rdfs:    <http://www.w3.org/2000/01/rdf-schema#>

select   ?child (count(?grandchild) as ?nGrandchildren) 
from <http://localhost:3030/testDB/data/schemaOrg>
where {
  select (count(?grandchild) as ?grandTotal) 
  {?child rdfs:subClassOf schema:Event .
  optional { 
    ?grandchild rdfs:subClassOf ?child
     }
   }
}  group by ?child

but got a single answer: " " -> 0.

回答1:

This query uses two sub-SELECTs: * the first computes the number of grandchildren per child * the second returns the total number of grandchildren

prefix schema:  <http://schema.org/>
prefix rdfs:    <http://www.w3.org/2000/01/rdf-schema#>

select ?child ?nGrandchildren 
(round(?nGrandchildren/?totalGrandchildren * 100) as ?percentageGrandchildren) {

# compute number per child
{
select ?child (count(?grandchild) as ?nGrandchildren) where {
  ?child rdfs:subClassOf schema:Event .
  optional { 
    ?grandchild rdfs:subClassOf ?child
  }
}
group by ?child
}

# compute total number
{
select (count(?grandchild) as ?totalGrandchildren) where {
  ?child rdfs:subClassOf schema:Event .
  optional { 
    ?grandchild rdfs:subClassOf ?child
  }
}
}

}

Output

-----------------------------------------------------------------------------------------------
| child                   | nGrandchildren | percentageGrandchildren                          |
===============================================================================================
| schema:UserInteraction  | 9              | "82"^^<http://www.w3.org/2001/XMLSchema#decimal> |
| schema:FoodEvent        | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:MusicEvent       | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:PublicationEvent | 2              | "18"^^<http://www.w3.org/2001/XMLSchema#decimal> |
| schema:LiteraryEvent    | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:SportsEvent      | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:DanceEvent       | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:ScreeningEvent   | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:DeliveryEvent    | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:ExhibitionEvent  | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:EducationEvent   | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:SaleEvent        | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:VisualArtsEvent  | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:CourseInstance   | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:ChildrensEvent   | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:BusinessEvent    | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:Festival         | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:ComedyEvent      | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:TheaterEvent     | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
| schema:SocialEvent      | 0              | "0"^^<http://www.w3.org/2001/XMLSchema#decimal>  |
-----------------------------------------------------------------------------------------------


标签: sparql