query-83d1a80c070a1bfc2b5637de38801db8

rq turtle/ttl

(72) Line chart: death numbers or average death age of CAS/CAE members by yearNo.72 (2022.12.30T0202 created)

Use at

PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX bd: <http://www.bigdata.com/rdf#>
# title of the query
#title: line chart of death numbers or average death age of CAS/CAE members by year
### Note: CAS = Chinese Academy of Sciences; CAE = Chinese Academy of Engineering.
# viewed as line chart (also viewable as table, bar chart, area chart, and bubble chart)
#defaultView:LineChart
### Note: (COUNT(DISTINCT ?death) AS ?death_count) can be replaced with (AVG(?death_age) AS ?AVG_death_age).
SELECT DISTINCT ?yearLabel (COUNT(DISTINCT ?death) AS ?death_count)
WHERE {
  # ?death is instance of (P31) human (Q5)
  ?death wdt:P31 wd:Q5;
         # ?death's place of death (P20)'s country (P17) is People's Republic of China (Q148);
         # ?death's manner of death (P1196) is natural causes (Q3739104)
         ### Note: The purpose of adding this line is to find the possible impact of the COVID-19 pandemic in China.
         wdt:P20/wdt:P17 wd:Q148; wdt:P1196 wd:Q3739104;
         # ?death's date of death (P570) is ?death_date
         wdt:P570 ?death_date;
         # ?death's date of birth (P569) is ?birth_date
         wdt:P569 ?birth_date
  # ?death's award received (P166) is subclass of (P279) [ZeroOrMorePath (*)] CAS Member (Q9384263)
  {?death wdt:P166/wdt:P279* wd:Q9384263}
  # OR ?death's award recived (P166) is subclass of (P279) [ZeroOrMorePath (*)] CAE Member (Q10873733)
  UNION {?death wdt:P166/wdt:P279* wd:Q10873733}
  # bind ?death_date's year as ?year
  BIND(YEAR(?death_date) as ?year)
  # ?death_date's month is December
  ### Note: This line can be removed.
  FILTER(MONTH(?death_date)=12)
  # use IF to calculate the age of death, and bind the age as ?death_age
  BIND(IF(MONTH(?death_date)>MONTH(?birth_date) || (MONTH(?death_date)=MONTH(?birth_date) && DAY(?death_date)>=DAY(?birth_date)),
       YEAR(?death_date)-YEAR(?birth_date), YEAR(?death_date)-YEAR(?birth_date)-1) AS ?death_age)
  # show label in auto language as default, and English when no default label exists
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
# group COUNT by ?yearLabel
GROUP BY ?yearLabel

Query found at

graph TD classDef projected fill:lightgreen; classDef literal fill:orange; classDef iri fill:yellow; v3("?birth_date") v2("?death"):::projected v5("?death_age") v6("?death_count") v1("?death_date") v4("?year") a1((" ")) a2((" ")) a3((" ")) c13(["wd:Q9384263"]):::iri c6(["wd:Q148"]):::iri c16(["bd:serviceParam"]):::iri c8(["wd:Q3739104"]):::iri c3(["wd:Q5"]):::iri c18(["#91;AUTO_LANGUAGE#93;,en"]):::literal c14(["wd:Q10873733"]):::iri f0[["month-from-dateTime(?death_date) = '12^^xsd:integer'"]] f0 --> v1 v2 --"wdt:P31"--> c3 v2 --"wdt:P20"--> a1 a1 --"wdt:P17"--> c6 v2 --"wdt:P1196"--> c8 v2 --"wdt:P570"--> v1 v2 --"wdt:P569"--> v3 subgraph union0[" Union "] subgraph union0l[" "] style union0l fill:#abf,stroke-dasharray: 3 3; v2 --"wdt:P166"--> a3 a3 --"wdt:P279"--> c14 end subgraph union0r[" "] style union0r fill:#abf,stroke-dasharray: 3 3; v2 --"wdt:P166"--> a2 a2 --"wdt:P279"--> c13 end union0r <== or ==> union0l end bind1[/"year-from-dateTime(?death_date)"/] v1 --o bind1 bind1 --as--o v4 bind2[/"if((month-from-dateTime(?death_date) > month-from-dateTime(?birth_date) || month-from-dateTime(?death_date) = month-from-dateTime(?birth_date)day-from-dateTime(?death_date) >= day-from-dateTime(?birth_date)),year-from-dateTime(?death_date) - year-from-dateTime(?birth_date),year-from-dateTime(?death_date) - year-from-dateTime(?birth_date) + '-1^^xsd:integer')"/] v1 --o bind2 v3 --o bind2 bind2 --as--o v5 subgraph s1["http://wikiba.se/ontology#label"] style s1 stroke-width:4px; c16 --"wikibase:language"--> c18 end bind4[/"count(?death)"/] v2 --o bind4 bind4 --as--o v6