데이터베이스 질문과 답변
로우데이타의 현재재고 합산 방법을 어떻게 해야하는지..ㅠㅠ
0
4
4,501
by 이카루스 [SQL Query] LAG LEAD oracle [2020.01.30 17:35:46]
안녕하세요 매일 현재재고 합계를 위해 LAG, LEAD 함수를 써도 신통치 않아 염치없이 도움을 요청합니다.. ㅠㅠ -- 소스 --- with t1 as ( select 'aaaa1111' as 상품코드, '20191225' as 일자, 0 as 현재재고, 0 as 입고, 0 as 출고 from dual union all select 'aaaa1111' , '20191226' , 0 , 10, 0 from dual union all select 'aaaa1111' , '20191227' , 0 , 30, 10 from dual union all select 'aaaa1111' , '20191228' , 0 , 0, 10 from dual union all select 'aaaa1111' , '20191229' , 0 , 0, 10 from dual union all select 'aaaa1111' , '20191230' , 0 , 0, 10 from dual union all select 'aaaa1111' , '20191231' , 0 , 5, 0 from dual union all select 'aaaa1111' , '20200101' , 0 , 5, 0 from dual union all select 'aaaa1111' , '20200102' , 0 , 5, 0 from dual union all select 'aaaa1111' , '20200103' , 0 , 5, 5 from dual union all select 'aaaa1111' , '20200103' , 0 , 0, 5 from dual ) select * from t1; ---타켓 / 현재재고합산 결과 ---- with t2 as ( select 'aaaa1111' as 상품코드, '20191225' as 일자, 0 as 현재재고, 0 as 입고, 0 as 출고 from dual union all select 'aaaa1111' , '20191226' , 10 , 10, 0 from dual union all select 'aaaa1111' , '20191227' , 30 , 30, 10 from dual union all select 'aaaa1111' , '20191228' , 20 , 0, 10 from dual union all select 'aaaa1111' , '20191229' , 10 , 0, 10 from dual union all select 'aaaa1111' , '20191230' , 0 , 0, 10 from dual union all select 'aaaa1111' , '20191231' , 5 , 5, 0 from dual union all select 'aaaa1111' , '20200101' , 10 , 5, 0 from dual union all select 'aaaa1111' , '20200102' , 15 , 5, 0 from dual union all select 'aaaa1111' , '20200103' , 15 , 5, 5 from dual union all select 'aaaa1111' , '20200103' , 10 , 0, 5 from dual ) select * from t2;
by 마농
[2020.01.30 17:54:34]
분석함수는 순차적으로 실행되는게 아니라 한번에 실행됩니다.
따라서 바로 이전값만 참조하는 LAG 가 아닌 SUM 함수를 이용해 보세요.
by 이카루스
[2020.01.31 16:39:51]
넵 말씀대로 기억났습니다 감사합니다!!
by jkson
[2020.01.31 07:40:58]
20100103이 두 개인데 일자별 순번 컬럼은 없나요?
with t1 as ( select 'aaaa1111' as 상품코드, '20191225' as 일자, 0 as 현재재고, 0 as 입고, 0 as 출고 from dual union all select 'aaaa1111' , '20191226' , 0 , 10, 0 from dual union all select 'aaaa1111' , '20191227' , 0 , 30, 10 from dual union all select 'aaaa1111' , '20191228' , 0 , 0, 10 from dual union all select 'aaaa1111' , '20191229' , 0 , 0, 10 from dual union all select 'aaaa1111' , '20191230' , 0 , 0, 10 from dual union all select 'aaaa1111' , '20191231' , 0 , 5, 0 from dual union all select 'aaaa1111' , '20200101' , 0 , 5, 0 from dual union all select 'aaaa1111' , '20200102' , 0 , 5, 0 from dual union all select 'aaaa1111' , '20200103' , 0 , 5, 5 from dual union all select 'aaaa1111' , '20200103' , 0 , 0, 5 from dual ) select 상품코드 , 일자 , sum(입고) OVER(PARTITION BY 상품코드 ORDER BY 일자, rownum) - sum(출고) OVER(PARTITION BY 상품코드 ORDER BY 일자, rownum) 현재재고--일자별순번컬럼 없어서 rownum으로 대체 , 입고 , 출고 from t1
by 이카루스
[2020.01.31 16:40:09]
넵 말씀대로 해결했습니다 감사합니다!!!
) 버튼을 클릭하여 작성 하시면 됩니다.