query-1a34e7fc284e221afd868260309f92b4

rq turtle/ttl

TODO:

* order by creation rather than QID string: "FILTER(STR(?item) < STR(?item2))"

* include non-en labels for first item (times out)

SELECT DISTINCT ?itemName ?item ?dob1displayStr ?dod1displayStr ?item2 ?dob2displayStr ?dod2displayStr ?dobdiff ?doddiff ?meandiff WHERE { ?item (wdt:P106/wdt:P279) wd:Q73145133. # chess people or any subclass thereof { ?item rdfs:label ?itemName. } UNION { ?item skos:altLabel ?itemName. } FILTER((LANG(?itemName)) = "en") ?item2 (wdt:P106/wdt:P279) wd:Q73145133. { ?item2 rdfs:label ?itemName. } UNION { ?item2 skos:altLabel ?itemName. } FILTER((STR(?item)) < (STR(?item2))) MINUS { ?item wdt:P1889 ?item2. } MINUS { ?item2 wdt:P1889 ?item. } MINUS { ?item wdt:P460 ?item2. } MINUS { ?item2 wdt:P460 ?item. } MINUS { ?item wdt:P3373 ?item2. } MINUS { ?item2 wdt:P3373 ?item. }

OPTIONAL { ?item p:P569/psv:P569 [ wikibase:timeValue ?dob1Val; wikibase:timePrecision ?dob1Prec ] . BIND(STR(?dob1Val) AS ?dob1fullStr) BIND(IF(?dob1Prec <= 10, 3, 0) AS ?dob1insigDayChars) BIND(IF(?dob1Prec <= 9, 3, 0) AS ?dob1insigMonthChars) BIND(IF(?dob1Prec <= 8, 9-?dob1Prec, 0) AS ?dob1insigYearChars) BIND(?dob1insigDayChars + ?dob1insigMonthChars + ?dob1insigYearChars AS ?dob1insigDateChars) BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dob1insigDateChars) AS ?dob1significantStr) BIND(CONCAT(?dob1significantStr, SUBSTR("0000000000000000", 1, ?dob1insigYearChars)) AS ?dob1displayStr) }

OPTIONAL { ?item2 p:P569/psv:P569 [ wikibase:timeValue ?dob2Val; wikibase:timePrecision ?dob2Prec ] . BIND(STR(?dob2Val) AS ?dob2fullStr) BIND(IF(?dob2Prec <= 10, 3, 0) AS ?dob2insigDayChars) BIND(IF(?dob2Prec <= 9, 3, 0) AS ?dob2insigMonthChars) BIND(IF(?dob2Prec <= 8, 9-?dob2Prec, 0) AS ?dob2insigYearChars) BIND(?dob2insigDayChars + ?dob2insigMonthChars + ?dob2insigYearChars AS ?dob2insigDateChars) BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dob2insigDateChars) AS ?dob2significantStr) BIND(CONCAT(?dob2significantStr, SUBSTR("0000000000000000", 1, ?dob2insigYearChars)) AS ?dob2displayStr) }

BIND(IF(?dob1insigDateChars > ?dob2insigDateChars, ?dob1insigDateChars, ?dob2insigDateChars) AS ?dobCommonInsigDateChars) BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dobCommonInsigDateChars) AS ?dob1CommonSignificantStr) BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dobCommonInsigDateChars) AS ?dob2CommonSignificantStr) BIND(STRDT(CONCAT(?dob1CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobCommonInsigDateChars)), xsd:dateTime) AS ?dob1CommonSignificant) BIND(STRDT(CONCAT(?dob2CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobCommonInsigDateChars)), xsd:dateTime) AS ?dob2CommonSignificant) BIND((CEIL(ABS((?dob1CommonSignificant - ?dob2CommonSignificant) / 3.652425))) / 100 AS ?dobdiff)

OPTIONAL { ?item p:P570/psv:P570 [ wikibase:timeValue ?dod1Val; wikibase:timePrecision ?dod1Prec ] . BIND(STR(?dod1Val) AS ?dod1fullStr) BIND(IF(?dod1Prec <= 10, 3, 0) AS ?dod1insigDayChars) BIND(IF(?dod1Prec <= 9, 3, 0) AS ?dod1insigMonthChars) BIND(IF(?dod1Prec <= 8, 9-?dod1Prec, 0) AS ?dod1insigYearChars) BIND(?dod1insigDayChars + ?dod1insigMonthChars + ?dod1insigYearChars AS ?dod1insigDateChars) BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dod1insigDateChars) AS ?dod1significantStr) BIND(CONCAT(?dod1significantStr, SUBSTR("0000000000000000", 1, ?dod1insigYearChars)) AS ?dod1displayStr) }

