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

오라클 기본수량(예치수량) 차감식

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(

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

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

데이터베이스 질문과 답변