Étape 1 : Pays
SELECT ?country ?countryLabel WHERE { ?country wdt:P31 wd:Q6256 . # countries SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } }
Étape 2 : Personnes mortes en 2015 et leur nationalité
SELECT ?person ?dod ?country WHERE { ?person wdt:P31 wd:Q5 . # humans ?person wdt:P570 ?dod . # of which we get the date(s) of death FILTER ( ( ?dod < "2016-00-00T00:00:00Z"^^xsd:dateTime ) && ( ?dod >= "2015-00-00T00:00:00Z"^^xsd:dateTime ) ) . # and remove dates that aren't in 2015 ?person wdt:P27 ?country . # and country of citizenship ?country wdt:P31 wd:Q6256 . # making sure we're using a country as citizenship }
Étape 3 : Nombre de personnes mortes par pays en 2015, et population du pays
SELECT ?country ( COUNT( ?person ) AS ?COUNT ) ( SAMPLE ( ?population ) AS ?population ) # SAMPLE picks at random one of the possible values for a variable that isn't GROUPed WHERE { ?country wdt:P31 wd:Q6256 . # countries ?country wdt:P1082 ?population . # of which we get the population ?person wdt:P27 ?country . # and the citizens ?person wdt:P570 ?dod . # of which we get the date(s) of death FILTER ( ( ?dod < "2016-00-00T00:00:00Z"^^xsd:dateTime ) && ( ?dod >= "2015-00-00T00:00:00Z"^^xsd:dateTime ) ) . # and remove dates that aren't in 2015 } GROUP BY ?country
Étape 4 : Nombre de personnes mortes par pays l’année passée, et population du pays
SELECT ?country ( COUNT( ?person ) AS ?COUNT ) ( SAMPLE ( ?population ) AS ?population ) # SAMPLE picks at random one of the possible values for a variable that isn't GROUPed WHERE { ?country wdt:P31 wd:Q6256 . # countries ?country wdt:P1082 ?population . # of which we get the population ?person wdt:P27 ?country . # and the citizens ?person wdt:P570 ?dod . # of which we get the date(s) of death FILTER ( ( ?dod < BIND( xsd:dateTime( CONCAT( xsd:string( YEAR( NOW() ) ),"-00-00T00:00:00Z"^^xsd:string ) ) ) && ( ?dod >= BIND(xsd:dateTime(CONCAT(xsd:string(YEAR(NOW()) - 1),"-00-00T00:00:00Z"^^xsd:string ) ) ) ) . # and remove dates that aren't in the previous year } GROUP BY ?country
Étape 5 : Nombre de personnes mortes par pays l’année passée, population du pays, et nom du pays en anglais ou français si disponible, sans utiliser le SERVICE wikibase:label
SELECT ?country ?countryLabel ( COUNT( ?person ) AS ?COUNT ) ( SAMPLE ( ?population ) AS ?population ) # SAMPLE picks at random one of the possible values for a variable that isn't GROUPed WHERE { ?country wdt:P31 wd:Q6256 . # countries ?country wdt:P1082 ?population . # of which we get the population ?person wdt:P27 ?country . # and the citizens ?person wdt:P570 ?dod . # of which we get the date(s) of death FILTER ( ( ?dod < xsd:dateTime( CONCAT( xsd:string( YEAR( NOW() ) ),"-00-00T00:00:00Z"^^xsd:string ) ) ) && ( ?dod >= xsd:dateTime(CONCAT(xsd:string(YEAR(NOW()) - 1),"-00-00T00:00:00Z"^^xsd:string ) ) ) ) . # and remove dates that aren't in the previous year # the following three lines implement a label language fallback, # and are equivalent to SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }, # except probably faster. OPTIONAL { ?country rdfs:label ?countryLabelEn . FILTER( LANG( ?countryLabelEn ) = "en" ) . } # and try to get the English label OPTIONAL { ?country rdfs:label ?countryLabelFr . FILTER( LANG( ?countryLabelFr ) = "fr" ) . } # and the French label BIND( COALESCE( ?countryLabelEn, ?countryLabelFr ) AS ?countryLabel ) . # and assign the English label if available, or the French label if available } GROUP BY ?country ?countryLabel
Étape 6 : Nombre de personnes mortes par nationalité l’année passée, et proportion sur la population du pays, par taux de décès décroissant
SELECT ?country ?countryLabel ?proportion WHERE { ?country wdt:P31 wd:Q6256 . # countries { SELECT ?country ?countryLabel ( COUNT ( ?person ) AS ?COUNT ) ( SAMPLE( ?population ) AS ?population ) WHERE { ?person wdt:P27 ?country . # person who are citizens of the current country ?person wdt:P570 ?dod . # and who have a date of death ?country wdt:P1082 ?population . # population of the country FILTER ( ( ?dod < xsd:dateTime( CONCAT( xsd:string( YEAR( NOW() ) ),"-00-00T00:00:00Z"^^xsd:string ) ) ) && ( ?dod >= xsd:dateTime(CONCAT(xsd:string(YEAR(NOW()) - 1),"-00-00T00:00:00Z"^^xsd:string ) ) ) ) . # and remove dates that aren't in the previous year # the following three lines implement a label language fallback, # and are equivalent to SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }, # except probably faster. OPTIONAL { ?country rdfs:label ?countryLabelEn . FILTER( LANG( ?countryLabelEn ) = "en" ) . } # and try to get the English label OPTIONAL { ?country rdfs:label ?countryLabelFr . FILTER( LANG( ?countryLabelFr ) = "fr" ) . } # and the French label BIND( COALESCE( ?countryLabelEn, ?countryLabelFr ) AS ?countryLabel ) . # and assign the English label if available, or the French label if available } GROUP BY ?country ?countryLabel ?population } # getting an aggregate number of dead people per country BIND( ( ?COUNT / ?population ) AS ?proportion ) # ratio of dead people (in Wikidata) to population of the current country } ORDER BY DESC( ?proportion )
Résultat final :
Où il apparaît que l’Éthiopie (taux de décès : 0.0000000103136662307, soit 1 pour 100 millions ou 0.001 pour 10 000 environ) est plus sûre que le Liechtenstein (taux de décès : 0.00005416384563303995 soit 5.4 pour 100 000 environ). Ou peut-être que les Éthiopiens sont beaucoup moins représentés et/ou documentés sur Wikidata. Allez savoir.
Petit ajout
Après en avoir discuté avec WikidataFacts, je me suis rendu compte que la requête pouvait être amplement accélérée, en extrayant l’assignation des variables de population et de label de la sous-requête. Le résultat est dix fois plus rapide environ. Ce gain de temps tient au fait que la métarequête n’est effectuée qu’une fois par pays, au lieu d’une fois par décès, du coup, oui, forcément, c’est plus rapide.
SELECT ?country ?countryLabel ?proportion WHERE { ?country wdt:P31 wd:Q6256 . # countries ?country wdt:P1082 ?population . # population of each country # the following three lines implement a label language fallback, # and are equivalent to SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }, # except probably faster. OPTIONAL { ?country rdfs:label ?countryLabelEn . FILTER( LANG( ?countryLabelEn ) = "en" ) . } # and try to get the English label OPTIONAL { ?country rdfs:label ?countryLabelFr . FILTER( LANG( ?countryLabelFr ) = "fr" ) . } # and the French label BIND( COALESCE( ?countryLabelEn, ?countryLabelFr ) AS ?countryLabel ) . # and assign the English label if available, or the French label if available { SELECT ?country ( COUNT ( ?person ) AS ?COUNT ) ( SAMPLE( ?population ) AS ?population ) WHERE { ?person wdt:P27 ?country . # person who are citizens of the current country ?person wdt:P570 ?dod . # and who have a date of death FILTER ( ( ?dod < xsd:dateTime( CONCAT( xsd:string( YEAR( NOW() ) ),"-00-00T00:00:00Z"^^xsd:string ) ) ) && ( ?dod >= xsd:dateTime(CONCAT(xsd:string(YEAR(NOW()) - 1),"-00-00T00:00:00Z"^^xsd:string ) ) ) ) . # and remove dates that aren't in the previous year } GROUP BY ?country } # getting an aggregate number of dead people per country BIND( ( ?COUNT / ?population ) AS ?proportion ) # ratio of dead people (in Wikidata) to population of the current country } ORDER BY DESC( ?proportion )
Leave a Reply