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

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

1. 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 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자리 반올림) ROUND(RATIO_TO_REPORT(T.Amt) OVER (PARTITION BY T.Grp),10) Amt_Rate_Grp_Round, -- Grp별 Amt 비율별 정해진 수치 배정(여기서 정해진 수치는 예로 5,000,000 임) 5000000.0 * RATIO_TO_REPORT(T.Amt) OVER (PARTITION BY T.Grp) Amt_Rate_Grp_Asign FROM TEMP_DATA T

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