SPARQL CONTRUCT ordered list with ORDER BY and GROUP BY

Question

Dear RDF/SPARQL community,

my goal is to construct an ordered list grouped by products and ordered by time by the means of SPARQL to show the track of a product. I am working with GraphDB, "scmon" is my custom ontology. And here are the details:

I have stored RDF data that hold the timestamps (scmon:atTime) and positions (scmon:atLocation) of products. In Turtle syntax it looks like this:

@base <http://example/base/testtracks> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
@prefix scmon: <http://www.semanticweb.org/sildop/ontologies/2020/scmon#> .

<tracks5> a scmon:NetPosition;
  scmon:atTime "2018-12-10T14:48:59Z"^^xsd:dateTimeStamp;
  scmon:atLocation <5>;
  scmon:positionOf <product1> .

<tracks6> a scmon:NetPosition;
  scmon:atTime "2018-12-12T13:37:19Z"^^xsd:dateTimeStamp;
  scmon:atLocation <5002>;
  scmon:positionOf <product1> .

<tracks7> a scmon:NetPosition;
  scmon:atTime "2018-12-13T19:46:00Z"^^xsd:dateTimeStamp;
  scmon:atLocation <2>;
  scmon:positionOf <product1> .

<tracks17> a scmon:NetPosition;
  scmon:atTime "2018-12-10T14:48:59Z"^^xsd:dateTimeStamp;
  scmon:atLocation <4953>;
  scmon:positionOf <product2> .

<tracks18> a scmon:NetPosition;
  scmon:atTime "2018-12-12T13:37:19Z"^^xsd:dateTimeStamp;
  scmon:atLocation <4953004195>;
  scmon:positionOf <product2> .

<tracks19> a scmon:NetPosition;
  scmon:atTime "2018-12-13T19:46:00Z"^^xsd:dateTimeStamp;
  scmon:atLocation <4176>;
  scmon:positionOf <product2> .

As you may see, every track entry is assigned to a product via the scmon:positionOf relation.

My goal is now to construct an ordered list grouped by products and ordered by time by the means of SPARQL to show the track of a product in a form similar to:

<product1> scmon:hasPosSequence <track5>
  <track5> scmon:nextPosition <track6>
  <track6> scmon:nextPosition <track7>
  <track7> scmon:nextPosition NULL
<product2> ...

This approach was inspired by the W3C example Pattern 2 for n-ary relations (https://www.w3.org/TR/swbp-n-aryRelations/#pattern2) which looks like this: https://www.w3.org/TR/swbp-n-aryRelations/flight_example.jpg

Also my custom ontology with the prefix scmon has adapted the suggested structure (with similar named classes and relations) that looks like this https://www.w3.org/TR/swbp-n-aryRelations/flight_classes.jpg

I was able to construct the first relation/line for <scmon:hasPosSequence> that points from the product ?product to the first position ?netPos that is found by (min(?time))...

PREFIX scmon: <http://www.semanticweb.org/sildop/ontologies/2020/scmon#>
CONSTRUCT {?product scmon:hasPosSequence ?netPos}
where {
    {
    ?product ^scmon:positionOf ?netPos .
    ?netPos scmon:atTime ?startTime .
    }
    {
                select ?product (min(?time) as ?startTime)
                where {
                    ?netPos scmon:positionOf ?product .
                    ?netPos scmon:atTime ?time .
                } group by ?product 
            }
        }

...but how could I go on from there? I have tried some SPARQL SELECT queries to test if I may select the information that is necessary to construct the scmon:nextPosition relation, but with no success.

Is it possible to construct this pattern? Or do I have to change my initial RDF data model somehow?

Thanks in advance for any hint!

Stanislav Kralin Stanislav Kralin · Accepted Answer · 2021-01-09T17:55:53

This way:

PREFIX scmon: <http://www.semanticweb.org/sildop/ontologies/2020/scmon#>

CONSTRUCT {
  ?product scmon:hasPosSequence ?track0 .
  ?track1 scmon:nextPosition ?track3
} WHERE {
  
  ?track1 scmon:positionOf ?product ;  scmon:atTime ?time1 .
    
  OPTIONAL {
    ?track2 scmon:positionOf ?product ;  scmon:atTime ?time2 .
    FILTER (?time2 > ?time1)
    FILTER NOT EXISTS {
      ?track_ scmon:positionOf ?product ;  scmon:atTime ?time_
      FILTER (?time_ > ?time1 && ?time_ < ?time2)
    }
  }
  BIND (COALESCE(?track2, scmon:NULL, ?track2) AS ?track3)
    
  ?track0 scmon:positionOf ?product ;  scmon:atTime ?time0 .
  FILTER NOT EXISTS {
    ?track_ scmon:positionOf ?product ;  scmon:atTime ?time_
    FILTER (?time_ < ?time0)
    }
}

By the way, if you need to number positions, try the following:

SELECT ?product ?track (COUNT(?track_) AS ?position) {
  ?track scmon:positionOf ?product ;  scmon:atTime ?time .
  ?track_ scmon:positionOf ?product ;  scmon:atTime ?time_
  FILTER (?time >= ?time_)                         
} GROUP BY ?product ?track ORDER BY ?product ?position

As for NULLs in RDF, see this discussion. If you really want them to be represented explicitely, you could use rdf:Lists which end with rdf:nil.

SPARQL CONTRUCT ordered list with ORDER BY and GROUP BY

1 Answers