query-3f1bbef6a4695ce686ebb75e65a6c2b7
01:40, 29 December 2020 (UTC)) talk (Tagishsimon --Very grateful for any suggestions - I banged my head against the walll for hours before giving up and writing a script to reassemble the lines (which merely changed the wall I was hitting my head against... :-).)11:08, 29 December 2020 (UTC)) talk (Andrew Gray (never changed seat or party). should have just one (Q9545)Tony Blair (all the same seat, but switched into and out of a party). Someone straightforward like should have three (Q3330215)Paul Marsden (two of which are the same seat different parties), and should have six (Q8016)Winston Churchill should have five entries (he had four periods of continuing service, but the last one was over two seats). (Q335791)Herbert Morrison The target here is one line per "period of continuing service in the same seat and party", so as a couple of other test cases 12:15, 29 December 2020 (UTC)) talk (Andrew GraySo we can now deduce what the end date should be for each start date line. The one clause in the outer query says "for a given start date, the end date has to be larger" (obviously), throwing out any that are earlier. The main SELECT clause then has a MIN(?end) element, meaning that it will select the lowest date left to it - which is the next one in sequence. It seems to give the right results for Morrison (below). but also for Churchill and Marsden : I think I've got it! This query runs the same item as two subqueries, one finding all the seat/party/start date pairs, and one finding all the seat/party/end date pairs. Each one throws out any with blank fields so each one is returning only "valid" sets. We know that the corresponding end date is logically the one immediately following the start date - because terms shouldn't overlap.Tagishsimon@
Use at
- https://query.wikidata.org/sparql
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start (min(?end) as ?end2) where {
# find all seat-party-start pairs
{ SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start
WHERE {
VALUES ?mp { wd:Q335791 }
?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. ?ps pq:P768 ?seat . ?ps pq:P4100 ?party .
{ ?ps pq:P580 ?start. filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
?ps0 pq:P4100 ?party . ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . } }
union
{ ?ps pq:P582 ?end. filter not exists { ?ps pq:P1534 wd:Q741182 . ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term .
?ps2 pq:P4100 ?party . ?ps2 pq:P768 ?seat . ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . } }
filter(bound(?start))
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}ORDER BY (?mp) ?start }
# and all possible end dates
{ SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?end
WHERE {
VALUES ?mp { wd:Q335791 }
?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. ?ps pq:P768 ?seat . ?ps pq:P4100 ?party .
{ ?ps pq:P580 ?start. filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
?ps0 pq:P4100 ?party . ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . } }
union
{ ?ps pq:P582 ?end. filter not exists { ?ps pq:P1534 wd:Q741182 . ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term .
?ps2 pq:P4100 ?party . ?ps2 pq:P768 ?seat . ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . } }
filter(bound(?end))
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}ORDER BY (?mp)?end }
# now take our starts as the key, and match each to its appropriate end - the next one along
# this is the *smallest* end date which is still *larger* than the start date
# so filter by larger here, and smallest using min in the SELECT clause
filter(?end > ?start) . # note > not >=
} group by ?mp ?mpLabel ?partyLabel ?seatLabel ?start order by ?start
Query found at
graph TD
classDef projected fill:lightgreen;
classDef literal fill:orange;
classDef iri fill:yellow;
v8("?elec")
v2("?end"):::projected
v13("?end2")
v13("?mp"):::projected
v7("?party")
v4("?ps")
v9("?ps0")
v11("?ps2")
v6("?seat")
v1("?start"):::projected
v5("?term")
v10("?term0")
v12("?term2")
c12(["wd:Q741182"]):::iri
c4(["wd:Q16707842"]):::iri
c17(["bd:serviceParam"]):::iri
c9(["wd:Q15283424"]):::iri
c19(["#91;AUTO_LANGUAGE#93;,en"]):::literal
f0[["?end > ?start"]]
f0 --> v2
f0 --> v1
f1[["bound(?start)"]]
f1 --> v1
bind2[/VALUES ?mp/]
bind2-->v13
bind20(["wd:Q335791"])
bind20 --> bind2
v13 --"p:P39"--> v4
v4 --"p:statement/P39"--> v5
v5 --"p:direct/P279"--> c4
v4 --"p:qualifier/P768"--> v6
v4 --"p:qualifier/P4100"--> v7
subgraph union0[" Union "]
subgraph union0l[" "]
style union0l fill:#abf,stroke-dasharray: 3 3;
f3[["not "]]
subgraph f3e0["Exists Clause"]
e0v1 --"p:qualifier/P1534"--> e0c2
e0v2 --"p:P39"--> e0v3
e0v3 --"p:statement/P39"--> e0v4
e0v4 --"p:direct/P155"--> e0v5
e0v3 --"p:qualifier/P4100"--> e0v6
e0v3 --"p:qualifier/P768"--> e0v7
e0v3 --"p:qualifier/P2715"--> e0v8
e0v8 --"p:direct/P31"--> e0c10
e0v8("?elec"):::projected
e0v2("?mp"):::projected
e0v6("?party"):::projected
e0v1("?ps"):::projected
e0v3("?ps2"):::projected
e0v7("?seat"):::projected
e0v5("?term"):::projected
e0v4("?term2"):::projected
e0c2(["wd:Q741182"]):::iri
e0c10(["wd:Q15283424"]):::iri
end
f3--EXISTS--> f3e0
f3 --> v4
f3 --> c11
f3 --> c12
f3 --> v13
f3 --> c1
f3 --> v11
f3 --> c2
f3 --> v12
f3 --> c14
f3 --> v5
f3 --> c6
f3 --> v7
f3 --> c5
f3 --> v6
f3 --> c7
f3 --> v8
f3 --> c8
f3 --> c9
v4 --"p:qualifier/P1534"--> c12
v13 --"p:P39"--> v11
v11 --"p:statement/P39"--> v12
v12 --"p:direct/P155"--> v5
v11 --"p:qualifier/P4100"--> v7
v11 --"p:qualifier/P768"--> v6
v11 --"p:qualifier/P2715"--> v8
v8 --"p:direct/P31"--> c9
v4 --"p:qualifier/P582"--> v2
end
subgraph union0r[" "]
style union0r fill:#abf,stroke-dasharray: 3 3;
f4[["not "]]
subgraph f4e1["Exists Clause"]
e1v1 --"p:qualifier/P2715"--> e1v2
e1v2 --"p:direct/P31"--> e1c3
e1v3 --"p:P39"--> e1v4
e1v4 --"p:statement/P39"--> e1v5
e1v5 --"p:direct/P156"--> e1v6
e1v4 --"p:qualifier/P4100"--> e1v7
e1v4 --"p:qualifier/P768"--> e1v8
e1v4 --"p:qualifier/P1534"--> e1c10
e1v2("?elec"):::projected
e1v3("?mp"):::projected
e1v7("?party"):::projected
e1v1("?ps"):::projected
e1v4("?ps0"):::projected
e1v8("?seat"):::projected
e1v6("?term"):::projected
e1v5("?term0"):::projected
e1c10(["wd:Q741182"]):::iri
e1c3(["wd:Q15283424"]):::iri
end
f4--EXISTS--> f4e1
f4 --> v4
f4 --> c7
f4 --> v8
f4 --> c8
f4 --> c9
f4 --> v13
f4 --> c1
f4 --> v9
f4 --> c2
f4 --> v10
f4 --> c10
f4 --> v5
f4 --> c6
f4 --> v7
f4 --> c5
f4 --> v6
f4 --> c11
f4 --> c12
v4 --"p:qualifier/P2715"--> v8
v8 --"p:direct/P31"--> c9
v13 --"p:P39"--> v9
v9 --"p:statement/P39"--> v10
v10 --"p:direct/P156"--> v5
v9 --"p:qualifier/P4100"--> v7
v9 --"p:qualifier/P768"--> v6
v9 --"p:qualifier/P1534"--> c12
v4 --"p:qualifier/P580"--> v1
end
union0r <== or ==> union0l
end
subgraph s1["http://wikiba.se/ontology#label"]
style s1 stroke-width:4px;
c17 --"wikibase:language"--> c19
end
f5[["bound(?end)"]]
f5 --> v2
bind6[/VALUES ?mp/]
bind6-->v13
bind60(["wd:Q335791"])
bind60 --> bind6
v13 --"p:P39"--> v4
v4 --"p:statement/P39"--> v5
v5 --"p:direct/P279"--> c4
v4 --"p:qualifier/P768"--> v6
v4 --"p:qualifier/P4100"--> v7
subgraph union1[" Union "]
subgraph union1l[" "]
style union1l fill:#abf,stroke-dasharray: 3 3;
f7[["not "]]
subgraph f7e2["Exists Clause"]
e2v1 --"p:qualifier/P1534"--> e2c2
e2v2 --"p:P39"--> e2v3
e2v3 --"p:statement/P39"--> e2v4
e2v4 --"p:direct/P155"--> e2v5
e2v3 --"p:qualifier/P4100"--> e2v6
e2v3 --"p:qualifier/P768"--> e2v7
e2v3 --"p:qualifier/P2715"--> e2v8
e2v8 --"p:direct/P31"--> e2c10
e2v8("?elec"):::projected
e2v2("?mp"):::projected
e2v6("?party"):::projected
e2v1("?ps"):::projected
e2v3("?ps2"):::projected
e2v7("?seat"):::projected
e2v5("?term"):::projected
e2v4("?term2"):::projected
e2c2(["wd:Q741182"]):::iri
e2c10(["wd:Q15283424"]):::iri
end
f7--EXISTS--> f7e2
f7 --> v4
f7 --> c11
f7 --> c12
f7 --> v13
f7 --> c1
f7 --> v11
f7 --> c2
f7 --> v12
f7 --> c14
f7 --> v5
f7 --> c6
f7 --> v7
f7 --> c5
f7 --> v6
f7 --> c7
f7 --> v8
f7 --> c8
f7 --> c9
v4 --"p:qualifier/P1534"--> c12
v13 --"p:P39"--> v11
v11 --"p:statement/P39"--> v12
v12 --"p:direct/P155"--> v5
v11 --"p:qualifier/P4100"--> v7
v11 --"p:qualifier/P768"--> v6
v11 --"p:qualifier/P2715"--> v8
v8 --"p:direct/P31"--> c9
v4 --"p:qualifier/P582"--> v2
end
subgraph union1r[" "]
style union1r fill:#abf,stroke-dasharray: 3 3;
f8[["not "]]
subgraph f8e3["Exists Clause"]
e3v1 --"p:qualifier/P2715"--> e3v2
e3v2 --"p:direct/P31"--> e3c3
e3v3 --"p:P39"--> e3v4
e3v4 --"p:statement/P39"--> e3v5
e3v5 --"p:direct/P156"--> e3v6
e3v4 --"p:qualifier/P4100"--> e3v7
e3v4 --"p:qualifier/P768"--> e3v8
e3v4 --"p:qualifier/P1534"--> e3c10
e3v2("?elec"):::projected
e3v3("?mp"):::projected
e3v7("?party"):::projected
e3v1("?ps"):::projected
e3v4("?ps0"):::projected
e3v8("?seat"):::projected
e3v6("?term"):::projected
e3v5("?term0"):::projected
e3c10(["wd:Q741182"]):::iri
e3c3(["wd:Q15283424"]):::iri
end
f8--EXISTS--> f8e3
f8 --> v4
f8 --> c7
f8 --> v8
f8 --> c8
f8 --> c9
f8 --> v13
f8 --> c1
f8 --> v9
f8 --> c2
f8 --> v10
f8 --> c10
f8 --> v5
f8 --> c6
f8 --> v7
f8 --> c5
f8 --> v6
f8 --> c11
f8 --> c12
v4 --"p:qualifier/P2715"--> v8
v8 --"p:direct/P31"--> c9
v13 --"p:P39"--> v9
v9 --"p:statement/P39"--> v10
v10 --"p:direct/P156"--> v5
v9 --"p:qualifier/P4100"--> v7
v9 --"p:qualifier/P768"--> v6
v9 --"p:qualifier/P1534"--> c12
v4 --"p:qualifier/P580"--> v1
end
union1r <== or ==> union1l
end
subgraph s1["http://wikiba.se/ontology#label"]
style s1 stroke-width:4px;
c17 --"wikibase:language"--> c19
end
bind10[/"min(?end)"/]
v2 --o bind10
bind10 --as--o v13