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)
