Wikidata SPARQL查询如何分组聚合P69属性的多值限定符

当前查询出现同院校条目拆分的核心问题是聚合层级错误：你直接在人物维度做全局GROUP BY，没有先对单条P69（educated at）陈述做子聚合。同一条陈述下每匹配到一个学位（P512）、专业（P812）的多值，就会生成一行独立的教育经历字符串，最终外层GROUP_CONCAT拼接时就会出现重复的院校条目。

通过两层聚合实现需求：

• 内层先以单条P69陈述为分组维度，将同一条陈述下的所有学位、专业值分别聚合拼接
• 外层再以人物为维度，将所有单条教育经历字符串按指定分隔符拼接

修改后的完整查询代码如下：

SELECT ?itemLabel 
  (GROUP_CONCAT(DISTINCT ?altNames; SEPARATOR = ";") AS ?aliases) 
  ?itemDesc 
  ?genderLabel 
  ?birthday 
  ?placeOfBirthLabel 
  ?image 
  (GROUP_CONCAT(DISTINCT ?ed; SEPARATOR = "|") AS ?education) 
WHERE {
  VALUES ?item { wd:Q5402996 }
  
  # 别名处理
  OPTIONAL {
    ?item skos:altLabel ?altNames.
    FILTER(LANG(?altNames) = "en")
  }

  # 基础人物属性
  OPTIONAL { ?item wdt:P21 ?gender. }
  OPTIONAL { ?item wdt:P569 ?birthday. }
  OPTIONAL { ?item wdt:P19 ?placeOfBirth. }
  OPTIONAL { ?item wdt:P18 ?image. }

  # 教育经历子查询：先按单条陈述聚合多值限定符
  OPTIONAL {
    SELECT ?item ?ed WHERE {
      ?item p:P69 ?statement.
      ?statement ps:P69 ?eduInst.
      ?eduInst rdfs:label ?eduLabel FILTER(LANG(?eduLabel) = "en")
      
      OPTIONAL { ?statement pq:P580 ?edStart. }
      OPTIONAL { ?statement pq:P582 ?edEnd. }
      
      # 聚合同陈述下所有学位
      OPTIONAL {
        SELECT ?statement (GROUP_CONCAT(DISTINCT ?degreeLabel; SEPARATOR = ";") AS ?edDegrees) WHERE {
          ?statement pq:P512 ?degree.
          ?degree rdfs:label ?degreeLabel FILTER(LANG(?degreeLabel) = "en")
        } GROUP BY ?statement
      }
      
      # 聚合同陈述下所有专业
      OPTIONAL {
        SELECT ?statement (GROUP_CONCAT(DISTINCT ?majorLabel; SEPARATOR = ";") AS ?edMajors) WHERE {
          ?statement pq:P812 ?major.
          ?major rdfs:label ?majorLabel FILTER(LANG(?majorLabel) = "en")
        } GROUP BY ?statement
      }

      BIND(IF(BOUND(?edStart), CONCAT("::start:", STR(YEAR(?edStart))), "") AS ?edStartText)
      BIND(IF(BOUND(?edEnd), CONCAT("::end:", STR(YEAR(?edEnd))), "") AS ?edEndText)
      BIND(IF(BOUND(?edDegrees), CONCAT("::degrees:", ?edDegrees), "") AS ?edDegreeText)
      BIND(IF(BOUND(?edMajors), CONCAT("::majors:", ?edMajors), "") AS ?edMajorText)
      BIND(CONCAT(?eduLabel, ?edStartText, ?edEndText, ?edDegreeText, ?edMajorText) AS ?ed)
    } GROUP BY ?item ?ed
  }

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en".
    ?item rdfs:label ?itemLabel;
          schema:description ?itemDesc.
    ?gender rdfs:label ?genderLabel.
    ?placeOfBirth rdfs:label ?placeOfBirthLabel.
  }
}
GROUP BY ?itemLabel ?itemDesc ?genderLabel ?birthday ?image ?placeOfBirthLabel

查询返回的教育字段格式和预期完全一致：

Harvard University::end:1980::degrees:Master of Arts;Doctor of Philosophy::majors:astronomy|University of Rochester::end:1976::degrees:Bachelor of Arts;Bachelor of Science

Wikidata底层使用Blazegraph引擎，内置JSON构造函数，可直接生成结构化嵌套结果，不需要自定义分隔符、后续做字符串拆分，稳定性更高：

• 用json:array将同维度多值包裹为数组
• 用json:object构造单条教育经历的结构化对象
• 分层聚合后直接输出可直接反序列化的JSON字段

核心实现代码如下：

SELECT ?itemLabel ?aliases ?itemDesc ?genderLabel ?birthday ?placeOfBirthLabel ?image ?educationJson WHERE {
  VALUES ?item { wd:Q5402996 }
  
  # 别名数组
  OPTIONAL {
    SELECT ?item (json:array(DISTINCT ?altNames) AS ?aliases) WHERE {
      ?item skos:altLabel ?altNames FILTER(LANG(?altNames) = "en")
    } GROUP BY ?item
  }

  # 基础属性
  OPTIONAL { ?item wdt:P21 ?gender. ?gender rdfs:label ?genderLabel FILTER(LANG(?genderLabel)="en") }
  OPTIONAL { ?item wdt:P569 ?birthday. }
  OPTIONAL { ?item wdt:P19 ?pob. ?pob rdfs:label ?placeOfBirthLabel FILTER(LANG(?placeOfBirthLabel)="en") }
  OPTIONAL { ?item wdt:P18 ?image. }
  OPTIONAL { ?item rdfs:label ?itemLabel FILTER(LANG(?itemLabel)="en") }
  OPTIONAL { ?item schema:description ?itemDesc FILTER(LANG(?itemDesc)="en") }

  # 构造嵌套教育经历JSON
  OPTIONAL {
    SELECT ?item (json:array(DISTINCT ?edEntry) AS ?educationJson) WHERE {
      ?item p:P69 ?stmt.
      ?stmt ps:P69 ?inst. ?inst rdfs:label ?instName FILTER(LANG(?instName)="en")
      OPTIONAL { ?stmt pq:P580 ?start. BIND(YEAR(?start) AS ?startYear) }
      OPTIONAL { ?stmt pq:P582 ?end. BIND(YEAR(?end) AS ?endYear) }
      # 学位数组
      OPTIONAL {
        SELECT ?stmt (json:array(DISTINCT ?dName) AS ?degrees) WHERE {
          ?stmt pq:P512 ?d. ?d rdfs:label ?dName FILTER(LANG(?dName)="en")
        } GROUP BY ?stmt
      }
      # 专业数组
      OPTIONAL {
        SELECT ?stmt (json:array(DISTINCT ?mName) AS ?majors) WHERE {
          ?stmt pq:P812 ?m. ?m rdfs:label ?mName FILTER(LANG(?mName)="en")
        } GROUP BY ?stmt
      }
      # 构造单条教育经历对象
      BIND(json:object(
        "institution", ?instName,
        "startYear", ?startYear,
        "endYear", ?endYear,
        "degrees", ?degrees,
        "majors", ?majors
      ) AS ?edEntry)
    } GROUP BY ?item
  }
}

返回的?educationJson是标准JSON数组，每个元素对应一条教育经历，学位、专业字段直接为数组结构，业务代码中直接反序列化即可使用，不需要做字符串分割处理。

内容的提问来源于stack exchange，提问作者Korimako