I have a simple RDF Graph with the following form
v:order1 v:time "2016-01-18T04:00:01.536"^^xsd:dateTime .
v:order1 v:value "3118915.75"^^xsd:string .
...
v:orderN v:time "2016-01-18T04:59:58.536"^^xsd:dateTime .
v:orderN v:value "4117779.00"^^xsd:string .
The graph records values for specific timestamps; therefore, a lot of data is generated in one day.
I need to obtain the difference in the time interval of one hour, from the last v:orderN and the first v:Order1 value of each hour for a SPARQL 1.1. Any ideas how can I achieve this?
A minimal example that we can work with always helps a lot. In the future, please provide data that we can run queries against, and let us know what the desired outcomes are. Since you didn't specify otherwise, I'm going to assume that the dataset only looks at dates within a one day range, so that we can group these just based on the hour, and we don't have to worry about the particular date. Here's some data where in the hour 04, there's a change from 10 to 12 (so the difference is 2), and in the hour 05, there's a change from 40 to 50 (so the difference is 10). Also note that I'm using xsd:decimal values, since talking about the difference of strings doesn't really make sense.
@prefix v: <urn:ex:> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
v:order1 v:time "2016-01-18T04:00:01.536"^^xsd:dateTime .
v:order1 v:value "10.0"^^xsd:decimal .
v:order2 v:time "2016-01-18T04:32:58.536"^^xsd:dateTime .
v:order2 v:value "11.00"^^xsd:decimal .
v:order3 v:time "2016-01-18T04:59:58.536"^^xsd:dateTime .
v:order3 v:value "12.00"^^xsd:decimal .
v:order4 v:time "2016-01-18T05:00:01.536"^^xsd:dateTime .
v:order4 v:value "40.00"^^xsd:decimal .
v:order5 v:time "2016-01-18T05:59:58.536"^^xsd:dateTime .
v:order5 v:value "50.00"^^xsd:decimal .prefix v: <urn:ex:>
The query is pretty straightforward. To find the latest value in an hour, you just find the entry for which there's no later value in the same hour. Similarly for the earliest value in an hour. Then you just compute the difference.
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
select ?hour (?latest - ?earliest as ?difference) {
{ select (hours(?time) as ?hour) ?earliest {
?order v:time ?time ; v:value ?earliest
filter not exists {
?order_ v:time ?time_
filter (hours(?time_) = hours(?time) && ?time_ < ?time)
}
}
}
{ select (hours(?time) as ?hour) ?latest {
?order v:time ?time ; v:value ?latest
filter not exists {
?order_ v:time ?time_
filter (hours(?time_) = hours(?time) && ?time < ?time_)
}
}
}
}
---------------------
| hour | difference |
=====================
| 04 | 2.00 |
| 05 | 10.00 |
---------------------