I’ve just completed a project at  tennismatchodds which 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]

GO

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)

 

GO

 

 

 

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---------------

Source:Jack Vamvas (http://www.sqlserver-dba.com)
Author: Jack Vamvas (http://www.sqlserver-dba.com)