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

수정 )오라클 쿼리 도움요청합니다 ㅠ..마농

by 정유석 [2017.06.28 12:11:10]

DATA_a	Daily	EA_A	EA_B	INVEN
png_L_01_DDA	20170501	0	19	Y
png_L_01_DDA	20170501	20	0
png_L_01_DDA	20170502	0	20
png_L_01_DDA	20170503	0	0
png_L_01_DDA	20170504	0	0
png_L_01_DDA	20170505	0	0
png_L_01_DDA	20170506	0	0
png_L_01_DDA	20170507	15	0
png_L_01_DDA	20470508	0	0

원본데이터입니다. 데이터의 컬럼 데이터값은 전부 varchar2(100Byte)입니다

select
data_a,
daily,
ea_a,
ea_b,
inven
from
(
select 'png_L_01_DDA' DATA_a, '20170501' daily, '0' EA_A, '19' EA_B, 'Y' INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170501' daily, '20' EA_A, '0' EA_B, null INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170502' daily, '0' EA_A, '20' EA_B, null INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170503' daily, '0' EA_A, '0' EA_B, null INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170504' daily, '0' EA_A, '0' EA_B, null INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170505' daily, '0' EA_A, '0' EA_B, null INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170506' daily, '0' EA_A, '0' EA_B, null INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170507' daily, '15' EA_A, '0' EA_B, null INVEN from dual union
select 'png_L_01_DDA' DATA_a, '20170508' daily, '0' EA_A, '0' EA_B, null INVEN from dual
)

해당쿼리이구요 아래의 로우데이터는 엑셀로 수기로 작성한것입니다.

DATA_a	Daily	EA_A	EA_B	표시되어야할EA_B	INVEN	차질 및 남은 재고
png_L_01_DDA	20170501	0	19	0	Y	19
png_L_01_DDA	20170501	20	0	19		-1
png_L_01_DDA	20170502	0	20	1		19
png_L_01_DDA	20170503	0	0	0		19
png_L_01_DDA	20170504	0	0	0		19
png_L_01_DDA	20170505	0	0	0		19
png_L_01_DDA	20170506	0	0	0		19
png_L_01_DDA	20170507	15	0	15		4
png_L_01_DDA	20470508	0	0	0		4

아래의 데이터처럼 나와야하는데.. 잘 되지가않습니다

DATA_a	Daily	EA_A	EA_B
png_L_01_DDA	20170501	20	19
png_L_01_DDA	20170502	0	1
png_L_01_DDA	20170503	0	0
png_L_01_DDA	20170504	0	0
png_L_01_DDA	20170505	0	0
png_L_01_DDA	20170506	0	0
png_L_01_DDA	20170507	15	15
png_L_01_DDA	20470508	0	0

by 마농 [2017.06.28 13:16:17]

자료에 대한 설명이 전혀 없네요. 왜 그렇게 나와야 하는지 설명해 주세요.
INVEN 항목의 역할이 있는건지 의문이구요?
올려주신 쿼리는 엉망이구요.(컬럼명 등이 전혀 맞질 안네요.)
올려주신 결과표 두개중 어느것이 필요한건지도 불분명하구요?

by 정유석 [2017.06.28 13:20:55]

내용 수정하였습니다.

설명은 EA_A는 주문이고 EA_B에 INVEN 'Y' 면 월초재고이며, null값인 EA_B는 해당일의 생산된제품입니다

해당일에 주문을 생산을 맞추느냐 확인하려고 합니다 그러고 차질된 제품도 확인하려 하고요

5/1 주문은 20인데 5/1에 생산된 제품은 없지만 재고가 19개 존재하므로

5/1은 19개의 주문만 수락되어 차질이 -1개가 발생하였습니다

5/2에는 주문은 없지만 생산된 제품이 20개가 있습니다

하지만 5/1에 차질이 -1이 있으므로 생산된 제품 20개로 확보가 되기때문에 5/2 남은 재고는 19개가 되며

EA_A 는 실질적으로 0이지만 EA_B에서는 1을 보여주어야합니다

INVEN 이란 항목은

5월초 즉 5월1일의 재고입니다.

아래 두개의 표 중 젤 아래것으로 결과물이 나왔으면합니다

중간에있는표는 저런식으로 계산이되어 표시되어야할 EA_B 라는항목이 젤 아래 EA_B로 보여져야합니다

by 마농 [2017.06.28 13:35:19]

