如何编写Wikidata SPARQL查询获取按营收排名的美国前十企业及其董事？现有查询聚合问题优化需求

Let's work through the aggregation and timeout issues you're facing, plus add the director Wikidata IDs you want. The main problem with your original query is that it tries to process directors for all US companies first before filtering and sorting—this creates huge intermediate datasets that cause timeouts when adding more grouping variables. Instead, we'll first narrow down to the top 10 companies by 2020 revenue, then fetch their director data separately to keep things efficient.

Here's the optimized query:

# Step 1: Get the top 10 US companies by 2020 revenue
WITH {
  SELECT ?item ?itemLabel ?rev ?date
  WHERE {
    ?item wdt:P31/wdt:P279* wd:Q4830453 ; # Instance of or subclass of business enterprise
          wdt:P17 wd:Q30 . # Country: United States
    ?item p:P2139 ?revSt .
    ?revSt pq:P585 ?date.
    FILTER (YEAR(?date) = 2020)
    ?revSt ps:P2139 ?rev .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  ORDER BY DESC(?rev)
  LIMIT 10
} AS %top_companies

# Step 2: Fetch directors (and their IDs) for these top companies
SELECT ?item ?itemLabel ?rev ?date 
       (GROUP_CONCAT(DISTINCT ?director; separator = ", ") AS ?director_wikidata_ids)
       (GROUP_CONCAT(DISTINCT ?directorLabel; separator = ", ") AS ?directors)
WHERE {
  INCLUDE %top_companies
  ?item p:P3320 ?relationship.
  ?relationship ps:P3320 ?director.
  FILTER NOT EXISTS {?relationship pq:P582 ?end.} # Only active directorships (no end date)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel ?rev ?date
ORDER BY DESC(?rev)

Key Adjustments: #

• Pre-filter with a WITH clause: By first isolating the top 10 companies, we cut down the dataset we need to process for director data from thousands of entities to just 10—this eliminates the timeout issue when adding ?rev and ?date to the GROUP BY.
• Dual aggregations: We now collect both the raw Wikidata IDs for directors (?director) and their human-readable labels in separate grouped fields, so you get exactly the data you need.
• Cleaner deduplication: The DISTINCT flag in GROUP_CONCAT ensures we don't get duplicate entries for directors who might be linked multiple times in Wikidata.

Optional Tweak: #

If you want to ensure directors are only human beings (excluding any organizational directors), add ?director wdt:P31 wd:Q5 to the second WHERE clause right after ?relationship ps:P3320 ?director..

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