August 9, 2010 at 7:55 am
Is there an equivalent of Excel's NORMDIST function in SQL Server 2005?
August 9, 2010 at 9:01 am
Nothing right out of the box that i know of. You will probably have to write your own. (or borrow somebody else's)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2010 at 6:31 am
If it helps anyone, this is how my function looks like. Thanks again to all who helped me.
CREATE FUNCTION [dbo].[udf_NORMDIST](@value FLOAT,
@mean FLOAT,
@sigma FLOAT,
@cummulative BIT)
RETURNS NUMERIC(28,8)
AS
/****************************************************************************************
NAME: udf_NORMDIST
WRITTEN BY: Tim Pickering
http://www.eggheadcafe.com/software/aspnet/31021839/normdistx-mean-standarddevtrue-in-sql-2005.aspx
DATE: 2010/07/13
PURPOSE: Mimics Excel's Function NORMDIST
Usage: SELECT dbo.udf_NORMDIST(.48321740,0,1,0)
OUTPUT: 0.35498205
REVISION HISTORY
Date Developer Details
2010/08/11 LC Posted Function
*****************************************************************************************/
BEGIN
DECLARE @x FLOAT
DECLARE @z FLOAT
DECLARE @t FLOAT
DECLARE @ans FLOAT
DECLARE @returnvalue FLOAT
SELECT @x = (@value-@mean)/@sigma
IF (@cummulative = 1)
BEGIN
SELECT @z = abs(@x)/sqrt(2.0)
SELECT @t = 1.0/(1.0+0.5*@z)
SELECT @ans = @t*exp(-@z*@z-1.26551223+@t*(1.00002368+@t*(0.37409196+@t*(0.09678418+@t*(-0.18628806+@t*(0.27886807+@t*(-1.13520398+@t*(1.48851587+@t*(-0.82215223+@t*0.17087277)))))))))/2.0
IF (@x <= 0)
SELECT @returnvalue = @ans
ELSE
SELECT @returnvalue = 1-@ans
END
ELSE
BEGIN
SELECT @returnvalue = exp(-@x*@x/2.0)/sqrt(2.0*3.14159265358979)
END
RETURN CAST(@returnvalue AS NUMERIC(28,8))
END
September 12, 2012 at 8:15 pm
Out of the box there is no equivalent in SQL Server (up to 2012) for NORMDIST.
NORMDIST computes the Normal Distribution PDF (Probability Density Function) when the cumulative parameter is false and the CDF (Cumulative Distribution Function) otherwise.
The PDF can be computed from the definition and is pretty straight forward, the CDF however has no close form and can only be approximated.
I've developed a function for the PDF and a set of functions using different approximations for the CDF.
You can read my analysis in:
http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html
and either copy paste the ones you want from the posts or get them from the project page for the blog posts at:
May 5, 2015 at 6:15 pm
I have been able to use the udf_NORMDIST code to stand in for Excel NORM.DIST (NORMDIST). Thank-you very much for posting it!
The other reference from idea/Forum Newbie points to code for NORM.S.DIST (NORMSDIST in older Excel), it seems to be incorrectly labelled as an equivalent for NORMDIST.
However the value returned when @cummulative is set to false (0) seems to be incorrect with some larger numbers, at least it doesn't line up with Excel.
e.g. I used SELECT dbo.udf_NORMDIST(358, 352.791176470588, 234.354144866830, 0)
Excel gives me 0.001701885, but this function gave me 0.398843752
So I created a replacement for the line that returns a value when not using cumulative
The formula for this change comes from http://stattrek.com/online-calculator/normal.aspx
Normal equation:
The value of the random variable Y is:
Y = { 1/[ s * sqrt(2p) ] } * e-(x - µ)2/2s2
where X is a normal random variable,
µ is the mean,
s is the standard deviation,
p is approximately 3.14159,
and e is approximately 2.71828.
Code change:
-- Incorrect for larger numbers?
--SELECT @returnvalue = exp(-@x*@x/2.0)/sqrt(2.0*3.14159265358979)
-- Correct for any numbers...
SELECT @returnvalue = (1/(@sigma * sqrt(2.0*3.14159265358979))) * exp(-((@value - @mean)*(@value - @mean))/(2*(@sigma*@sigma)))
Also, the precision is not quite right, it needs to be 29,9, not 28,8
Here is a replacement for the function:
CREATE FUNCTION [dbo].[udf_NORMDIST](
@value FLOAT,
@mean FLOAT,
@sigma FLOAT,
@cummulative BIT )
RETURNS NUMERIC( 29,9 )
AS
/****************************************************************************************
NAME: udf_NORMDIST
WRITTEN BY: Tim Pickering
http://www.eggheadcafe.com/software/aspnet/31021839/normdistx-mean-standarddevtrue-in-sql-2005.aspx
DATE: 2010/07/13
PURPOSE: Mimics Excel's Function NORMDIST
Usage: SELECT dbo.udf_NORMDIST(.48321740,0,1,0)
OUTPUT: 0.35498205
REVISION HISTORY
Date Developer Details
2010/08/11 LC Posted Function
*****************************************************************************************/
BEGIN
DECLARE @x FLOAT;
DECLARE @z FLOAT;
DECLARE @t FLOAT;
DECLARE @ans FLOAT;
DECLARE @returnvalue FLOAT;
SELECT @x = (@value - @mean) / @sigma;
IF(@cummulative = 1)
BEGIN
SELECT @z = ABS( @x ) / SQRT( 2.0 );
SELECT @t = 1.0 / (1.0 + 0.5 * @z);
SELECT @ans = @t*exp(-@z*@z-1.26551223+@t*(1.00002368+@t*(0.37409196+@t*(0.09678418+@t*(-0.18628806+@t*(0.27886807+@t*(-1.13520398+@t*(1.48851587+@t*(-0.82215223+@t*0.17087277)))))))))/2.0;
IF(@x <= 0)
SELECT @returnvalue = @ans;
ELSE
SELECT @returnvalue = 1 - @ans;
END;
ELSE
BEGIN
SELECT @returnvalue = (1/(@sigma * SQRT( 2.0 * 3.14159265358979 ))) * EXP(-((@value-@mean)*(@value-@mean)) / (2*(@sigma*@sigma)));
END;
RETURN CAST( @returnvalue AS NUMERIC( 29,9 ));
END;
May 19, 2015 at 9:35 pm
Nothing out of the box as stated, but there is this:
Excel in T-SQL Part 2 – The Normal Distribution (NORM.DIST) Density Functions[/url]
There are also some links at the end to approximation formulas for the cumulative distribution function. The probability density function is pretty straightforward.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 8, 2019 at 5:37 am
use this for POSTGRESQL:
-- Start
CREATE OR REPLACE FUNCTION stock.udf_NORMDIST(
value1 FLOAT,
mean float,
sigma float,
cummulative float )
RETURNS NUMERIC( 29,9 )
AS $total$
declare total integer;
DECLARE x FLOAT;
DECLARE z FLOAT;
DECLARE t FLOAT;
DECLARE ans FLOAT;
DECLARE returnvalue FLOAT;
BEGIN
select (value1 - mean) / sigma into x;
IF(cummulative = 1) then
select ABS( x ) / SQRT( 2.0 ) into z;
select 1.0 / (1.0 + 0.5 * z) into t;
select (t*exp(-z*z-1.26551223+t*(1.00002368+t*(0.37409196+t*(0.09678418+t*(-0.18628806+t*(0.27886807+t*(-1.13520398+t*(1.48851587+t*(-0.82215223+t*0.17087277)))))))))/2.0) into ans;
IF(x <= 0) then
SELECT ans into returnvalue;
ELSE
SELECT 1 - ans into returnvalue;
END IF;
ELSE
SELECT (1/(sigma * SQRT( 2.0 * 3.14159265358979 ))) * EXP(-((value1-mean)*(value1-mean)) / (2*(sigma*sigma))) INTO returnvalue;
END IF;
--RETURN x;
RETURN CAST( returnvalue * 100 AS NUMERIC( 29,2 ));
END; $total$
LANGUAGE PLPGSQL;
-- END
example usage: SELECT stock.udf_NORMDIST(-2.55,0,1,1)
December 9, 2019 at 5:31 pm
use this for POSTGRESQL:
-- Start
This really isn't helpful. This code is POSTGRE but this is a sql server forum.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply