Normal Distribution function resulting similar to MySQL or Microsoft Excel

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Normal Distribution function resulting similar to MySQL or Microsoft Excel

Hi Guys,

 

Could anyone please help me resolve this issue as our client needs a function or UDF to be defined in TERADATA which would result in similar values as their current excel results. We tried creating a function in MySQL which matches exactly, but in TD we're facing variances which I assume becuase of the precision loss in calculation. FYR, following is the procedure we've defined in MySQL,

 

EX: Normdist (1,0.5,1.885912688) in MySQL=0.604543, whereas in TD when we tried similar math in SQL, it was around 0.5545

 

CREATE FUNCTION NORMDIST(x float, mean float, stdev float ) RETURNS float
BEGIN
set @z = (x -mean) / stdev;
set @b1 = 0.319381530;
set @b2 = -0.356563782;
set @b3 = 1.781477937;
set @b4 = -1.821255978;
set @b5 = 1.330274429;
set @p = 0.2316419;
set @c = 0.39894228;
IF @z >= 0.0 THEN
set @t = 1.0 / ( 1.0 + @p * @z );
return (1.0 - @c * exp(-@z * @z / 2.0) * @t * ( @t * ( @t * ( @t * ( @t * @b5 + @b4 ) + @b3 ) + @b2 ) + @b1 ));
ELSE
set @t = 1.0 / ( 1.0 - @p * @z );
return ( @c * exp(-@z * @z / 2.0) * @t * ( @t * ( @t * ( @t * ( @t * @b5 + @b4 ) + @b3 ) + @b2 ) + @b1 ));
END IF;
END

 

Kindly help me if any of you have sorted this kind of similar issue in your experience.

 

Thanks,

Wiki


Wiki

Accepted Solutions
Enthusiast

Re: Normal Distribution function resulting similar to MySQL or Microsoft Excel

Much Thanks for your help dnoeth. Based on your function, I've done some more modification for appropriate result,

 

REPLACE FUNCTION NORMDIST
(
    X FLOAT     -- VALUE TO BE EVALUATED
    ,MEAN FLOAT   -- MEAN OF THE NORMAL DISTRIBUTION
    ,STDEV FLOAT  -- STANDARD DEVIATION OF THE NORMAL DISTRIBUTION
)
RETURNS DECIMAL(38,12)
LANGUAGE SQL
CONTAINS SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
CASE WHEN X>=0 THEN 1 ELSE 0 END 
+ 
CASE WHEN X>=0 THEN -1 ELSE 1 END 
* 0.39894228 
 							* EXP(-((X-MEAN)/STDEV)* (X-MEAN)/STDEV/2.0) 
							*  (1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END  * ((X-MEAN)/STDEV))) 
							*  ( (1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV))) 
								* ((1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV))) 
								* ((1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV)))  
								* ((1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV))) * 1.330274429 + (-1.821255978)
								) + 1.781477937
								) -0.356563782
								) + 0.319381530
								)
  ;

Because if X>=0 then value should 1- <something> , you've written -1+ <something> which is negating the results. Also T variable is different for X>=0 & X<0.

 

Thanks again.

 

Wiki


Wiki
1 ACCEPTED SOLUTION
3 REPLIES
Senior Apprentice

Re: Normal Distribution function resulting similar to MySQL or Microsoft Excel

You probably did something wrong when you translated the formula.

This is a similar function using the same formula, it returns what you expected:

REPLACE FUNCTION NORMDIST(x FLOAT, mean FLOAT, stdev FLOAT)
RETURNS FLOAT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
   CASE WHEN x > 0.0 THEN -1 ELSE 0 END
   + (0.39894228 
      * Exp(-((x - mean) / stdev) * ((x - mean) / stdev) / 2)
      * (1 / (1 + 0.2316419 * ((x - mean) / stdev))) 
      * ((1 / (1 + 0.2316419 * ((x - mean) / stdev))) 
          * ((1 / (1 + 0.2316419 * ((x - mean) / stdev))) 
              * ((1 / (1 + 0.2316419 * ((x - mean) / stdev))) 
                  * ((1 / (1 + 0.2316419 * ((x - mean) / stdev))) * 1.330274429 -1.821255978
                    ) + 1.781477937
                ) + -0.356563782
            ) + 0.319381530
        )
     );

 

Enthusiast

Re: Normal Distribution function resulting similar to MySQL or Microsoft Excel

Much Thanks for your help dnoeth. Based on your function, I've done some more modification for appropriate result,

 

REPLACE FUNCTION NORMDIST
(
    X FLOAT     -- VALUE TO BE EVALUATED
    ,MEAN FLOAT   -- MEAN OF THE NORMAL DISTRIBUTION
    ,STDEV FLOAT  -- STANDARD DEVIATION OF THE NORMAL DISTRIBUTION
)
RETURNS DECIMAL(38,12)
LANGUAGE SQL
CONTAINS SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
CASE WHEN X>=0 THEN 1 ELSE 0 END 
+ 
CASE WHEN X>=0 THEN -1 ELSE 1 END 
* 0.39894228 
 							* EXP(-((X-MEAN)/STDEV)* (X-MEAN)/STDEV/2.0) 
							*  (1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END  * ((X-MEAN)/STDEV))) 
							*  ( (1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV))) 
								* ((1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV))) 
								* ((1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV)))  
								* ((1.0/(1+CASE WHEN X>=0 THEN  0.2316419 ELSE - 0.2316419 END   * ((X-MEAN)/STDEV))) * 1.330274429 + (-1.821255978)
								) + 1.781477937
								) -0.356563782
								) + 0.319381530
								)
  ;

Because if X>=0 then value should 1- <something> , you've written -1+ <something> which is negating the results. Also T variable is different for X>=0 & X<0.

 

Thanks again.

 

Wiki


Wiki
Senior Apprentice

Re: Normal Distribution function resulting similar to MySQL or Microsoft Excel

Ops, seems I oversimplified the calculation, didn't notice the +/-