OPTIONAL { ?item2 p:P570/psv:P570 [ wikibase:timeValue ?dod2Val; wikibase:timePrecision ?dod2Prec ] . BIND(STR(?dod2Val) AS ?dod2fullStr) BIND(IF(?dod2Prec <= 10, 3, 0) AS ?dod2insigDayChars) BIND(IF(?dod2Prec <= 9, 3, 0) AS ?dod2insigMonthChars) BIND(IF(?dod2Prec <= 8, 9-?dod2Prec, 0) AS ?dod2insigYearChars) BIND(?dod2insigDayChars + ?dod2insigMonthChars + ?dod2insigYearChars AS ?dod2insigDateChars) BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dod2insigDateChars) AS ?dod2significantStr) BIND(CONCAT(?dod2significantStr, SUBSTR("0000000000000000", 1, ?dod2insigYearChars)) AS ?dod2displayStr) }

BIND(IF(?dod1insigDateChars > ?dod2insigDateChars, ?dod1insigDateChars, ?dod2insigDateChars) AS ?dodCommonInsigDateChars) BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dodCommonInsigDateChars) AS ?dod1CommonSignificantStr) BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dodCommonInsigDateChars) AS ?dod2CommonSignificantStr) BIND(STRDT(CONCAT(?dod1CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodCommonInsigDateChars)), xsd:dateTime) AS ?dod1CommonSignificant) BIND(STRDT(CONCAT(?dod2CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodCommonInsigDateChars)), xsd:dateTime) AS ?dod2CommonSignificant) BIND((CEIL(ABS((?dod1CommonSignificant - ?dod2CommonSignificant) / 3.652425))) / 100 AS ?doddiff)

BIND(BOUND(?dobdiff) AS ?dobdiffbound) BIND(BOUND(?doddiff) AS ?doddiffbound)

BIND(IF(BOUND(?dobdiff), ?dobdiff, 0) AS ?dobdiffsafe) BIND(IF(BOUND(?doddiff), ?doddiff, 0) AS ?doddiffsafe) BIND((CEIL((?dobdiffsafe + ?doddiffsafe) / (xsd:integer(?dobdiffbound) + xsd:integer(?doddiffbound)) * 100) / 100) AS ?meandiff) } ORDER BY (!(?dobdiffbound) && !(?doddiffbound)) ?meandiff (!(?dobdiffbound)) ?dobdiff (!(?doddiffbound)) ?doddiff ?item LIMIT 500

Use at

