Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP

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 .