[phil-story] Always Fighting 

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) 오라클 함수로 작성*/

/*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;
/

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;
/