query-758c578319162d26e8cb414cc71880db

rq turtle/ttl

Submission from Dipsacus fullonumI made everything in one giant SPARQL query. With 9 named and 12 unnamed subqueries – if I counted correctly – it can probably be made simpler, but it is rather fast (approx 1 s). Note that the code handles these special cases: If one MP starts the term the day after another MP retires, it is considered as only one period as the overall count will not change in the two days. MP retires, it is counted correctly with 1 extra MP for the date of their overlaping periods.anotherIf one MP starts the term the same day as , it is considered as only one period (thus the date of change of status is ignored)(P4100)parliamentary group When the same MP has consecutive periods starting and ending the same date due to e.g. change of

Use at

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
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/>
SELECT ?period ?start_date ?end_date ?days ?MPs
{
  # Shows all periods and aggregated values
  { SELECT ?period ?start_date ?end_date ?days (?members AS ?MPs) { 
{
  # Join dates with themselves using ?date_numbers as keys to get periods
  SELECT ?period ?start_date ?end_date (xsd:integer(?end_date - ?start_date + 1) AS ?days) ?members
  {
    { SELECT (?adjusted_date AS ?start_date) ?members (?date_number AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
    { SELECT (?adjusted_date - "P1D"^^xsd:duration AS ?end_date) (?date_number - 1 AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
  }
}} }
  UNION
  { SELECT ("Minimum" AS ?period) (MIN(?members) AS ?MPs) { 
{
  # Join dates with themselves using ?date_numbers as keys to get periods
  SELECT ?period ?start_date ?end_date (xsd:integer(?end_date - ?start_date + 1) AS ?days) ?members
  {
    { SELECT (?adjusted_date AS ?start_date) ?members (?date_number AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
    { SELECT (?adjusted_date - "P1D"^^xsd:duration AS ?end_date) (?date_number - 1 AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
  }
}} }
  UNION
  { SELECT ("Maximum" AS ?period) (MAX(?members) AS ?MPs) { 
{
  # Join dates with themselves using ?date_numbers as keys to get periods
  SELECT ?period ?start_date ?end_date (xsd:integer(?end_date - ?start_date + 1) AS ?days) ?members
  {
    { SELECT (?adjusted_date AS ?start_date) ?members (?date_number AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
    { SELECT (?adjusted_date - "P1D"^^xsd:duration AS ?end_date) (?date_number - 1 AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
  }
}} }
  UNION
  { SELECT ("Average" AS ?period) (SUM(?members * ?days) / SUM(?days) AS ?MPs) { 
{
  # Join dates with themselves using ?date_numbers as keys to get periods
  SELECT ?period ?start_date ?end_date (xsd:integer(?end_date - ?start_date + 1) AS ?days) ?members
  {
    { SELECT (?adjusted_date AS ?start_date) ?members (?date_number AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
    { SELECT (?adjusted_date - "P1D"^^xsd:duration AS ?end_date) (?date_number - 1 AS ?period) { 
{
  # Join the dates with themselves to number them
  SELECT ?adjusted_date (SUM(?change2) AS ?members) (COUNT(?changes_day_sum) AS ?date_number)
  {
    INCLUDE %summed_day_changes
    { SELECT (?adjusted_date AS ?date2) (?changes_day_sum AS ?change2) { INCLUDE %summed_day_changes } }
    FILTER (?date2 <= ?adjusted_date)
  }
  GROUP BY ?adjusted_date
  ORDER BY ?adjusted_date
}} }
  }
}} }
}
ORDER BY ?period

Query found at