오라클 기본수량(예치수량) 차감식 0 8 5,878

by 신이만든지기 [SQL Query] oracle 11g [2019.11.07 14:39:58]


안녕하세요. 

아래과 같은 원본테이블에서 결과 테이블을 만들고자 합니다. (ORACLE 11G)

고수분들의 도움 기다립니다.

하단의 쿼리는 제가 실패한 쿼리입니다. ㅠ_ㅠ

[원본 테이블]

PRODUCT COMP_ID SALES_CNT BASE_CNT
1 11111 225 400
1 22222 21 400
1 33333 14 400
1 44444 63 400
2 11111 640 1000
2 22222 700 1000
2 33333 741 1000
2 44444 921 1000
3 11111 100 100
3 22222 250 100
3 33333 50 100
3 44444 30 100

 

[결과 테이블]

PRODUCT COMP_ID SALES_CNT BASE_CNT RESULT
1 11111 225 400 225
1 22222 21 400 21
1 33333 14 400 14
1 44444 63 400 63
2 11111 640 1000 640
2 22222 700 1000 360
2 33333 741 1000 0
2 44444 921 1000 0
3 11111 100 100 100
3 22222 250 100 0
3 33333 50 100 0
3 44444 30 100 0

 

테이블을 각 컬럼을 설명하자면,

PRODUCT : 제품번호

COMP_ID : 회사번호

SALES_CNT : 판매건수(음수는 나오지 않음)

BASE_CNT : 기본수량(예치수량)

RESULT : 각 제품의 기본수량(예치수량)에서 판매건수를 차감하여 기본수량이 0이 되는 시점까지 값을 출력, 단, 판매건수를 초과할 수는 없음, 0을 못만들면 거기까지만 출력

 

[원본테이블 쿼리]

WITH
    T AS
        (SELECT 1 PRODUCT, 11111 COMP_ID, 225 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 22222 COMP_ID, 21 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 33333 COMP_ID, 14 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 44444 COMP_ID, 63 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 11111 COMP_ID, 640 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 22222 COMP_ID, 700 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 33333 COMP_ID, 741 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 44444 COMP_ID, 921 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 11111 COMP_ID, 100 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 22222 COMP_ID, 250 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 33333 COMP_ID, 50 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 44444 COMP_ID, 30 SALES_CNT, 100 BASE_CNT FROM DUAL)
  SELECT PRODUCT
       , COMP_ID
       , SALES_CNT
       , BASE_CNT   
  FROM T
ORDER BY PRODUCT, COMP_ID;

 

[실패한 쿼리]

WITH
    T AS
        (SELECT 1 PRODUCT, 11111 COMP_ID, 225 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 22222 COMP_ID, 21 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 33333 COMP_ID, 14 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 44444 COMP_ID, 63 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 11111 COMP_ID, 640 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 22222 COMP_ID, 700 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 33333 COMP_ID, 741 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 44444 COMP_ID, 921 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 11111 COMP_ID, 100 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 22222 COMP_ID, 250 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 33333 COMP_ID, 50 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 44444 COMP_ID, 30 SALES_CNT, 100 BASE_CNT FROM DUAL)
  SELECT PRODUCT
       , COMP_ID
       , SALES_CNT
       , BASE_CNT       
       , CASE WHEN BASE_CNT > SUM (SALES_CNT) OVER(PARTITION BY PRODUCT ORDER BY COMP_ID) THEN SALES_CNT
              ELSE GREATEST(0, SALES_CNT + SUM(BASE_CNT - SALES_CNT) OVER(PARTITION BY PRODUCT ORDER BY COMP_ID ))
         END RESULT
    FROM (
          SELECT PRODUCT, COMP_ID, SALES_CNT
               , DECODE(ROW_NUMBER() OVER(PARTITION BY PRODUCT ORDER BY COMP_ID), 1, BASE_CNT, 0) BASE_CNT
             FROM T
         )

 

by 마농 [2019.11.07 14:55:46]

원본 테아블 설계가 좀 잘못 된게 아닌지?
 - 정규화 : base_cnt 는 별도 테이블로 분리되어야 할 것 같구요.
 - 순서 : 회사번호 가 아닌 판매일자와 같은 순서 항목이 필요할 듯 한데요?

SELECT product, comp_id, sales_cnt, base_cnt
     , CASE WHEN base_cnt >= s             THEN sales_cnt
            WHEN base_cnt <= s - sales_cnt THEN 0
            ELSE base_cnt - (s - sales_cnt)
        END result
  FROM (SELECT product, comp_id, sales_cnt, base_cnt
             , SUM(sales_cnt) OVER(PARTITION BY product ORDER BY comp_id) s
          FROM t
        )
;

 


by 신이만든지기 [2019.11.07 15:09:32]

네.

- BASE_CNT는 별도 테이블에 있습니다, 원본테이블도 실제는 서브쿼리를 통해 조인하여 가져온 결과입니다.

- 해당 데이터는 모두 같은 판매년월(YYYYMM)을 가지고 있습니다. 월정산이라 일자(DAY)는 계산하지 않습니다.

