query-17dd828cced62e990ee0c16644ce5df1

rq turtle/ttl

A possible (elaborated) solution, using the initial modelComputes the sums of the months of a year if they exists, and also takes the values of the total year if they exists as well. Takes whichever is available for a year. If both are presents, computes the mean of both available values, and the max difference to the mean of some available value.

Use at

PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pqv: <http://www.wikidata.org/prop/qualifier/value/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
SELECT ?item ?itemLabel (avg(?number) as ?passenger) (max(avg(?number) - ?number) as ?plus_minus) ?year 

where {
  {
    select ?item ?year (sum(?numberperperiod) as ?number) {
       {
  select ?item ?statement ?year ?timevalue ?numberperperiod{
    ?item wdt:P238 ?IATA
    VALUES ?IATA {"JNB"}.
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue 
               ; ps:P3872 ?numberperperiod
               .
    ?timevalue wikibase:timeValue ?date .

    MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
    bind (YEAR(?date) AS ?year) 
    FILTER (?year >1985) .
  }
}.
      ?timevalue wikibase:timePrecision ?prec filter ( ?prec > 9 )
    } group by ?item ?year
  } union {
      ?timevalue wikibase:timePrecision 9 .
    bind (?numberperperiod as ?number)
       {
  select ?item ?statement ?year ?timevalue ?numberperperiod{
    ?item wdt:P238 ?IATA
    VALUES ?IATA {"JNB"}.
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue 
               ; ps:P3872 ?numberperperiod
               .
    ?timevalue wikibase:timeValue ?date .

    MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
    bind (YEAR(?date) AS ?year) 
    FILTER (?year >1985) .
  }
}  } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel ?year 
  order by ?item desc (?year)

Query found at

graph TD classDef projected fill:lightgreen; classDef literal fill:orange; classDef iri fill:yellow; v10("?IATA") v8("?date") v1("?item"):::projected v10("?number"):::projected v7("?numberperperiod") v10("?passenger") v11("?plus_minus") v3("?prec") v5("?statement") v6("?timevalue") v10("?year"):::projected a1((" ")):::projected c9(["wikibase:DeprecatedRank"]):::iri c12(["bd:serviceParam"]):::iri c1(["9^^xsd:integer"]):::literal c14(["#91;AUTO_LANGUAGE#93;,en"]):::literal subgraph union0[" Union "] subgraph union0l[" "] style union0l fill:#abf,stroke-dasharray: 3 3; v6 --"wikibase:timePrecision"--> c1 bind0[/"?numberperperiod"/] v7 --o bind0 bind0 --as--o v10 f1[["?year > '1985^^xsd:integer'"]] f1 --> v10 v1 --"p:direct/P238"--> v10 bind2[/VALUES ?IATA/] bind2-->v10 bind20(["JNB"]) bind20 --> bind2 v1 --"p:P3872"--> v5 v5 --"p:qualifier/value/P585"--> v6 v5 --"p:statement/P3872"--> v7 v6 --"wikibase:timeValue"--> v8 subgraph minus3["MINUS"] style minus3 stroke-width:6px,fill:pink,stroke:red; v5 --"wikibase:rank"--> c9 end bind4[/"year-from-dateTime(?date)"/] v8 --o bind4 bind4 --as--o v10 end subgraph union0r[" "] style union0r fill:#abf,stroke-dasharray: 3 3; f5[["?prec > '9^^xsd:integer'"]] f5 --> v3 f6[["?year > '1985^^xsd:integer'"]] f6 --> v10 v1 --"p:direct/P238"--> v10 bind7[/VALUES ?IATA/] bind7-->v10 bind70(["JNB"]) bind70 --> bind7 v1 --"p:P3872"--> v5 v5 --"p:qualifier/value/P585"--> v6 v5 --"p:statement/P3872"--> v7 v6 --"wikibase:timeValue"--> v8 subgraph minus8["MINUS"] style minus8 stroke-width:6px,fill:pink,stroke:red; v5 --"wikibase:rank"--> c9 end bind9[/"year-from-dateTime(?date)"/] v8 --o bind9 bind9 --as--o v10 v6 --"wikibase:timePrecision"--> v3 bind11[/"sum(?numberperperiod)"/] v7 --o bind11 bind11 --as--o v10 end union0r <== or ==> union0l end subgraph s1["http://wikiba.se/ontology#label"] style s1 stroke-width:4px; c12 --"wikibase:language"--> c14 end bind15[/"average(?number)"/] v10 --o bind15 bind15 --as--o v10 bind16[/"max( - ?number)"/] a1 --o bind16 v10 --o bind16 bind16 --as--o v11