正太分布函数和反函数标量值函数 (借鉴)

最新推荐文章于 2024-04-10 10:31:57 发布

Irene_miss

最新推荐文章于 2024-04-10 10:31:57 发布

阅读量5.2k

点赞数

--标准正态分布函数
CREATE function [dbo].[normcdf] ( @p decimal(28,18) )
AS
begin
declare @L decimal(28,18)
declare @K decimal(28,18)
declare @dCND decimal(28,18)
declare @pi decimal(28,18)

declare @a1 decimal(28,18)
declare @a2 decimal(28,18)
declare @a3 decimal(28,18)
declare @a4 decimal(28,18)
declare @a5 decimal(28,18)

select @L = 0.0
select @K = 0.0
select @dCND = 0.0

select @a1 = 0.31938153
select @a2 = -0.356563782
select @a3 = 1.781477937
select @a4 = -1.821255978
select @a5 = 1.330274429
select @pi = 3.1415926535897932384626433832795
  select @L = Abs(@p)

if @L >= 30
begin
if sign(@p) = 1 begin
select @n_result = 1

end else begin
select @n_result = 0

end
end
else
begin
-- perform calculation
select @K = 1.0 / (1.0 + 0.2316419 * @L)
select @dCND = 1.0 - 1.0 / Sqrt(2 * @pi) * Exp(-@L * @L / 2.0) *
(@a1 * @K + @a2 * @K * @K + @a3 * POWER(@K, 3.0) + @a4 * POWER(@K, 4.0) + @a5 * POWER (@K, 5.0))

if (@p < 0) begin
select @n_result = 1.0 - @dCND

end else begin
select @n_result = @dCND

end
end

return @n_result

end

--标准正态分布反函数
CREATE function [dbo].[norminv] (@p decimal(28,18))
returns decimal(28,18)
as
begin

declare @a1 decimal(28,18)
declare @a2 decimal(28,18)
declare @a3 decimal(28,18)
declare @a4 decimal(28,18)
declare @a5 decimal(28,18)
declare @a6 decimal(28,18)
declare @b1 decimal(28,18)
declare @b2 decimal(28,18)
declare @b3 decimal(28,18)
declare @b4 decimal(28,18)
declare @b5 decimal(28,18)
declare @c1 decimal(28,18)
declare @c2 decimal(28,18)
declare @c3 decimal(28,18)
declare @c4 decimal(28,18)
declare @c5 decimal(28,18)
declare @c6 decimal(28,18)
declare @d1 decimal(28,18)
declare @d2 decimal(28,18)
declare @d3 decimal(28,18)
declare @d4 decimal(28,18)
declare @plow decimal(28,18)
declare @phigh decimal(28,18)
declare @q decimal(28,18)
declare @r decimal(28,18)
declare @g_result decimal(28,18)


SET @a1 = -39.6968302866538
SET @a2 = 220.946098424521
SET @a3 = -275.928510446969
SET @a4 = 138.357751867269
SET @a5 = -30.6647980661472
SET @a6 = 2.50662827745924
SET @b1 = -54.4760987982241
SET @b2 = 161.585836858041
SET @b3 = -155.698979859887
SET @b4 = 66.8013118877197
SET @b5 = -13.2806815528857
SET @c1 = -7.78489400243029E-03
SET @c2 = -0.322396458041136
SET @c3 = -2.40075827716184
SET @c4 = -2.54973253934373
SET @c5 = 4.37466414146497
SET @c6 = 2.93816398269878
SET @d1 = 7.78469570904146E-03
SET @d2 = 0.32246712907004
SET @d3 = 2.445134137143
SET @d4 = 3.75440866190742
SET @plow=0.02425
SET @phigh=1-@plow

if (@p<@plow)
begin
select @q = Sqrt(-2 * Log(@p))
select @g_result=(((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1)

end
else begin
if (@p<@phigh)
begin
select @q =@p - 0.5
select @r = @q * @q
select @g_result= (((((@a1 * @r + @a2) * @r + @a3) * @r + @a4) * @r + @a5) * @r + @a6) * @q / (((((@b1 * @r + @b2) * @r + @b3) * @r + @b4) * @r + @b5) * @r + 1)

end
else begin
select @q = Sqrt(-2 * Log(1 - @p))
select @g_result= -(((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1)

end
end

return @g_result
end