Probability Theory

Lenny77 · #1 (**permalink**) 10-07-10, 16:51

1. For make our life easier we have to create the function RandInt:

Code:

drop function RandInt;

CREATE FUNCTION RandInt (X integer) 
RETURNS integer 
LANGUAGE SQL 
CONTAINS SQL 
NO EXTERNAL ACTION 
not DETERMINISTIC 
RETURN int(X * Rand() + .5);

2. After our function was created we can play heads or tails game without dime, using the query:

Code:

select case when randint(test) = 0 then 'Tail'
            else 'Head'
       end "Game With Dime" 
from
(select 1 test from sysibm.sysdummy1) tst

3. Average of distribution (in our example for 10000), you'll find out how it close to 5000 = 10000 / 2:

Code:

with prob (k, maxK, total) as
(select 0, 10000, 0 from sysibm.sysdummy1
union all
select k + 1, maxK, total + randint(1)
from prob
where k + 1 <= maxK
) 
select maxK, total from prob
where k = maxK

Lenny

Lenny77 · #2 (**permalink**) 10-07-10, 17:53

You can find Average Of Distribution without function, if you don't have the authority to create function:

Code:

with prob (k, maxK, total) as
(select 0, 10000, 0 from sysibm.sysdummy1
union all
select k + 1, maxK, total + int(rand() + .5)
from prob
where k + 1 <= maxK
) 
select maxK, total from prob
where k = maxK

Lenny

Lenny77 · #3 (**permalink**) 10-08-10, 12:20

VARIANCE The formula used to calculate the biased variance is logically equivalent to:
VARIANCE = SUM(X**2)/COUNT(X) - (SUM(X)/COUNT(X))**2

Standard Deviation:

STDDEV = SQRT(VARIANCE).

Now I'm using the RandInt function, created the test example, to try, how it's working in a real life:

Code:

with prob (k, maxK, prb, total, totsqr) as
(select 0, 100, 1, 0, 0 
   from sysibm.sysdummy1
union all
select pb.k + 1, maxK, prb, total + rnd, 
       totsqr + power(rnd, 2)
from prob pb, 
table
(select randint(prb) rnd, pb.k
from sysibm.sysdummy1 ) rr
where pb.k + 1 <= maxK
) 
select maxK, strip(char(total)) || ' to ' || char(maxK * prb) chance_to_win, 
sqrt(totsqr * 1. / maxK - power((total * 1. / maxK), 2)) 
     as Standard_Deviation 
from prob
where k = maxK;

Typical result:

MAXK...... CHANCE_TO_WIN.......... STANDARD_DEVIATION
10000........... 497 to 1000.......... 4.99995589980552E-001

Lenny

Lenny77 · #4 (**permalink**) 10-12-10, 17:43

Let see how it working
if we make from the input word to the new word with the same characters.

Code:

with Input (word) as 
(select '1234567890ABCDEF' from sysibm.sysdummy1
)
,
RandCharOrder (randword, remword, k) as
(select varchar('', 1000), strip(word), length(word)  
   from Input
union all
select randword || substr(remword, pos, 1), 
case when pos = 1 and length(remword) > 1
     then substr(remword, pos + 1)
     when length(remword) = 1
     then ''
     else substr(remword, 1, pos - 1) || substr(remword, pos + 1)
end, ch.k - 1
from RandCharOrder ch, Input, 
table
(select randint(length(remword) - 1) + 1 pos
from sysibm.sysdummy1 ) rr

Where length(remword) > 0  
) 
select word, randword from RandCharOrder, Input
where k = 0;

Lenny

Lenny77 · #5 (**permalink**) 10-15-10, 13:02

The function RandInt(integer) is working now in interval:
[0, X] when X > 0
[X, 0] when X < 0
[0, 1] when X in (0, 1)

Code:

drop function RandInt(integer);

CREATE FUNCTION RandInt (X integer) 
RETURNS integer 
LANGUAGE SQL 
CONTAINS SQL 
NO EXTERNAL ACTION 
not DETERMINISTIC 
RETURN 
case when X = 0 
then int(1 * Rand() + .5)
else int(X * Rand() + 0.5 * sign(X))
end
;

The function RandInt(integer, integer) is working in interval:
[start#, end#] when end# >= start#
[end#, start#] when end# <= start#

Code:

CREATE FUNCTION RandInt (start# integer, end# integer) 
RETURNS integer 
LANGUAGE SQL 
CONTAINS SQL 
NO EXTERNAL ACTION 
not DETERMINISTIC 
RETURN RandInt(end# - start#) + start#;

What could be also interesting to us:
Create statement of RandInt(integer, integer) function consists call to
parent function RandInt(integer).

Example of using new functions:

Code:

select 
randint(500) int1, randint(1000) int2, randint(500, 1000) int3
from sysibm.sysdummy1;

Lenny

Lenny77 · #6 (**permalink**) 10-21-10, 16:53

We will use the RandInt(m, n) function, because of dice has numbers (in random sequence)
from 1 to 6, so it will be RandInt(1, 6) for each dice:

Code:

select 
player1.dice1, player1.dice2, player1.dice3, 
player2.dice1, player2.dice2, player2.dice3, 
(player1.dice1 + player1.dice2 + player1.dice3) player1,
(player2.dice1 + player2.dice2 + player2.dice3) player2,
case when (player1.dice1 + player1.dice2 + player1.dice3) >
          (player2.dice1 + player2.dice2 + player2.dice3)
     then 'Player1  WIN !' 
     when (player1.dice1 + player1.dice2 + player1.dice3) <
          (player2.dice1 + player2.dice2 + player2.dice3)
     then 'Player2  WIN !' 
     else 'Tie (draw)' 
end result  
  
from
(select randint(1, 6) dice1, randint(1, 6) dice2, randint(1, 6) dice3
from sysibm.sysdummy1) player1
,
(select randint(1, 6) dice1, randint(1, 6) dice2, randint(1, 6) dice3
from sysibm.sysdummy1) player2

You can TRY.