Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool Unanswered: Probability Theory

    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
    Last edited by Lenny77; 10-07-10 at 17:54.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Average Of Distribution

    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

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Calculating Standard Deviation

    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
    Last edited by Lenny77; 10-08-10 at 16:57.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Permutation of characters in the word

    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
    Last edited by Lenny77; 10-13-10 at 18:52. Reason: change query

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Change RandInt function, override RandInt

    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

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Dice game / two players / 3 dices

    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Have you ever considered learning Java or C?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by Marcus_A View Post
    Have you ever considered learning Java or C?
    Why did you ask ?
    Looks like CLASS CONSTRUCTOR ?

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Lenny77 View Post
    Why did you ask ?
    Looks like CLASS CONSTRUCTOR ?
    Becasue these types of calcuations seem more appropriate in a programming language than in a DBMS.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by Marcus_A View Post
    Becasue these types of calcuations seem more appropriate in a programming language than in a DBMS.
    That's what I am exactly want: Using DBMS like any other programming languages.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Lenny77 View Post
    That's what I am exactly want: Using DBMS like any other programming languages.
    A DBMS is not a programming language, it is a dababase managment system.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by Marcus_A View Post
    A DBMS is not a programming language, it is a dababase managment system.
    How you can see, I can use DB2 functions and statements to solve different programming problems. Is it not good ?

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One other question: are you retired?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by Marcus_A View Post
    One other question: are you retired?
    No, I am not. I am SPA and VP of very huge financial company.

    But this question actually not a legal.

  15. #15
    Join Date
    Nov 2004
    Posts
    54
    Quote Originally Posted by Lenny77 View Post
    No, I am not. I am SPA and VP of very huge financial company.

    But this question actually not a legal.

    I *LOVE* the things you come up with Lenny. Keep it up!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •