Data Modeling

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-27-2013
01:51 AM

05-27-2013
01:51 AM

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().

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-08-2016
06:47 AM

06-08-2016
06:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-08-2016
01:17 PM

06-08-2016
01:17 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-14-2016
11:59 PM

06-14-2016
11:59 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-15-2016
01:35 PM

06-15-2016
01:35 PM

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.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.