[Oracle] 내 값의 전체Sum 대비 비율(RATIO_TO

프로그래밍/Oracle

[Oracle] 내 값의 전체Sum 대비 비율(RATIO_TO_REPORT)

도깨비방망이 2018. 11. 2. 13:57

[Oracle] 내 값의 전체Sum 대비 비율(RATIO_TO_REPORT)

1. Group별 Sum(값)의 내 값이 차지하는 비율 및 정해진 값 비율로 할당

- 적용 예제 : 정해진 매출 대비 보너스 매출 비율로 할당

2. Group별 Sum(값)의 내 값이 차지하는 비율 및 정해진 값 비율로 할당시 할당된 값이 정확히 안떨어질때

나머지 값은 최고 수치에 할당

- 적용 예제 : 정해진 매출 대비 보너스 매출 비율로 할당하되 비율로 할당시 단 단위 남거나 모자르면 그룹별 가장 큰 매출자에게 할당

( 모든 보너스 다 소진 )

WITH TEMP_DATA AS (
    SELECT 'G1' Grp, 'A' Id, 1000 Amt FROM DUAL
    UNION ALL
    SELECT 'G1' Grp,'B' Id, 3000 Amt FROM DUAL
    UNION ALL
    SELECT 'G1' Grp,'C' Id, 5000 Amt FROM DUAL
    UNION ALL
    SELECT 'G1' Grp,'D' Id, 2000 Amt FROM DUAL
    UNION ALL
    SELECT 'G2' Grp,'E' Id, 10000 Amt FROM DUAL    
)
 SELECT     Z.RNUM,Z.Grp, Z.Id,Z.Amt,Z.Amt_Rate,Z.Amt_Rate_Grp,
            -- Grp별 Amt 비율(10자리 반올림)
            ROUND(Z.Amt_Rate_Grp,10) Amt_Rate_Grp_Round,
            -- Grp별 Amt 비율별 정해진 수치 배정(여기서 정해진 수치는 예로 5,000,000 임)
            5000000.0 * Z.Amt_Rate_Grp Amt_Rate_Grp_Asign,
            -- 나머지 Grp 가장 높은 사람에게 할당
            CASE 
            WHEN RNUM = 1 THEN 5000000.0 - NVL(SUM(CASE WHEN RNUM > 1 THEN ROUND(5000000.0 * Z.Amt_Rate_Grp,3) END) OVER(PARTITION BY Grp),0)
            ELSE ROUND(5000000.0 * Z.Amt_Rate_Grp,3)
            END Amt_Rate_Grp_Asign_Real
FROM    (  
    SELECT      ROW_NUMBER() OVER(PARTITION BY T.Grp ORDER BY T.Amt DESC) RNUM,
                T.Grp, T.Id, T.Amt, 
                -- 모든 데이터의 Amt 비율
                RATIO_TO_REPORT(T.Amt) OVER () Amt_Rate, 
                -- Grp별 Amt 비율
                RATIO_TO_REPORT(T.Amt) OVER (PARTITION BY T.Grp) Amt_Rate_Grp 
                -- Grp별 Amt 비율(10자리 반올림)
    FROM        TEMP_DATA T
) Z

저작자표시 비영리 변경금지