Changing odds as soccer score changes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Changing odds as soccer score changes

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2011-03-13 : 21:23:12

I am looking at finding out the chances of a home soccer team winning a game as a match progresses. There will be factors including whether or not the home team is higher ranked on the season to date or if any player has been dismissed, but a major factor will clearly be what the scoreline is at any point in time during the game

Excluding other factors, I can get the initial likelihood (win,loss or tie) from tblResults which has matchID,hometeamID,awayteamID,homegoals,awaygoals columns

select count(matchID) as total,
round(100*sum(case when homeGoals > awayGoals then 1 else 0 end)/cast(count(matchid) as money),2) as win_pc,
round(100*sum(case when homeGoals = awayGoals then 1 else 0 end)/cast(count(matchid) as money),2) as tie_pc,
round(100*sum(case when homeGoals < awayGoals then 1 else 0 end)/cast(count(matchid) as money),2) as loss_pc
from soccer.tblResults

I also have a tblgoals table which includes a time column (ranging from 1 -90 minutes) and a playerMatch column for each goal scored
whch can be joined to the tblResults table via intermediary tables. So for any individual game, I have the data available to tell me the scoreline for each minute and the final result. Collating all such results I can calculate the variation in the above odds at any point in time (although have under 10,000 games so may want to group the goals by 5 minute interval

I'm at a bit of a loss re how to go about this and would appreciate any advice

Andrew Clark
www.premiersoccerstats.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 08:41:23

Well...creating odds on sporting events is an artform, and relies on far more dimensions than the goal statistics. But in order to help you we would need some more specific requirements.

What is the table layout of tblResults and tblGoals and what does your expected output look like? If you provide working sql code with create/insert statements and sample data it will be far easier to help you.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

AndyC
Yak Posting Veteran

53 Posts

Posted - 2011-03-14 : 14:26:20

Probabilities would have been a better choice of word than odds

What I had envisaged as output was something like fangraphs does for baseball

http://www.fangraphs.com/scoreboard.aspx?date=2010-11-01

with time (in minutes) as the x axis and two lines delineating the space for a win,draw, and loss moving as the game progresses and the score varies. So for instance, the home team win line would be around the 45% to start with, decrease over time if no goals are scored and a draw becomes more likely but spike up whenever they scored a goal

I would be using data in an established database which has a goals table which has fields including playerMatchID and timeOfGoal as well as other factors such as distance which would be irrelevant to this problem.The playerMatchID links to both a MatchID and a PlayerID, which in term links to a teamID. The results table has information on homeTeam,awayTeam,homeGoals,awayGoals, and a derived field,result

So for instance I have a row in the results table


MATCHID	HOME	AWAY	HGOALS	AGOALS	RESULT
5	TOT	ASV	3	2	W

The corresponding five goals can be queried thus


SELECT     distinct soccer.tblGoals.TIME as time, soccer.tblMatchTeam.TEAMID, soccer.tblMatchTeam.[HOME/AWAY] as venue, soccer.tblResults.MATCHID, soccer.tblResults.HomeTeamResult
FROM        soccer.tblGoals INNER JOIN
                      soccer.tblPlayer_Match ON soccer.tblGoals.PLAYER_MATCH = soccer.tblPlayer_Match.PLAYER_MATCH INNER JOIN
                      soccer.tblMatchTeam ON soccer.tblPlayer_Match.TEAMMATCHID = soccer.tblMatchTeam.TEAMMATCHID INNER JOIN
                      soccer.tblResults ON soccer.tblMatchTeam.MATCHID = soccer.tblResults.MATCHID CROSS JOIN
                      soccer.tblMatch
WHERE     (soccer.tblResults.MATCHID = 5)


Time   TeamID Venue   MatchID  Result
6	TOT	H	5	W
27	ASV	A	5	W
58	ASV	A	5	W
66	TOT	H	5	W
77	TOT	H	5	W

So here the home side scored first after six minutes, were subsequently down 1-2 after 58 minutes but rallied to win the game

What I want to do is collate all the matches so that I can see what are the chances of the home side, winning losing or tying at any point/scoreline in the game

So for instance if this game were to happen in the future you would see the winlline spike after 6 minutes and drop down markedly by the hour mark before
rising again as the hometeam scores the final two goals reaching 100% at the final whistle

Hope this clarifies

Andrew Clark
www.premiersoccerstats.com

AndyC
Yak Posting Veteran

53 Posts

Posted - 2011-03-16 : 21:26:24

'Hope This Clarifies'

I guess it didn't

One last try. I can create several game records as above detailing when a home or away team scored and the final result
I want to go through them all and establish for every minute of a 90 minute game what are the chances of a win tie or loss for the home team based on the past results
For instance, in the simplest situation after 1 minute of play in actuality 44 of the home teams scored, 33 of them went on to win, 6 tied and 5 lost. The corresponding case where the away team scored has been 23 wins, 8 ties 9 losses. However, I am having trouble getting my head around how to get all 90 minutes scorelines and compare them with the final result (Only one goal can be scored in any specific minute)

cheers

Andrew Clark
www.premiersoccerstats.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-17 : 05:44:21

I'm sorry Andy that I haven't replied sooner but I've been busy and this is a complex problem so it will take some thinking. However, I used to work in the odds-business (I developed a betting exchange back in 2003-2005) so I find your problem compelling :)

I'll see if I can come up with something...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

AndyC
Yak Posting Veteran

53 Posts

Posted - 2011-03-17 : 14:50:13

Thanks Lumbago. That would be great

Andrew Clark
www.premiersoccerstats.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources