반응형
/***** STEP.1 : 정규분포값 환산하는 오라클 펑션 생성하기 *****/
CREATE OR REPLACE FUNCTION GETSTDNORMALCDF(U IN FLOAT) RETURN NUMBER
IS
/*
* PGM ID : GETSTDNORMALCDF
* TITLE : 정규누적분포 반환
* AUTHOR : grapro.lim
* DATE : 2010.10.28
* VERSION : 1.0
* CONTENTS : 참조사이트
-> http://home.online.no/~pjacklam/notes/invnorm/
-> http://blog.naver.com/pumba3/10043752102
*/
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;
/***** STEP.2 : 쿼리실행(바인드변수 셋팅, SN_EMP_CNT 컬럼값이 정규분포높이로 환산된 n개의 건수) *****/
WITH V1 AS
(
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL UNION ALL
SELECT NULL NO FROM DUAL
)
SELECT A.*
, SN_EMP_S - LAG(SN_EMP_S,1,0) OVER (ORDER BY H_NO) AS SN_EMP_CNT
FROM (
SELECT A.*
, SUM(SN_Y_P) OVER (ORDER BY H_NO) AS SN_Y_PS
, ROUND(EMP_CNT * SUM(SN_Y_P) OVER (ORDER BY H_NO) / 100) AS SN_EMP_S
FROM (
SELECT A.*
, (
CASE
WHEN H_NO <= HCNT
THEN (SN_Y * (M_P-H_P)) + H_P
WHEN L_NO <= HCNT
THEN (SN_Y * (M_P-L_P)) + L_P
ELSE M_P
END
)
* 100
/ SUM
(
CASE
WHEN H_NO <= HCNT
THEN (SN_Y * (M_P-H_P)) + H_P
WHEN L_NO <= HCNT
THEN (SN_Y * (M_P-L_P)) + L_P
ELSE M_P
END
) OVER ()
AS SN_Y_P
FROM (
SELECT A.*
, CASE
WHEN H_NO <= HCNT
THEN SN_H_SUM
WHEN L_NO <= HCNT
THEN SN_L_SUM
ELSE 3
END AS SN_X
, GETSTDNORMALCDF
(
CASE
WHEN H_NO <= HCNT
THEN SN_H_SUM
WHEN L_NO <= HCNT
THEN SN_L_SUM
ELSE 3
END
) AS SN_Y
FROM (
SELECT A.*
, SUM(DECODE(H_NO,1,-3,SN_STEP)) OVER (ORDER BY H_NO) AS SN_H_SUM
, SUM(DECODE(L_NO,1,-3,SN_STEP)) OVER (ORDER BY L_NO) AS SN_L_SUM
FROM (
SELECT B.NO AS H_NO
, ROW_NUMBER() OVER (ORDER BY B.NO DESC) AS L_NO
, A.W_MAX - (A.STEP * (B.NO-1)) AS WGT
, A.*
FROM (
SELECT A.*
, 6 / HCNT AS SN_STEP
FROM (
SELECT A.*
, TRUNC((W_MAX - W_MIN) / STEP + 1) AS TCNT
, TRUNC(( TRUNC((W_MAX - W_MIN) / STEP + 1) -1) / 2) AS HCNT
FROM (
SELECT H_P
, M_P
, L_P
, TRUNC(W_MAX/STEP)*STEP AS W_MAX
, TRUNC(W_MIN/STEP)*STEP AS W_MIN
, STEP
, EMP_CNT
FROM (
/* BND변수 셋팅 */
SELECT 30 AS H_P /* BND - 정규분포 X축 좌측 Y높이 PERCENT */
, 80 AS M_P /* BND - 정규분포 X축 중간 Y높이 PERCENT */
, 30 AS L_P /* BND - 정규분포 X축 우측 Y높이 PERCENT */
, 140 AS W_MAX /* BND - 정규분포 X축 좌측 값 */
, 60 AS W_MIN /* BND - 정규분포 X축 우측 값 */
, 5 AS STEP /* BND - 정규분포 X축 STEP 값 */
, 3000 AS EMP_CNT /* BND - 정규분포내 총건수 */
FROM DUAL
)
) A
) A
) A
,(
SELECT ROW_NUMBER() OVER (ORDER BY A.NO) AS NO FROM V1 A, V1 B, V1 C
) B
WHERE B.NO BETWEEN 1 AND A.TCNT
) A
) A
) A
) A
) A
ORDER BY 1
;
'전산Tip > Oracle' 카테고리의 다른 글
| [ORACLE] INSERT 시 APPEND 힌트 사용 주의... (0) | 2010.12.28 |
|---|---|
| 테이블스페이스 변경시 인덱스 리빌드해줘야함.. (3) | 2010.07.26 |
| [ORACLE] Procedure Re-Compile (0) | 2010.06.01 |