SQL and Markov Chains

I’ve just completed a project that required the analysing probabilities of outcomes in the sport of tennis. An algorithm created the likely outcome of the event. A further requirement of the project was to calculate likely outcomes of the event , from different points and scores during the event. For example, if player A was 1 set up what was the probability of them winning the match.

I analysed the data within SQL Server 2000 and the algorithms were created using T-SQL. There was a possibility to use Java 1.4, but the customer requested (for different commercial reasons) for the analysis to occur in SQL. Initially , I investigated the possibility of a script library that could be imported. I could push the data through and get an immediate result. No luck. I decided to simulate the Markov Chain modelling through T-SQL.

A Markov chain model simulates changes in a finite number of states at regular time intervals. For my purposes , it was effective because I was dealing with a large set of objects which required local manipulation . For example, modify an object slightly to get a new one, also as a way to discover patterns within the different result sets. In other words, you can compute the probability of the match being in any given state.

The following code , between START CODE and END CODE, should be copied and pasted into Query Analyzer . A result set will appear that will allow you to view , how you can programmatically start off with an initial figure and go through every possibility.

The data in “#tempNum” represents all the possibilities within my model.

-------------------------START CODE----------------------------------------

--the code is an abstraction for educational purposes .

--It is an example of simulating Markov Chain modelling with SQL Server 2000

--the key Markov Chain elements are represented with lower case SQL

--particuarly between the comments --start loop and --end loop

CREATE TABLE #tempNum (

[id] [int] NULL ,

[a] [int] NULL ,

[b] [int] NULL

) ON [PRIMARY]

INSERT INTO #tempNum ([id],[a],[b])VALUES(1,4,0)

INSERT INTO #tempNum ([id],[a],[b])VALUES(2,4,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(3,4,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(4,4,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(5,4,4)

INSERT INTO #tempNum ([id],[a],[b])VALUES(6,3,4)

INSERT INTO #tempNum ([id],[a],[b])VALUES(7,3,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(8,3,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(9,3,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(10,3,0)

INSERT INTO #tempNum ([id],[a],[b])VALUES(11,2,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(12,2,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(13,2,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(14,2,0)

DECLARE @playerBReturn DECIMAL(10,4)

DECLARE @playerAserver DECIMAL(10,4)

SET @playerAserver = .50

SET @playerBReturn = 1-@playerAserver

begin

set nocount on

DECLARE @initCalc DECIMAL(10,8)

SET @initCalc = .33

create table #tmp (recno int identity, a int,b int,pv DECIMAL(10,4))

insert into #tmp (a,b,pv)

select a,b,1 from #tempNum order by id ASC

declare @t int,@c int

DECLARE @tmpPV DECIMAL(10,8),@tmpPVUpdate DECIMAL(10,8)

DECLARE @tmpAplus INT, @tmpBplus INT --+1

DECLARE @tmpA INT, @tmpB INT --+1

set @t = (select count(*) from #tmp)

set @c = 1

--start loop

while @c <= @t

begin

IF @c < 7

BEGIN

update #tmp

set pv = 1.00

where recno=@c

END

IF @c = 7

BEGIN

SET @tmpPV = @initCalc

END

ELSE IF @c > 7

BEGIN

SET @tmpPV = (select pv from #tmp x where x.recno=@c-1)

END

IF @c = 7

BEGIN

update #tmp

set pv = @initCalc

where recno=@c

END

ELSE IF @c > 7

BEGIN

--deal with 0

IF ((SELECT b FROM #tmp x WHERE x.recno=@c) IN (3)) SET @tmpPV =0

SET @tmpA = (SELECT a FROM #tmp x WHERE x.recno=@c)

SET @tmpB = (SELECT b FROM #tmp x WHERE x.recno=@c)

SET @tmpAplus = ((SELECT a FROM #tmp x WHERE x.recno=@c) + 1)

SET @tmpBplus = ((SELECT b FROM #tmp x WHERE x.recno=@c) + 1)

IF @tmpAplus = 3 AND @tmpBplus = 4 SET @tmpBplus=3

IF @tmpAplus = 2 AND @tmpBplus = 4 SET @tmpBplus=3

IF @tmpAplus = 1 AND @tmpBplus = 4 SET @tmpBplus=3

IF @tmpA = 2 AND @tmpB = 2 SET @tmpBplus = 2

IF @tmpA = 2 AND @tmpB = 1 SET @tmpBplus = 1

IF @tmpA = 2 AND @tmpB = 0 SET @tmpBplus = 0

IF @tmpA = 1 AND @tmpB = 2 SET @tmpBplus = 2

IF @tmpA = 1 AND @tmpB = 1 SET @tmpBplus = 1

IF @tmpA = 1 AND @tmpB = 0 SET @tmpBplus = 0

IF @tmpA = 0 AND @tmpB = 2 SET @tmpBplus = 2

IF @tmpA = 0 AND @tmpB = 1 SET @tmpBplus = 1

IF @tmpA = 0 AND @tmpB = 0 SET @tmpBplus = 0

SET @tmpPVUpdate = (SELECT pv FROM #tmp x WHERE a = @tmpAplus AND b = @tmpBplus)

update #tmp

set pv = ((@playerAserver * @tmpPVUpdate) + (@playerBReturn * @tmpPV))

where recno=@c

END

set @c = @c + 1

end

--end loop

select recno,a,b,pv = pv from #tmp where a <> 4 AND b <> 4

DROP TABLE #tempNum

DROP TABLE #tmp

end

-------------------------END CODE---------------