In a recent post (https://technology.amis.nl/2013/07/24/oracle-database-12c-find-most-valuable-player-using-match_recognize-in-sql/) I described how we can use the new Oracle Database 12c MATCH_RECOGNIZE operator to spot patterns in records and derive results from those patterns. I used the MATCH_RECOGNIZE to find the most valuable player in a football team (US readers: I mean the sports that you may refer to as Soccer). My definition of the MVP is the player who is most frequently part of a period of uninterrupted ball possession ending with a goal. Whether the play scores the goal, presents the assist or is involved earlier on does not matter: if he was part of the play leading up to the goal – we count the contribution. However, the ball possession ends when a player from the other team has possession of the ball.
On closer inspection, this is a type of pattern that we can also find using a Recursive Subquery – albeit not as elegantly and presumably not as well performing.
The starting situation is the same of course as in the previous article. We use two simple tables to record the matches and the match_events:
The action column holds values P (for pass – including throw in, penalty kick and corner kick), C (for causing a corner), O (for playing the ball outside the pitch) and G (for goal). Team is either A or B – A for the home team, and B for the team playing away. Player is the number on the player’s jersey, uniquely identifying the player within the team.
A few matches are created:
And some match events are loaded:
The first piece of SQL we create is a recursive subquery that will go into the match events and first find all GOAL actions. From each of the goals it will recurse its way back to all prior actions in the same match and by players of the same team. All these actions are assists – according to my definition.
with goals (match_id, players, team, seq, root_seq) as ( select me.match_id , '+'||me.player , case me.team when 'A' then m.team_a when 'B' then m.team_b end team , me.seq , me.seq from match_events me join matches m on (m.id = me.match_id) where me.action= 'G' UNION ALL select goals.match_id , goals.players ||'+'||me.player , goals.team , me.seq , goals.root_seq from match_events me join matches m on (m.id = me.match_id) join goals on ( me.seq = goals.seq -1 and me.match_id = goals.match_id and case me.team when 'A' then m.team_a when 'B' then m.team_b end = goals.team ) ) select match_id, root_seq, team, players from goals
The query returns results like this:
I add SQL to find all players then join the players with the goals – making sure that each root_seq (the sequence of the goal itself) – can be counted only once for each player.
with goals (match_id, players, team, seq, root_seq) as ( select me.match_id , '+'||me.player , case me.team when 'A' then m.team_a when 'B' then m.team_b end team , me.seq , me.seq from match_events me join matches m on (m.id = me.match_id) where me.action= 'G' UNION ALL select goals.match_id , goals.players ||'+'||me.player , goals.team , me.seq , goals.root_seq from match_events me join matches m on (m.id = me.match_id) join goals on ( me.seq = goals.seq -1 and me.match_id = goals.match_id and case me.team when 'A' then m.team_a when 'B' then m.team_b end = goals.team ) ) , players as ( select distinct me.match_id , me.player , case me.team when 'A' then m.team_a when 'B' then m.team_b end team from match_events me join matches m on (m.id = me.match_id) ) select p.player , p.team , count(distinct g.root_seq||g.match_id) goals_and_assists from goals g join players p on (g.match_id = p.match_id and p.team = g.team and instr(g.players, '+'||p.player) >0 ) group by p.player , p.team order by goals_and_assists desc
And the results are in: player number 7 from the Dutch team is the MVP based on the limited set of data available for our analysis:
Now unfortunately there seems to be a difference between these results and the once found in the previous article – player number 7 from DE (Germany) is doing better in this query than in the other. I need to look into this.
Resources
Download SQL Scripts with all statements described in this article: mostValuablePlayerRecursive.
Some considerations with regard to measuring ball possession in football: http://leastthing.blogspot.nl/2012/02/how-ball-possession-is-measured-in.html .