• https://query.wikidata.org/sparql

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX psv: <http://www.wikidata.org/prop/statement/value/>
PREFIX p: <http://www.wikidata.org/prop/>
# TODO:
# * order by creation rather than QID string: "FILTER(STR(?item) < STR(?item2))"
# * include non-en labels for first item (times out)
SELECT DISTINCT ?itemName ?item ?dob1displayStr ?dod1displayStr ?item2 ?dob2displayStr ?dod2displayStr ?dobdiff ?doddiff ?meandiff WHERE {
  ?item (wdt:P106/wdt:P279*) wd:Q73145133. # chess people or any subclass thereof
  { ?item rdfs:label ?itemName. } UNION { ?item skos:altLabel ?itemName. }
  FILTER((LANG(?itemName)) = "en")
  ?item2 (wdt:P106/wdt:P279*) wd:Q73145133.
  { ?item2 rdfs:label ?itemName. } UNION { ?item2 skos:altLabel ?itemName. }
  FILTER((STR(?item)) < (STR(?item2)))
  MINUS { ?item wdt:P1889 ?item2. }
  MINUS { ?item2 wdt:P1889 ?item. }
  MINUS { ?item wdt:P460 ?item2. }
  MINUS { ?item2 wdt:P460 ?item. }
  MINUS { ?item wdt:P3373 ?item2. }
  MINUS { ?item2 wdt:P3373 ?item. }

  OPTIONAL {
    ?item p:P569/psv:P569 [
      wikibase:timeValue ?dob1Val;
      wikibase:timePrecision ?dob1Prec ] .
    BIND(STR(?dob1Val) AS ?dob1fullStr)
    BIND(IF(?dob1Prec <= 10, 3, 0) AS ?dob1insigDayChars)
    BIND(IF(?dob1Prec <= 9, 3, 0) AS ?dob1insigMonthChars)
    BIND(IF(?dob1Prec <= 8, 9-?dob1Prec, 0) AS ?dob1insigYearChars)
    BIND(?dob1insigDayChars + ?dob1insigMonthChars + ?dob1insigYearChars AS ?dob1insigDateChars)
    BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dob1insigDateChars) AS ?dob1significantStr)
    BIND(CONCAT(?dob1significantStr, SUBSTR("0000000000000000", 1, ?dob1insigYearChars)) AS ?dob1displayStr)
    }

  OPTIONAL {
    ?item2 p:P569/psv:P569 [
      wikibase:timeValue ?dob2Val;
      wikibase:timePrecision ?dob2Prec ] .
    BIND(STR(?dob2Val) AS ?dob2fullStr)
    BIND(IF(?dob2Prec <= 10, 3, 0) AS ?dob2insigDayChars)
    BIND(IF(?dob2Prec <= 9, 3, 0) AS ?dob2insigMonthChars)
    BIND(IF(?dob2Prec <= 8, 9-?dob2Prec, 0) AS ?dob2insigYearChars)
    BIND(?dob2insigDayChars + ?dob2insigMonthChars + ?dob2insigYearChars AS ?dob2insigDateChars)
    BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dob2insigDateChars) AS ?dob2significantStr)
    BIND(CONCAT(?dob2significantStr, SUBSTR("0000000000000000", 1, ?dob2insigYearChars)) AS ?dob2displayStr)
    }

  BIND(IF(?dob1insigDateChars > ?dob2insigDateChars, ?dob1insigDateChars, ?dob2insigDateChars) AS ?dobCommonInsigDateChars)
  BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dobCommonInsigDateChars) AS ?dob1CommonSignificantStr)
  BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dobCommonInsigDateChars) AS ?dob2CommonSignificantStr)
  BIND(STRDT(CONCAT(?dob1CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobCommonInsigDateChars)), xsd:dateTime) AS ?dob1CommonSignificant)
  BIND(STRDT(CONCAT(?dob2CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobCommonInsigDateChars)), xsd:dateTime) AS ?dob2CommonSignificant)
  BIND((CEIL(ABS((?dob1CommonSignificant - ?dob2CommonSignificant) / 3.652425))) / 100 AS ?dobdiff)


  OPTIONAL {
    ?item p:P570/psv:P570 [
      wikibase:timeValue ?dod1Val;
      wikibase:timePrecision ?dod1Prec ] .
    BIND(STR(?dod1Val) AS ?dod1fullStr)
    BIND(IF(?dod1Prec <= 10, 3, 0) AS ?dod1insigDayChars)
    BIND(IF(?dod1Prec <= 9, 3, 0) AS ?dod1insigMonthChars)
    BIND(IF(?dod1Prec <= 8, 9-?dod1Prec, 0) AS ?dod1insigYearChars)
    BIND(?dod1insigDayChars + ?dod1insigMonthChars + ?dod1insigYearChars AS ?dod1insigDateChars)
    BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dod1insigDateChars) AS ?dod1significantStr)
    BIND(CONCAT(?dod1significantStr, SUBSTR("0000000000000000", 1, ?dod1insigYearChars)) AS ?dod1displayStr)
    }

  OPTIONAL {
    ?item2 p:P570/psv:P570 [
      wikibase:timeValue ?dod2Val;
      wikibase:timePrecision ?dod2Prec ] .
    BIND(STR(?dod2Val) AS ?dod2fullStr)
    BIND(IF(?dod2Prec <= 10, 3, 0) AS ?dod2insigDayChars)
    BIND(IF(?dod2Prec <= 9, 3, 0) AS ?dod2insigMonthChars)
    BIND(IF(?dod2Prec <= 8, 9-?dod2Prec, 0) AS ?dod2insigYearChars)
    BIND(?dod2insigDayChars + ?dod2insigMonthChars + ?dod2insigYearChars AS ?dod2insigDateChars)
    BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dod2insigDateChars) AS ?dod2significantStr)
    BIND(CONCAT(?dod2significantStr, SUBSTR("0000000000000000", 1, ?dod2insigYearChars)) AS ?dod2displayStr)
    }

  BIND(IF(?dod1insigDateChars > ?dod2insigDateChars, ?dod1insigDateChars, ?dod2insigDateChars) AS ?dodCommonInsigDateChars)
  BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dodCommonInsigDateChars) AS ?dod1CommonSignificantStr)
  BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dodCommonInsigDateChars) AS ?dod2CommonSignificantStr)
  BIND(STRDT(CONCAT(?dod1CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodCommonInsigDateChars)), xsd:dateTime) AS ?dod1CommonSignificant)
  BIND(STRDT(CONCAT(?dod2CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodCommonInsigDateChars)), xsd:dateTime) AS ?dod2CommonSignificant)
  BIND((CEIL(ABS((?dod1CommonSignificant - ?dod2CommonSignificant) / 3.652425))) / 100 AS ?doddiff)

  BIND(BOUND(?dobdiff) AS ?dobdiffbound)
  BIND(BOUND(?doddiff) AS ?doddiffbound)

  BIND(IF(BOUND(?dobdiff), ?dobdiff, 0) AS ?dobdiffsafe)
  BIND(IF(BOUND(?doddiff), ?doddiff, 0) AS ?doddiffsafe)
  BIND((CEIL((?dobdiffsafe + ?doddiffsafe) / (xsd:integer(?dobdiffbound) + xsd:integer(?doddiffbound)) * 100) / 100) AS ?meandiff)
}
ORDER BY (!(?dobdiffbound) && !(?doddiffbound)) ?meandiff (!(?dobdiffbound)) ?dobdiff (!(?doddiffbound)) ?doddiff ?item
LIMIT 500

Query found at

• https://www.wikidata.org/wiki/Wikidata:WikiProject_Chess/Maintenance_Queries