考虑到就欧洲足球而言,我们仍处于赛季前 转会狂潮 ,我认为整理足球转会图表以查看是否有任何有趣的见解会很有趣。
我花了一段时间才找到合适的消息来源,但最终我遇到了transfermarkt.co.uk ,其中包含的转移至少可以追溯到1992年英超联赛的开始。
我编写了一个快速的Python脚本来创建所有传输的CSV文件。 该文件如下所示:
$ head -n 10 data/transfers.csv
player,from_team,from_team_id,to_team,to_team_id,fee,season
Martin Keown,Everton,29,Arsenal FC,11,"2,10 Mill. £",1992-1993
John Jensen,Bröndby IF,206,Arsenal FC,11,"1,12 Mill. £",1992-1993
Alan Miller,Birmingham,337,Arsenal FC,11,,1992-1993
Jim Will,Sheffield Utd.,350,Arsenal FC,11,,1992-1993
David Rocastle,Arsenal FC,11,Leeds,399,"1,68 Mill. £",1992-1993
Perry Groves,Arsenal FC,11,Southampton FC,180,595 Th. £,1992-1993
Ty Gooden,Arsenal FC,11,Wycombe Wand.,2805,?,1992-1993
Geraint Williams,Derby,22,Ipswich Town,677,525 Th. £,1992-1993
Jason Winters,Chelsea U21,9250,Ipswich Town,677,?,1992-1993
我将创建以下图形,然后我们将编写一些查询来探索涉及球员和俱乐部的转会链。
我使用Neo4j的LOAD CSV命令编写了一些导入脚本,首先设置了适当的索引:
create index on :Team(id);
create index on :Season(name);
create index on :Transfer(description);
create index on :Player(name);
// teams
load csv with headers from "file:///Users/markneedham/projects/football-transfers/data/teams.csv" as row
merge (team:Team {id: toint(row.team_id)})
on create set team.name = row.team;
// seasons
load csv with headers from "file:///Users/markneedham/projects/football-transfers/data/transfers.csv" as row
merge (season:Season {name: row.season})
ON CREATE SET season.starts = toint(split(season.name, "-")[0]);
// players
load csv with headers from "file:///Users/markneedham/projects/football-transfers/data/transfers.csv" as row
merge (player:Player {name: row.player});
// transfers
load csv with headers from "file:///Users/markneedham/projects/football-transfers/data/transfers.csv" as row
match (from:Team {id: toint(row.from_team_id)})
match (to:Team {id: toint(row.to_team_id)})
match (season:Season {name: row.season})
match (player:Player {name: row.player})
merge (transfer:Transfer {description: row.player + " from " + from.name + " to " + to.name})
merge (transfer)-[:FROM_TEAM]->(from)
merge (transfer)-[:TO_TEAM]->(to)
merge (transfer)-[:IN_SEASON]->(season)
merge (transfer)-[:PLAYER]->(player);
// connect transfers
match (season)<-[:IN_SEASON]-(transfer:Transfer)-[:PLAYER]->(player)
WITH player, season, transfer
ORDER BY player.name, season.starts
WITH player, COLLECT({s: season, t: transfer}) AS transfers
UNWIND range(0, length(transfers)-2) AS idx
WITH player, transfers[idx] AS t1, transfers[idx +1] AS t2
WITH player, t1.t AS t1, t2.t AS t2
MERGE (t1)-[:NEXT]->(t2);
如果您想使用数据,那么所有文件和脚本都在此要旨上。 您唯一需要更改的是每条“ LOAD CSV”行上的文件路径。
“连接转账”查询比其他查询要复杂一些,因为我们首先按照球员分组的升序对转账进行排序,然后创建一个球员转账的链表 。
现在我们已经加载了数据,让我们找出哪个球员被转移的次数最多:
match path = (:Transfer)-[:NEXT*0..]->(transfer:Transfer)
where NOT (transfer)-[:NEXT]->()
RETURN path
ORDER BY LENGTH(path) DESC
LIMIT 1
哪些其他球员经常移动球队?
match path = (first:Transfer)-[:NEXT*0..]->(transfer:Transfer),
(player)<-[:PLAYER]-(transfer)
where NOT ((transfer)-[:NEXT]->()) AND NOT ((first)<-[:NEXT]-())
RETURN player.name, LENGTH(path) AS numberOfTransfers
ORDER BY numberOfTransfers DESC
LIMIT 10
==> +--------------------------------------+
==> | player.name | numberOfTransfers |
==> +--------------------------------------+
==> | "Craig Bellamy" | 7 |
==> | "David Unsworth" | 6 |
==> | "Andrew Cole" | 6 |
==> | "Peter Crouch" | 6 |
==> | "Les Ferdinand" | 5 |
==> | "Kevin Phillips" | 5 |
==> | "Mark Hughes" | 5 |
==> | "Tommy Wright" | 4 |
==> | "Carl Tiler" | 4 |
==> | "Don Hutchison" | 4 |
==> +--------------------------------------+
==> 10 rows
转会中最常见的俱乐部组合是什么?
match (from)<-[:FROM_TEAM]-(t:Transfer)-[:TO_TEAM]->(to), (t)-[:PLAYER]->(p)
RETURN from.name, to.name, COUNT(*) AS times, COLLECT(p.name) AS players
ORDER BY times DESC
LIMIT 10
==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | from.name | to.name | times | players |
==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | "West Ham United" | "Queens Park Rangers" | 13 | ["Keith Rowland","Iain Dowie","Tim Breacker","Ludek Miklosko","Bertie Brayley","Terrell Forbes","Steve Lomas","Hogan Ephraim","Nigel Quashie","Danny Gabbidon","Kieron Dyer","Robert Green","Gary O'Neil"] |
==> | "Tottenham Hotspur" | "Portsmouth FC" | 12 | ["Paul Walsh","Andy Turner","Rory Allen","Justin Edinburgh","Tim Sherwood","Teddy Sheringham","Noé Pamarot","Pedro Mendes","Sean Davis","Jermain Defoe","Younès Kaboul","Kevin-Prince Boateng"] |
==> | "Liverpool FC" | "West Ham United" | 12 | ["Julian Dicks","David Burrows","Mike Marsh","Don Hutchison","Neil Ruddock","Titi Camara","Rob Jones","Rigobert Song","Craig Bellamy","Joe Cole","Andy Carroll","Stewart Downing"] |
==> | "Manchester United" | "Everton FC" | 9 | ["Andrey Kanchelskis","John O'Kane","Jesper Blomqvist","Phil Neville","Tim Howard","Louis Saha","Darron Gibson","Sam Byrne","Tom Cleverley"] |
==> | "Newcastle United" | "West Ham United" | 9 | ["Paul Kitson","Shaka Hislop","Stuart Pearce","Wayne Quinn","Lee Bowyer","Kieron Dyer","Scott Parker","Nolberto Solano","Kevin Nolan"] |
==> | "Blackburn Rovers" | "Leicester City" | 9 | ["Steve Agnew","Tim Flowers","Callum Davidson","John Curtis","Keith Gillespie","Craig Hignett","Nils-Eric Johansson","Bruno Berner","Paul Gallagher"] |
==> | "Chelsea FC" | "Southampton FC" | 8 | ["Ken Monkou","Kerry Dixon","Neil Shipperley","Mark Hughes","Paul Hughes","Graeme Le Saux","Jack Cork","Ryan Bertrand"] |
==> | "Birmingham City" | "Coventry City" | 8 | ["David Rennie","John Gayle","Liam Daish","Gary Breen","Stern John","Julian Gray","Lee Carsley","Gary McSheffrey"] |
==> | "Southampton FC" | "Fulham FC" | 8 | ["Micky Adams","Kevin Moore","Terry Hurlock","Maik Taylor","Alan Neilson","Luís Boa Morte","Antti Niemi","Chris Baird"] |
==> | "Portsmouth FC" | "Stoke City" | 8 | ["Kevin Harper","Lewis Buxton","Anthony Pulis","Vincent Péricard","Asmir Begovic","Marc Wilson","Elliot Wheeler","Alex Grant"] |
==> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> 10 rows
曾经有过玩家双向转移的情况吗?
match (from)<-[:FROM_TEAM]-(t:Transfer)-[:TO_TEAM]->(to), (t)-[:PLAYER]->(player)
where id(from) < id(to)
WITH from, to, COUNT(*) AS times, COLLECT(player.name) AS players
match (to)<-[:FROM_TEAM]-(t:Transfer)-[:TO_TEAM]->(from), (t)-[:PLAYER]->(player)
RETURN from.name, to.name, times, COUNT(*) as otherWayTimes, players, COLLECT(player.name) AS otherWayPlayers
ORDER BY times + otherWayTimes DESC
LIMIT 10
==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | from.name | to.name | times | otherWayTimes | players | otherWayPlayers |
==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | "Tottenham Hotspur" | "Portsmouth FC" | 12 | 5 | ["Paul Walsh","Andy Turner","Rory Allen","Justin Edinburgh","Tim Sherwood","Teddy Sheringham","Noé Pamarot","Pedro Mendes","Sean Davis","Jermain Defoe","Younès Kaboul","Kevin-Prince Boateng"] | ["Jermain Defoe","Niko Kranjcar","Younès Kaboul","Peter Crouch","Darren Anderton"] |
==> | "West Ham United" | "Liverpool FC" | 4 | 12 | ["Julian Dicks","Daniel Sjölund","Yossi Benayoun","Javier Mascherano"] | ["Stewart Downing","Andy Carroll","Joe Cole","Craig Bellamy","Rigobert Song","Titi Camara","Rob Jones","Neil Ruddock","Don Hutchison","Julian Dicks","Mike Marsh","David Burrows"] |
==> | "West Ham United" | "Queens Park Rangers" | 13 | 2 | ["Keith Rowland","Iain Dowie","Tim Breacker","Ludek Miklosko","Bertie Brayley","Terrell Forbes","Steve Lomas","Hogan Ephraim","Nigel Quashie","Danny Gabbidon","Kieron Dyer","Robert Green","Gary O'Neil"] | ["Andy Impey","Trevor Sinclair"] |
==> | "West Ham United" | "Tottenham Hotspur" | 5 | 8 | ["Jermain Defoe","Frédéric Kanouté","Michael Carrick","Jimmy Walker","Scott Parker"] | ["Sergiy Rebrov","Mauricio Taricco","Calum Davenport","Les Ferdinand","Matthew Etherington","Bobby Zamora","Ilie Dumitrescu","Mark Robson"] |
==> | "West Ham United" | "Portsmouth FC" | 8 | 5 | ["Martin Allen","Adrian Whitbread","Marc Keller","Svetoslav Todorov","Hayden Foxe","Shaka Hislop","Sébastien Schemmel","Hayden Mullins"] | ["Stephen Henderson","Teddy Sheringham","Shaka Hislop","Marc Keller","Lee Chapman"] |
==> | "Newcastle United" | "West Ham United" | 9 | 3 | ["Paul Kitson","Shaka Hislop","Stuart Pearce","Wayne Quinn","Lee Bowyer","Kieron Dyer","Scott Parker","Nolberto Solano","Kevin Nolan"] | ["Demba Ba","Lee Bowyer","David Terrier"] |
==> | "Birmingham City" | "Coventry City" | 8 | 4 | ["David Rennie","John Gayle","Liam Daish","Gary Breen","Stern John","Julian Gray","Lee Carsley","Gary McSheffrey"] | ["Scott Dann","David Burrows","Peter Ndlovu","David Smith"] |
==> | "Manchester City" | "Portsmouth FC" | 8 | 4 | ["Paul Walsh","Carl Griffiths","Fitzroy Simpson","Eyal Berkovic","David James","Andrew Cole","Sylvain Distin","Tal Ben Haim"] | ["Benjani","Gerry Creaney","Kit Symons","Paul Walsh"] |
==> | "Blackburn Rovers" | "Southampton FC" | 5 | 6 | ["David Speedie","Stuart Ripley","James Beattie","Kevin Davies","Zak Jones"] | ["Zak Jones","Egil Östenstad","Kevin Davies","Alan Shearer","Jeff Kenna","Tim Flowers"] |
==> | "AFC Bournemouth" | "West Ham United" | 3 | 8 | ["Keith Rowland","Paul Mitchell","Scott Mean"] | ["Steve Jones","Matt Holland","Mohammed Berthé","Scott Mean","Paul Mitchell","Jamie Victory","Mark Watson","Stephen Purches"] |
==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
是否有任何球员回到过往的同一个俱乐部?
match (player:Player)<-[:PLAYER]-(t1:Transfer)-[:FROM_TEAM]->(from)<-[:TO_TEAM]-(t2:Transfer)-[:FROM_TEAM]->(to),
(t2)-[:PLAYER]->(player), (t1)-[:TO_TEAM]->(to)
WHERE ID(to) < ID(from)
WITH player, COLLECT([ from.name, " ⥄ ", to.name]) AS teams
RETURN player.name,
REDUCE(acc = [], item in teams | acc + REDUCE(acc2 = "", i in item | acc2 + i)) AS thereAndBack
ORDER BY LENGTH(thereAndBack) DESC
LIMIT 10
==> +-------------------------------------------------------------------------------------+
==> | player.name | thereAndBack |
==> +-------------------------------------------------------------------------------------+
==> | "Mark Stein" | ["Stoke City ⥄ Chelsea FC","Ipswich Town ⥄ Chelsea FC"] |
==> | "Peter Beagrie" | ["Bradford City ⥄ Everton FC","Bradford City ⥄ Wigan Athletic"] |
==> | "Richard Dryden" | ["Southampton FC ⥄ Stoke City","Southampton FC ⥄ Swindon Town"] |
==> | "Robbie Elliott" | ["Bolton Wanderers ⥄ Newcastle United"] |
==> | "Elliot Grandin" | ["Blackpool FC ⥄ Crystal Palace"] |
==> | "Robert Fleck" | ["Chelsea FC ⥄ Norwich City"] |
==> | "Paul Walsh" | ["Portsmouth FC ⥄ Manchester City"] |
==> | "Rick Holden" | ["Manchester City ⥄ Oldham Athletic"] |
==> | "Gary McAllister" | ["Liverpool FC ⥄ Coventry City"] |
==> | "Lee Bowyer" | ["West Ham United ⥄ Newcastle United"] |
==> +-------------------------------------------------------------------------------------+
这就是我目前所拥有的一切–如果您能想到其他有趣的探索途径,请告诉我,我会看看。
翻译自: https://www.javacodegeeks.com/2015/07/neo4j-the-football-transfers-graph.html