All Forums Data Modeling
pimkuurman 1 post Joined 05/13
27 May 2013
Does Teradata have a probit or Normal dsitribution function

I've made a probit regression model using a statistical package. I'de like to use Teradata to score our customers. For this I need a function that converts a real value to a probability value (value between 0 and 1) based on the normal distribution. Is there such a function, or method to do this in Teradata SQL? In Excel for example this function is called NORMDIST().

uniqueneo1 1 post Joined 05/16
08 Jun 2016

Have you heard any response regarding normal distribution in Teradata? I am looking to do the same thing bank accounts balances

dnoeth 4628 posts Joined 11/04
08 Jun 2016

If you only need the PDF:

/*
   Probability density function for Normal Distribution
   
   https://en.wikipedia.org/wiki/Probability_density_function
*/
REPLACE FUNCTION NormDist
(
    val        NUMBER      -- Value to be evaluated
    ,Mean      NUMBER      -- Mean of the Normal Distribution
    ,StdDev    NUMBER      -- Standard Deviation of the Normal Distribution
)
RETURNS NUMBER
LANGUAGE SQL
CONTAINS SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
   EXP(-0.5*(val-Mean)*(val-Mean) / (StdDev*StdDev)) / (2.506628274631 /*=SQRT(2.*PI()*/ *StdDev);

To get the Cumulative PDF you need to implement a C-UDF.

Dieter

RaulFS 1 post Joined 06/16
14 Jun 2016

Hello,
Is it possible to get the integral of this function in Teradata?we need the probability distribution function, and that would be the integral of the density function.
In Excel sheet this calcultation can be done with DISTR.NORM.ESTAND.N functions, and INV.NORM.ESTAND for the inverse function.
Example:
DISTR.NORM.ESTAND.N(1,96;TRUE)
INV.NORM.ESTAND(0,025)
 
 
 

dnoeth 4628 posts Joined 11/04
15 Jun 2016

NORM.S.INV is the Cumulative distribution, there's no way to calculate an integral in SQL.
A quick serach shows some solutions for SQL Server, which might be easily adopted to Teradata (lots of Search&Replace to get a single calculation):
http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html
 
But of course the simplest and most efficient way would be wrapping one of the many C-implemetations into a UDF. 

Dieter

You must sign in to leave a comment.