- 정렬을 위해 회사번호를 이용하였으나, 사실 회사번호는 중요하지 않습니다. 이 결과 테이블은 제품별(앨범별, 서비스 별 등)로 통계화면을 제공하기 위한 중간정산 테이블입니다.(회사별로는 통계를 제공하지 않습니다.)

 

사실 다른 사람이 만들어둔 프로시저를 수정하는 작업중인데요.

프로시저를 새로 다시짜는 것은 금액불일치 같은 정산 위험이 있어서, 가능한 적은부분만 수정(예치수량 계산)하려고 꼼수를 쓰는 중입니다. ^^;


by 신이만든지기 [2019.11.07 15:22:39]

저도 질문을 올리고 나서 아래와 같이 답을 찾았습니다.

아무래도 구루비에는 좋은 기운이 있나봅니다. ^^;

WITH
    T AS
        (SELECT 1 PRODUCT, 11111 COMP_ID, 225 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 22222 COMP_ID, 21 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 33333 COMP_ID, 14 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 1 PRODUCT, 44444 COMP_ID, 63 SALES_CNT, 400 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 11111 COMP_ID, 640 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 22222 COMP_ID, 700 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 33333 COMP_ID, 741 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 2 PRODUCT, 44444 COMP_ID, 921 SALES_CNT, 1000 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 11111 COMP_ID, 100 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 22222 COMP_ID, 250 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 33333 COMP_ID, 50 SALES_CNT, 100 BASE_CNT FROM DUAL
         UNION ALL
         SELECT 3 PRODUCT, 44444 COMP_ID, 30 SALES_CNT, 100 BASE_CNT FROM DUAL)
  SELECT PRODUCT
       , COMP_ID
       , SALES_CNT
       , BASE_CNT
       , CASE WHEN BASE_CNT > SUM (SALES_CNT) OVER(PARTITION BY PRODUCT ORDER BY COMP_ID) THEN SALES_CNT
              WHEN SALES_CNT < SUM(BASE_CNT - SALES_CNT) OVER(PARTITION BY PRODUCT ORDER BY COMP_ID ) THEN SALES_CNT
              ELSE GREATEST(0, SALES_CNT + SUM(BASE_CNT - SALES_CNT) OVER(PARTITION BY PRODUCT ORDER BY COMP_ID ))
         END RESULT
    FROM (
          SELECT PRODUCT, COMP_ID, SALES_CNT
               , DECODE(ROW_NUMBER() OVER(PARTITION BY PRODUCT ORDER BY COMP_ID), 1, BASE_CNT, 0) BASE_CNT
             FROM T
         )

 


by jkson [2019.11.07 15:10:55]
WITH T(PRODUCT,	COMP_ID,	SALES_CNT,	BASE_CNT) AS
(
SELECT '1',	'11111',	225	,400 FROM DUAL UNION ALL
SELECT '1',	'22222',	21	,400 FROM DUAL UNION ALL
SELECT '1',	'33333',	14	,400 FROM DUAL UNION ALL
SELECT '1',	'44444',	63	,400 FROM DUAL UNION ALL
SELECT '2',	'11111',	640	,1000 FROM DUAL UNION ALL
SELECT '2',	'22222',	700	,1000 FROM DUAL UNION ALL
SELECT '2',	'33333',	741	,1000 FROM DUAL UNION ALL
SELECT '2',	'44444',	921	,1000 FROM DUAL UNION ALL
SELECT '3',	'11111',	100	,100 FROM DUAL UNION ALL
SELECT '3',	'22222',	250	,100 FROM DUAL UNION ALL
SELECT '3',	'33333',	50	,100 FROM DUAL UNION ALL
SELECT '3',	'44444',	30	,100 FROM DUAL
)
SELECT PRODUCT, COMP_ID, SALES_CNT, BASE_CNT
     , GREATEST(CASE WHEN SUMCNT >= BASE_CNT THEN BASE_CNT - (SUMCNT - SALES_CNT)
                     ELSE SALES_CNT
                END,0) SUMCNT
  FROM
  (
  SELECT PRODUCT, COMP_ID, SALES_CNT, BASE_CNT 
       , SUM(SALES_CNT) OVER(PARTITION BY PRODUCT ORDER BY ROWNUM) SUMCNT --단순히 나열된 순서대로 판매량을 누적한다고 본다.--누적 기준에 따라 ORDER BY 구현
  FROM T
   )

 


by 신이만든지기 [2019.11.07 15:20:08]

아~ 아쉽게도 제품1번의 결과행이 판매건수를 초과하여 기대하는 결과는 아닙니다.

답변 주심에 진심으로 감사드립니다.

 


by jkson [2019.11.07 15:21:48]

수정했습니다. ELSE 다음을 SALES_CNT로 했어야 했는데 SUMCNT로 했었네요. PC에서 쿼리를 못 만들다보니;;;


by 신이만든지기 [2019.11.07 15:23:57]

오오~ 이렇게나 빨리 답을 달아주시다니 새삼 고수가 많다고 느낍니다. 

답변 감사합니다!!


by jkson [2019.11.07 15:25:27]

망분리 땜시롱 답변달기 힘드네요 ㅋㅋ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입