如何在Neo4j足球数据图谱中筛选各队的顶级射手？

Got it, let's sort this out for you! Your current query does a solid job listing every goal-scoring player alongside their count, grouped by team—but to narrow it down to only the top scorer(s) per team, we have two reliable approaches depending on your Neo4j version.

Method 1: Works for All Neo4j Versions (Pre-4.0 Included) #

This approach first calculates the maximum goal count per team, then matches back to the players who hit that number:

// Aggregate goals per player per team
MATCH (e:Event)-[:INVOLVING]->(p:Player)-[:PLAYED_FOR]->(t:Team)
WHERE e.eventType = 'GOAL'
WITH t.teamName AS teamName, p.name AS playerName, COUNT(DISTINCT e) AS goalCount

// Calculate max goals per team and collect all player-goal pairs
WITH teamName, MAX(goalCount) AS maxGoals, COLLECT({player: playerName, goals: goalCount}) AS allTeamPlayers

// Unwind and filter to keep only top scorers
UNWIND allTeamPlayers AS playerData
WHERE playerData.goals = maxGoals
RETURN teamName, playerData.player AS playerName, playerData.goals AS goalCount
ORDER BY teamName, goalCount DESC

How this works: #

1. We start with your original logic to get each player's total goals for their team.
2. We group by team to find the highest goal total (maxGoals) for that squad, and collect every player-goal pair into a list.
3. We unwind that list, filter out anyone who doesn't match the maxGoals value, then return the results. This automatically handles ties—if two players are tied for top scorer on a team, both will show up.

Method 2: Using Window Functions (Neo4j 4.0+) #

If you're running Neo4j 4.0 or newer, window functions make this even cleaner. We'll use RANK() to assign a position to each player within their team, then keep only those ranked #1:

MATCH (e:Event)-[:INVOLVING]->(p:Player)-[:PLAYED_FOR]->(t:Team)
WHERE e.eventType = 'GOAL'
WITH t.teamName AS teamName, p.name AS playerName, COUNT(DISTINCT e) AS goalCount

// Assign ranks based on goal count within each team
WITH teamName, playerName, goalCount,
     RANK() OVER (PARTITION BY teamName ORDER BY goalCount DESC) AS playerRank

// Keep only top-ranked players
WHERE playerRank = 1
RETURN teamName, playerName, goalCount
ORDER BY teamName, goalCount DESC

Notes on this method: #

• RANK() will give the same rank to players with identical goal counts (so ties are preserved). If you want to break ties (e.g., pick alphabetically by player name), add a secondary sort to the window function: ORDER BY goalCount DESC, playerName ASC.
• If you only want one top scorer even if there's a tie, use ROW_NUMBER() instead of RANK()—but this will arbitrarily pick one player if counts are equal.

Quick Tip: #

If each GOAL event is only linked to one scorer (no duplicate links), you can simplify COUNT(DISTINCT e) to COUNT(e) for a small performance boost.

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