WITH low_data_c_table_cs AS
(
SELECT 'png_L_01_DDA' data_a, '20170501' daily, 0 ea_a, 19 ea_b, 'Y' inven FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170501', 20,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170502',  0, 20, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170503',  0,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170504',  0,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170505',  0,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170506',  0,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170507', 15,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170508',  0,  0, '' FROM dual
)
SELECT data_a, daily
     , ea_a                                         -- 주문
     , ea_b                                         -- 생산(재고포함)
     , LEAST(a, b) - LEAST(a - ea_a, b - ea_b) ea_c -- 출고
     , b - LEAST(a, b) ea_d                         -- 재고
  FROM (SELECT data_a, daily
             , SUM(ea_a) ea_a
             , SUM(ea_b) ea_b
             , SUM(SUM(ea_a)) OVER(PARTITION BY data_a, SUBSTR(daily, 1, 6) ORDER BY daily) a
             , SUM(SUM(ea_b)) OVER(PARTITION BY data_a, SUBSTR(daily, 1, 6) ORDER BY daily) b
          FROM low_data_c_table_cs
         WHERE data_a ='png_L_01_DDA'
           AND daily BETWEEN '20170501' AND '20170508'
         GROUP BY data_a, daily
        )
 ORDER BY data_a, daily
;

by 마농 [2017.06.28 14:05:50]

WITH low_data_c_table_cs AS
(
SELECT 'png_L_01_DDA' data_a, '20170501' daily, 0 ea_a, 0 ea_b, 'Y' inven FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170501', 10,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170502', 10, 10, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170503',  0,  6, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170504',  0,  3, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170505',  0,  6, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170506',  0, 12, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170507', 10,  0, '' FROM dual
UNION ALL SELECT 'png_L_01_DDA', '20170508', 10,  0, '' FROM dual
)
SELECT data_a, daily
     , ea_a                                         -- 주문
     , ea_b                                         -- 생산(재고포함)
     , LEAST(a, b) - LEAST(a - ea_a, b - ea_b) ea_c -- 출고
     , b - LEAST(a, b) ea_d                         -- 재고
     , b - a ea_e                                   -- 재고 및 차질
  FROM (SELECT data_a, daily
             , SUM(ea_a) ea_a
             , SUM(ea_b) ea_b
             , SUM(SUM(ea_a)) OVER(PARTITION BY data_a, SUBSTR(daily, 1, 6) ORDER BY daily) a
             , SUM(SUM(ea_b)) OVER(PARTITION BY data_a, SUBSTR(daily, 1, 6) ORDER BY daily) b
          FROM low_data_c_table_cs
         WHERE data_a ='png_L_01_DDA'
           AND daily BETWEEN '20170501' AND '20170508'
         GROUP BY data_a, daily
        )
 ORDER BY data_a, daily
;

by 정유석 [2017.06.28 14:08:37]

정말 감사합니다

by 가을에사랑 [2017.06.28 17:06:34]

질문있습니다.

지난 번 조언 주신것이 생각나서 올라온 쿼리들을 보면서 분석하곤 하는데요

왜 LEAST함수가 사용되나 싶어서 보다 보니 이해가 되었습니다.

그런데 ^^;; 보통 LEAST함수를 이용해서 재고, 출고를 구하는것이다 라고 이해를 하면 되나요?

아니면 이 경우이기 때문에 LEAST를 이용하게 되는건가요?

by 마농 [2017.06.28 17:17:25]

LEAST 를 써야만 하는 것도 아니고,
LEAST 만 사용한다고 모든게 다 해결될 일도 아니죠.
LEAST 외에 분석함수도 사용했고, 인라인뷰도 사용했고, 마이너스 연산도 사용했죠.
이럴 땐 이걸 반드시 써야 한다는 그런 규칙은 없습니다.
가장 적당한 것들을 골라서, 가장 적당한 조합을 구성하는 거죠.
- 적재적소(適材適所) : 어떤 일에 적당(適當)한 재능(才能)을 가진 자에게 적합(適合)한 지위(地位)나 임무(任務)를 맡김

by 가을에사랑 [2017.06.28 17:30:07]

넵.. ^^;; 맞습니다.
그런데 설마... 무조건 그렇게 될거라고 생각해서 여쭤본건 아니구요
빈도가 높은가 보다 싶어서 여쭤봤습니다.

항상 감사합니다.

댓글등록

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

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

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

데이터베이스 질문과 답변