## 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 +/-