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.

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.

meta

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 206 other subscribers

Email Address

About

AMIS is internationally recognized for its deep technological insight in Oracle technology. This knowledge is reflected in the presentations we deliver at international conferences such as Oracle OpenWorld, Hotsos and many user conferences around the world. Our AMIS Technology Blog, the most referred Oracle technology knowledge base outside the oracle.com domain. However you arrived here, we appreciate your interest in AMIS. Link to our Google+ Profile AMIS