NORMSINV 가 무엇인지를 찾아보니 표준편차를 구하는 엑셀의 함수였던 것인데...
이는 오라클에 정의되어져 있지 않기 때문에 사용자 정의 함수로 만들어야 한다는 것이다.
관련해서 2가지의 포스트를 발견하였으니 스크랩한다..
1. ====================================================================================
1번의 경우에는 우선적으로 NORMDIST 라는 함수부터 생성을 해야한다..
NORMSINV 함수에서 NORMDIST를 호출하기 때문이다...
1. ====================================================================================
1. ====================================================================================
http://home.online.no/~pjacklam/notes/invnorm/
http://support.microsoft.com/kb/826772
plsql source
http://home.online.no/~pjacklam/notes/invnorm/impl/smit/
/*excel 함수 : NORMDIST(x) 오라클 함수로 작성*/
create or replace function fc_stdnormal_cdf(u in float)
return float is
type doubleArray is varray(9) of float;
a doubleArray := doubleArray (
1.161110663653770e-002,3.951404679838207e-001,2.846603853776254e+001,
1.887426188426510e+002,3.209377589138469e+003
);
b doubleArray := doubleArray (
1.767766952966369e-001,8.344316438579620e+000,1.725514762600375e+002,
1.813893686502485e+003,8.044716608901563e+003
);
c doubleArray := doubleArray (
2.15311535474403846e-8,5.64188496988670089e-1,8.88314979438837594e00,
6.61191906371416295e01,2.98635138197400131e02,8.81952221241769090e02,
1.71204761263407058e03,2.05107837782607147e03,1.23033935479799725E03
);
d doubleArray := doubleArray (
1.00000000000000000e00,1.57449261107098347e01,1.17693950891312499e02,
5.37181101862009858e02,1.62138957456669019e03,3.29079923573345963e03,
4.36261909014324716e03,3.43936767414372164e03,1.23033935480374942e03
);
p doubleArray := doubleArray (
1.63153871373020978e-2,3.05326634961232344e-1,3.60344899949804439e-1,
1.25781726111229246e-1,1.60837851487422766e-2,6.58749161529837803e-4
);
q doubleArray := doubleArray (
1.00000000000000000e00,2.56852019228982242e00,1.87295284992346047e00,
5.27905102951428412e-1,6.05183413124413191e-2,2.33520497626869185e-3
);
z float;
y float;
outValue float;
begin
y := abs(u);
if (y <= 0.46875*sqrt(2)) then
/* evaluate erf() for |u| <= sqrt(2)*0.46875 */
z := y*y;
y := u*((((a(1)*z+a(2))*z+a(3))*z+a(4))*z+a(5))
/((((b(1)*z+b(2))*z+b(3))*z+b(4))*z+b(5));
outValue:=0.5+y;
return outValue;
end if;
z := exp(-y*y/2)/2;
if (y <= 4.0*sqrt(2)) then
/* evaluate erfc() for sqrt(2)*0.46875 <= |u| <= sqrt(2)*4.0 */
y := y/sqrt(2);
y :=
((((((((c(1)*y+c(2))*y+c(3))*y+c(4))*y+c(5))*y+c(6))*y+c(7))*y+c(8))*y+c(9))
/((((((((d(1)*y+d(2))*y+d(3))*y+d(4))*y+d(5))*y+d(6))*y+d(7))*y+d(8))*y+d(9));
y := z*y;
else
/* evaluate erfc() for |u| > sqrt(2)*4.0 */
z := z*sqrt(2)/y;
y := 2/(y*y);
y := y*(((((p(1)*y+p(2))*y+p(3))*y+p(4))*y+p(5))*y+p(6))
/(((((q(1)*y+q(2))*y+q(3))*y+q(4))*y+q(5))*y+q(6));
y := z*(1/sqrt(3.141592654)-y);
end if;
if(u<0) then outValue:=y;
else outValue:=1-y; end if;
return outValue;
end fc_stdnormal_cdf;
/
/*excel 함수 : NORMSINV(probability) 오라클 함수로 작성*/
create or replace function fc_stdnormal_inv(p in float)
return float is
type doubleArray is varray(6) of float;
a doubleArray := doubleArray(
-3.969683028665376e+01, 2.209460984245205e+02,
-2.759285104469687e+02, 1.383577518672690e+02,
-3.066479806614716e+01, 2.506628277459239e+00
);
b doubleArray := doubleArray(
-5.447609879822406e+01, 1.615858368580409e+02,
-1.556989798598866e+02, 6.680131188771972e+01,
-1.328068155288572e+01
);
c doubleArray := doubleArray(
-7.784894002430293e-03, -3.223964580411365e-01,
-2.400758277161838e+00, -2.549732539343734e+00,
4.374664141464968e+00, 2.938163982698783e+00
);
d doubleArray :=doubleArray(
7.784695709041462e-03, 3.224671290700398e-01,
2.445134137142996e+00, 3.754408661907416e+00
);
q float;
t float;
u float;
begin
if (p = 0.0) then
u:=-10000;
return u;
end if;
if (p = 1.0) then
u:=10000;
return u;
end if;
if (p = 0.5) then
u:=-1.39214E-16;
return u;
end if;
q := least(p,1-p);
if (q > 0.02425) then
/* Rational approximation for central region. */
u := q-0.5;
t := u*u;
u := u*(((((a(1)*t+a(2))*t+a(3))*t+a(4))*t+a(5))*t+a(6))
/(((((b(1)*t+b(2))*t+b(3))*t+b(4))*t+b(5))*t+1);
else
/* Rational approximation for both tail regions. */
t := sqrt(-2*ln(q));
u := (((((c(1)*t+c(2))*t+c(3))*t+c(4))*t+c(5))*t+c(6))
/((((d(1)*t+d(2))*t+d(3))*t+d(4))*t+1);
end if;
/* The relative error of the approximation has absolute value less
than 1.15e-9. One iteration of Halley's rational method (third
order) gives full machine precision... */
t :=fc_stdnormal_cdf(u);
t :=t-q; /* error */
t := t*sqrt(2*3.141592654)*exp(u*u/2); /* f(u)/df(u) */
u := u-t/(1+u*t/2); /* Halley's method */
if(p>0.5) then
u:=-u;
end if;
return u;
end fc_stdnormal_inv;
/
[출처] http://blog.naver.com/pumba3?Redirect=Log&logNo=10043752102
2. ==============================================================================
2. ==============================================================================
2. ==============================================================================
CREATE OR REPLACE
function NORMSINV(p number) return number IS
plow NUMBER := 0.02425;
phigh NUMBER := 1 - plow;
q NUMBER;
r NUMBER;
-- Coefficients in rational approximations
a1 NUMBER := -3.969683028665376e+01;
a2 NUMBER := 2.209460984245205e+02;
a3 NUMBER := -2.759285104469687e+02;
a4 NUMBER := 1.383577518672690e+02;
a5 NUMBER := -3.066479806614716e+01;
a6 NUMBER := 2.506628277459239e+00;
b1 NUMBER := -5.447609879822406e+01;
b2 NUMBER := 1.615858368580409e+02;
b3 NUMBER := -1.556989798598866e+02;
b4 NUMBER := 6.680131188771972e+01;
b5 NUMBER := -1.328068155288572e+01;
c1 NUMBER := -7.784894002430293e-03;
c2 NUMBER := -3.223964580411365e-01;
c3 NUMBER := -2.400758277161838e+00;
c4 NUMBER := -2.549732539343734e+00;
c5 NUMBER := 4.374664141464968e+00;
c6 NUMBER := 2.938163982698783e+00;
d1 NUMBER := 7.784695709041462e-03;
d2 NUMBER := 3.224671290700398e-01;
d3 NUMBER := 2.445134137142996e+00;
d4 NUMBER := 3.754408661907416e+00;
BEGIN
-- Rational approximation for lower region:
IF p < plow THEN
q := SQRT(-2 * LN(p));
return (((((c1*q+c2)*q+c3)*q+c4)*q+c5)*q+c6) /
((((d1*q+d2)*q+d3)*q+d4)*q+1);
END IF;
-- Rational approximation for upper region:
IF phigh < p THEN
q := SQRT(-2 * LN(1-p));
return -(((((c1*q+c2)*q+c3)*q+c4)*q+c5)*q+c6) /
((((d1*q+d2)*q+d3)*q+d4)*q+1);
END IF;
-- Rational approximation for central region:
q := p - 0.5;
r := q*q ;
return (((((a1*r+a2)*r+a3)*r+a4)*r+a5)*r+a6)*q /
(((((b1*r+b2)*r+b3)*r+b4)*r+b5)*r+1);
return a2;
END;
/
[출처] http://blog.naver.com/devil349?Redirect=Log&logNo=50016774553
