Results 1 to 6 of 6

Thread: NORMDIST in DB2

  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: NORMDIST in DB2

    Hi ,

    Please help me in finding equivalent function in DB2 that is used as NORMSDIST function in excel.

    or if there is no function in DB2 can you please guide if there are any methods that we can use to derrive NORMSDIST function.

    thnanks in anticipation,
    Praneet

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the specifications of the NORMSDIST function?
    - Input parameters(mandatory and optional) and their datatypes.
    - Return value and its datatype

  3. #3
    Join Date
    Jul 2012
    Posts
    4
    The following function is used in excel and we need to implement this in DB2
    MIN(2*NORMSDIST(H2),2*(1-NORMSDIST(H2))

    where H2 is cell value

    H2 Values are like 1.3387
    and the value return for the above function is 0.18066

    Yes Values are Mandatory and their data type is Decimal Values
    and return value is also decimal .

    thanks,
    Praneet

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What does NORMSDIST do? Is it this one: NORMSDIST function ? If so, you have the formula that you could implement with the corresponding SQL operations, couldn't you?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Thank you, Stolze and Praneet.

    Looking in reference, Normal Distribution Function can NOT be calculaed only by elementary funcions.
    Integral calculus is necessary to calculate exactly.
    So, some approximations must be necessary on SQL.

    Here is a trial example.
    But, evaluation of accuracy is insufficient...
    - Selection of approximation functions
    - Boundary of each functions
    - Margin of error
    - Precision of parameter and return value
    - so on...

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE FUNCTION normsdist(z DEC(9 , 8) )
    RETURNS DEC(9 , 8)
    CONTAINS SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    /*
    Referenced materials:
    (1) Normal Distribution Function
    http://mathworld.wolfram.com/NormalDistributionFunction.html
    
    (2) Divisors for (10) in (1)
    Sloane's A014481
    http://oeis.org/A014481
    
    (3) Maclaurin series for erf
    http://mathworld.wolfram.com/Erf.html
    */
    RETURN
       0.5
     + CASE
       WHEN ABS(z) < 2 THEN
         /* derived from Maclaurin series for erf -- (10) in (1) */
            (        z
             - POWER(z ,  3) /             6
             + POWER(z ,  5) /            40
             - POWER(z ,  7) /           336
             + POWER(z ,  9) /          3456
             - POWER(z , 11) /         42240
             + POWER(z , 13) /        599040
             - POWER(z , 15) /       9676800
             + POWER(z , 17) /     175472640
             - POWER(z , 19) /    3530096640
             + POWER(z , 21) /   78033715200
             - POWER(z , 23) / 1880240947200 /* Sloane's A014481 */
            )  / SQRT(2. * 3.14159265358979)
       ELSE
         /* Approximation due to Bagby (1995) -- (14) in (1) */
            SIGN(z)
          * SQRT(
               1 - (   7 * EXP( - z*z / 2 )
                    + 16 * EXP( - z*z * (2 - SQRT(2)) )
                    + (7 + 3.14159265358979 * z*z / 4) * EXP( - z*z )
                   ) / 30
            ) / 2
       END
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Use of the example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES MIN( 2 * NORMSDIST(1.3387) , 2 * (1 - NORMSDIST(1.3387) ) );
    ------------------------------------------------------------------------------
    
    1                                
    ---------------------------------
                           0.18066836
    
      1 record(s) selected.

  6. #6
    Join Date
    Jul 2012
    Posts
    4
    Thank you Very Much tonkuma..The below routine is working for us.

Posting Permissions

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