농부지기

[ Oracle - 금액배분 후 남은 잔여분 재일 큰 거래처에 가감  ]

--문제 : 거래처 구분별로 100,000만원을 각 거래처에 배분하여 인센티브를 지급하려고 한다.
--       단, 배분시 매출액대비 지급율을 계산하여 인센티브를 지급하는데
--       거래처 구분별로 100,000만원이 넘어가거나, 남을 경우  제일 많이 지급되는 거래처에서 가감한다.

WITH SALE_LIST AS ( SELECT '백화점' AS GUBN, '하늘점' AS CUST, '20104' AS YM, 31 AS AMT FROM DUAL UNION ALL SELECT '백화점' , '하늘점' , '20105' , 24 FROM DUAL UNION ALL SELECT '백화점' , '하늘점' , '20106' , 34 FROM DUAL UNION ALL SELECT '백화점' , '누리점' , '20101' , 25 FROM DUAL UNION ALL SELECT '백화점' , '누리점' , '20102' , 48 FROM DUAL UNION ALL SELECT '백화점' , '가리봉점' , '20105' , 34 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20105' , 77 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20106' , 85 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20107' , 79 FROM DUAL UNION ALL SELECT '온라인' , 'GS마켓' , '20101' , 64 FROM DUAL UNION ALL SELECT '온라인' , 'GS마켓' , '20112' , 70 FROM DUAL ) SELECT A.* , ( CASE WHEN RANKING=1 AND 100000 != SUM(INCT_AMT) OVER(PARTITION BY GUBN) THEN INCT_AMT + (100000 - SUM(INCT_AMT) OVER(PARTITION BY GUBN)) ELSE INCT_AMT END ) AS REAL_INCT_AMT , INCT_AMT - ( CASE WHEN RANKING=1 AND 100000 != SUM(INCT_AMT) OVER(PARTITION BY GUBN) THEN INCT_AMT + (100000 - SUM(INCT_AMT) OVER(PARTITION BY GUBN)) ELSE INCT_AMT END ) AS DIFF_AMT FROM ( SELECT GUBN , CUST , SUM(AMT) AS CUST_SUM , SUM(SUM(AMT)) OVER(PARTITION BY GUBN) AS GUBN_SUM , TRUNC(SUM(AMT) / SUM(SUM(AMT)) OVER(PARTITION BY GUBN), 3) AS CUST_RAT , ROW_NUMBER() OVER(PARTITION BY GUBN ORDER BY SUM(AMT) DESC) AS RANKING , ROUND(TRUNC(SUM(AMT) / SUM(SUM(AMT)) OVER(PARTITION BY GUBN), 3) * 100000, 0) AS INCT_AMT FROM SALE_LIST GROUP BY GUBN, CUST ) A ORDER BY GUBN, RANKING ;