꿈꾸는 개발자, DBA 커뮤니티 구루비

누적 합산, 관련 로직 피드백 주시면 감사합니다.

by 가보자고 [Oracle 기초] [2023.11.12 04:59:23]

조건 : ITEM 별로 PRICE 이 큰 순서대로 나열하여,
총액의 80% 를 차지하는 NUM만 관리함. 80% 를 차지하는 NUM의 갯수가 5개 이상이라면 5개까지만 관리함.
ex)만약 ITEM 총액이 10000 일 때 8000을 차지하는 ITEM NUM의 갯수가 5개 라면 그 5개가 최종 관리 대상.
만약 ITEM 총액 8000을 차지하는 ITEMNUM가 8개면 PRICE이 높은 5개만 관리 대상이 됨.

아직 너무 초보라서 로직상 괜찮게 짰는지... 더 좋은 방법이 있는지 조언 부탁드립니다

WITH temp AS
(
 SELECT '1' NUM ,'A' ITEM,   '2000' PRICE FROM dual
UNION ALL SELECT '2' NUM ,'A' ITEM,   '2000' PRICE FROM dual
UNION ALL SELECT '3' NUM ,'A' ITEM,   '1400' PRICE FROM dual
UNION ALL SELECT '4' NUM ,'A' ITEM,   '1000' PRICE FROM dual
UNION ALL SELECT '5' NUM ,'A' ITEM,   '1500' PRICE FROM dual
UNION ALL SELECT '6' NUM ,'A' ITEM,   '1100' PRICE FROM dual
UNION ALL SELECT '7' NUM ,'A' ITEM,   '1300' PRICE FROM dual
UNION ALL SELECT '8' NUM ,'A' ITEM,   '1600' PRICE FROM dual
UNION ALL SELECT '9' NUM ,'A' ITEM,   '2200' PRICE FROM dual
UNION ALL SELECT '10' NUM ,'A' ITEM,  '3600' PRICE FROM dual
UNION ALL SELECT '1' NUM ,'B' ITEM,   '2300' PRICE FROM dual
UNION ALL SELECT '2' NUM ,'B' ITEM,   '3100' PRICE FROM dual
UNION ALL SELECT '3' NUM ,'B' ITEM,   '4400' PRICE FROM dual
UNION ALL SELECT '4' NUM ,'B' ITEM,   '7100' PRICE FROM dual
UNION ALL SELECT '5' NUM ,'B' ITEM,   '2300' PRICE FROM dual
UNION ALL SELECT '6' NUM ,'B' ITEM,   '1100' PRICE FROM dual
UNION ALL SELECT '7' NUM ,'B' ITEM,   '1200' PRICE FROM dual
UNION ALL SELECT '8' NUM ,'B' ITEM,   '3200' PRICE FROM dual
UNION ALL SELECT '9' NUM ,'B' ITEM,   '1400' PRICE FROM dual
UNION ALL SELECT '10' NUM ,'B' ITEM,  '1500' PRICE FROM dual
)
SELECT * 
  FROM (
        SELECT ITEM, PRICE, RNK
             , SUM(PRICE) OVER(PARTITION BY ITEM ORDER BY RNK ASC) RNK2
             , (SUM(PRICE) OVER(PARTITION BY ITEM) * 0.8) AS CR
          FROM (
                 SELECT ITEM, PRICE  
                         , ROW_NUMBER() OVER(PARTITION BY ITEM ORDER BY PRICE DESC) RNK
                      FROM TEMP
               )
      ) 
 WHERE RNK2 <= CR
   AND RNK <= 5

by 마농 [2023.11.13 00:51:38]

1. 샘플 자료는 일괄적으로 따옴표 붙이지 마시고 숫자는 따옴표 빼세요. num, price
2. 랭킹과 누계를 단계별로 할 필요는 없어 보입니다. 한번에 가능
3. price 가 동일한 경우 처리방안을 고민해 보세요. 다양한 고민이 필요합니다.

WITH temp AS
(
SELECT 1 num ,'A' item, 2000 price FROM dual
UNION ALL SELECT  2, 'A', 2000 FROM dual
UNION ALL SELECT  3, 'A', 1400 FROM dual
UNION ALL SELECT  4, 'A', 1000 FROM dual
UNION ALL SELECT  5, 'A', 1500 FROM dual
UNION ALL SELECT  6, 'A', 1100 FROM dual
UNION ALL SELECT  7, 'A', 1300 FROM dual
UNION ALL SELECT  8, 'A', 1600 FROM dual
UNION ALL SELECT  9, 'A', 2200 FROM dual
UNION ALL SELECT 10, 'A', 3600 FROM dual
UNION ALL SELECT  1, 'B', 2300 FROM dual
UNION ALL SELECT  2, 'B', 3100 FROM dual
UNION ALL SELECT  3, 'B', 4400 FROM dual
UNION ALL SELECT  4, 'B', 7100 FROM dual
UNION ALL SELECT  5, 'B', 2300 FROM dual
UNION ALL SELECT  6, 'B', 1100 FROM dual
UNION ALL SELECT  7, 'B', 1200 FROM dual
UNION ALL SELECT  8, 'B', 3200 FROM dual
UNION ALL SELECT  9, 'B', 1400 FROM dual
UNION ALL SELECT 10, 'B', 1500 FROM dual
)
SELECT *
  FROM (SELECT num, item, price
             , ROW_NUMBER() OVER(PARTITION BY item ORDER BY price DESC, num) rn
             , SUM(price) OVER(PARTITION BY item ORDER BY price DESC, num) prc
             , SUM(price) OVER(PARTITION BY item) * 0.8 cr
          FROM temp
        )
 WHERE rn  <= 5
   AND prc <= cr
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변