데이터 분포에 따라 번호 배정하기

현업 중 분석처리 서버를 지정해 줘야 하는 경우를 위해 산수를 이용해 봤습니다.
정답이라 말할 순 없습니다.

db call을 줄이는 방향과 시료수가 극단적으로 치우침이 발생한 경우등도 고려해야겠지만,그건 능력밖~그런다고 누가 아나? 정신승리뿐~

( 앞쪽 번호부터 큐형태로 채우는 경우는 큐가 비었다는 걸 알수 있는 subquery가 필요하겠네요,각 큐의 크기는?스케줄링을 통해 업데이트하라는데... )

 

WITH T1 ( SEQ,W_OBS ) AS
(
    SELECT 1,25 FROM DUAL
    UNION ALL
    SELECT 2,28 FROM DUAL
    UNION ALL
    SELECT 3,30 FROM DUAL
    UNION ALL
    SELECT 4,40 FROM DUAL
    UNION ALL
    SELECT 5,100 FROM DUAL
    UNION ALL
    SELECT 6,55 FROM DUAL
    UNION ALL
    SELECT 7,83 FROM DUAL
    UNION ALL
    SELECT 8,75 FROM DUAL
    UNION ALL
    SELECT 9,175 FROM DUAL
    UNION ALL
    SELECT 10,575 FROM DUAL
    UNION ALL
    SELECT 11,10240 FROM DUAL
)
SELECT
      SEQ
    , W_OBS
    , SUM(W_OBS) OVER(ORDER BY W_OBS ASC) AS W_SUM
    , ROUND(CUME_DIST() OVER(ORDER BY W_OBS,SEQ ASC),3)*100 AS W_CUM
    , DECODE(FLOOR(ROUND(CUME_DIST() OVER(ORDER BY W_OBS,SEQ ASC)/0.2)),0,1,FLOOR(ROUND(CUME_DIST() OVER(ORDER BY W_OBS,SEQ ASC)/0.2))) SERVERSEQ
    , CEIL(SUM(W_OBS) OVER(ORDER BY W_OBS,SEQ)/CEIL(SUM(W_OBS) OVER()/5))  AS SERVERSEQ1
    , CEIL(SUM(W_OBS) OVER(ORDER BY W_OBS,SEQ)/CEIL(SUM(W_OBS) OVER()/DECODE(SIGN(5-COUNT(*) OVER()),-1,5,COUNT(*) OVER())))  AS SERVERSEQ2
    , NTILE(5) OVER(ORDER BY W_OBS,SEQ)  AS SERVERSEQ3 
    , CASE WHEN (MAX(W_OBS) OVER()-MIN(W_OBS) OVER() ) > (STDDEV_POP(W_OBS) OVER()+AVG(W_OBS) OVER()) THEN
        NTILE(5) OVER(ORDER BY W_OBS,SEQ)
      ELSE
        CEIL(SUM(W_OBS) OVER(ORDER BY W_OBS,SEQ)/CEIL(SUM(W_OBS) OVER()/DECODE(SIGN(5-COUNT(*) OVER()),-1,5,COUNT(*) OVER())))
      END AS SERVERSEQ4
    , TO_CHAR( ( 3*(AVG(W_OBS) OVER()-MEDIAN(W_OBS) OVER()) ) /STDDEV(W_OBS) OVER(), '9,999,999,990.990') AS K1
    , TO_CHAR( VAR_POP(W_OBS) OVER() ,'9,999,999,990.990') AS VAR
    , TO_CHAR( STDDEV_POP(W_OBS) OVER() ,'9,999,999,990.990') AS STD_DEV
    , TO_CHAR( ((MAX(W_OBS) OVER()+MIN(W_OBS) OVER())/2  )- (AVG(W_OBS) OVER()/((MAX(W_OBS) OVER()-MIN(W_OBS) OVER())/2)) ,'9,999,999,999.990') AS K2
    , MAX(W_OBS) OVER()-MIN(W_OBS) OVER() AS "RANGE"
    , TO_CHAR( AVG(W_OBS) OVER() ,'9,999,999,990.990') AS "AVG"
    , TO_CHAR( (STDDEV_POP(W_OBS) OVER()+AVG(W_OBS) OVER()) ,'9,999,999,990.990') R1
    --, TO_CHAR( 1/ ( STDDEV_POP(W_OBS) OVER()*SQRT(2*ACOS(-1)) ) ,'9,999,999,990.990')
    , TO_CHAR( 1/ ( STDDEV_POP(W_OBS) OVER()*SQRT(2*ACOS(-1)) )*EXP( -POWER(W_OBS-AVG(W_OBS) OVER(),2) /(2*POWER(STDDEV_POP(W_OBS) OVER(),2) )) ,'990.9999999990') AS STD_RGL
FROM T1;