database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41194 게시물 읽기
No. 41194
실용성 있는 sql 퀴즈입니다.
작성자
김흥수(protokhs)
작성일
2016-07-07 16:12ⓒ
2016-07-07 16:17ⓜ
조회수
10,539

 선입선출법을 sql로 풀어보기

 
이것도 역시 실전에 유용한 sql 퀴즈입니다.
 
재고자산의 원가 계산방법 중 하나.
 
재고자산이 출고될 때 장부상 먼저 입고된 것으로 되어있는 상품부터 출고된 것으로 간주하는 방법, 
한마디로 먼저 들어온 물건이 먼저 팔린 것으로 치는 방법이다. 
 
예를 들어 다음과 같은 입고가 있는 경우
1 상품 입고 100개 단가 300원
2 상품 입고 120개 단가 320원
 
만약 상품이 50개 출고가 되는 경우 50 * 300 = 1500 의 출고가가 결정이 되며
추가로 상품이 100개가 출고가 되는 경우 50 * 300 + 50 * 320 = 31000 의 출고가가 결정이 된다.
 
다음과 같은 데이타가 주어져 있다고 할 때
with base_table as
(
select '1' 상품코드 , 1 순번 , '입고' 구분 , 1000 수량 , 100 단가 from dual
union all select '1' 상품코드 , 2 순번 , '출고' 구분 , 800 수량 , null 단가 from dual
union all select '1' 상품코드 , 3 순번 , '입고' 구분 , 200 수량 , 95 단가 from dual
union all select '1' 상품코드 , 4 순번 , '입고' 구분 , 1900 수량 , 120 단가 from dual
union all select '1' 상품코드 , 5 순번 , '출고' 구분 , 800 수량 , null 단가 from dual
union all select '1' 상품코드 , 6 순번 , '입고' 구분 , 300 수량 , 130 단가 from dual
union all select '1' 상품코드 , 7 순번 , '출고' 구분 , 600 수량 , null 단가 from dual
union all select '1' 상품코드 , 8 순번 , '입고' 구분 , 400 수량 , 140 단가 from dual
union all select '1' 상품코드 , 9 순번 , '입고' 구분 , 300 수량 , 130 단가 from dual
union all select '1' 상품코드 ,10 순번 , '출고' 구분 , 1000 수량 , null 단가 from dual
union all select '1' 상품코드 ,11 순번 , '출고' 구분 , 800 수량 , null 단가 from dual
)
 
각각의 출고에 대하여 출고 금액을 계산하는 sql을 만드시면 됩니다.
순번, 출고단가,출고금액
2 , 100, 80000
5 , 108.75, 87000
7 , 120,72000
10, 121,121000
11, 134.4444, 121000
 
 
이 글에 대한 댓글이 총 5건 있습니다.

WITH base_table AS
(
SELECT '1' 상품코드, 1 순번, '입고' 구분, 1000 수량, 100 단가 FROM dual
UNION ALL SELECT '1',  2, '출고',  800, null FROM dual
UNION ALL SELECT '1',  3, '입고',  200,   95 FROM dual
UNION ALL SELECT '1',  4, '입고', 1900,  120 FROM dual
UNION ALL SELECT '1',  5, '출고',  800, null FROM dual
UNION ALL SELECT '1',  6, '입고',  300,  130 FROM dual
UNION ALL SELECT '1',  7, '출고',  600, null FROM dual
UNION ALL SELECT '1',  8, '입고',  400,  140 FROM dual
UNION ALL SELECT '1',  9, '입고',  300,  130 FROM dual
UNION ALL SELECT '1', 10, '출고', 1000, null FROM dual
UNION ALL SELECT '1', 11, '출고',  900, null FROM dual
)
, tmp AS
(
SELECT 상품코드, 순번, 구분, 수량, 단가
     , SUM(수량) OVER(PARTITION BY 상품코드, 구분 ORDER BY 순번) 누적수량
  FROM base_table
)
SELECT 상품코드
     , 순번
     , 요청수량
     , SUM(출고수량) 출고수량
     , ROUND(SUM(출고수량 * 출고단가) / SUM(출고수량), 2) 출고단가
     , SUM(출고수량 * 출고단가) AS 출고금액
  FROM (SELECT b.상품코드
             , b.순번
             , b.수량 요청수량
             , a.단가 출고단가
             , LEAST( a.수량
                    , b.수량
                    , b.누적수량 - (a.누적수량 - a.수량)
                    , a.누적수량 - (b.누적수량 - b.수량)
                    ) AS 출고수량
          FROM tmp a
             , tmp b
         WHERE a.구분 = '입고'
           AND b.구분 = '출고'
           AND a.상품코드 = b.상품코드
           AND a.누적수량 - a.수량 < b.누적수량
           AND b.누적수량 - b.수량 < a.누적수량
        )
 GROUP BY 상품코드, 순번, 요청수량
 ORDER BY 상품코드, 순번
;

마농(manon94)님이 2016-07-08 09:28에 작성한 댓글입니다.
이 댓글은 2016-07-08 10:59에 마지막으로 수정되었습니다.

 마농님 질문있는데요 원래 아시는 문제였나요 아니면 이번에 푸신건가요?

김흥수(protokhs)님이 2016-07-08 16:32에 작성한 댓글입니다.

예전에 고민해서 풀었던 문제고요.

내용 정리해서 SQL퀴즈로 컬럼에 기고했던 내용이네요 ^^

http://www.gurubee.net/lecture/2837

마농(manon94)님이 2016-07-08 16:48에 작성한 댓글입니다.

 핸폰이라  제가 푼건 집에가서 올니겠습니다

근데 전 이렇게 쉬운 방법으로 풀릴지 몰랐습니다

정말 훌륭한 풀이입니다

아마 제가 접근한 방식과 비교하면 이 방법이 얼마나 멋있는 방법인지를 알 수 있을것입니다 감사합니다

김흥수(protokhs)님이 2016-07-08 17:25에 작성한 댓글입니다.

 제가 푼 방법은 아래의 sql입니다.

엄청복잡합니다.
 
선입선출을 신입사원때 sp로 작성했던 것을 이번에 sql로 해보았습니다.
선입선출이 기본적으로 merge-sort와 같은 알고리즘입니다.
merge-sort는 재귀를 내포하구요..
 
그래서 recursive-with로 푼것인데...
마농님의 sql을 보고 제가 너무 틀에 박혀있었구나 하고 깨달았습니다.
마농님의 sql도 기본적으로 merge-sort와 같은 아이디어입니다.
least를 사용한 부분이 압권인데요...교집합의 양쪽 경계부분과 입고수량이 적은 경우 출고수량이 적은 경우 딱 4가지밖에 없다는 점에 착안한 훌륭한 풀이를 보고
아..
난 왜... 재귀구조라고 속단하고 일반 집합적 sql로 시도하는 것을 포기했나.. 하고 반성했습니다.
감사합니다.
 
with base_table as
(
SELECT '1' 상품코드, 1 순번, '입고' 구분, 1000 수량, 100 단가 FROM dual
UNION ALL SELECT '1',  2, '출고',  800, null FROM dual
UNION ALL SELECT '1',  3, '입고',  200,   95 FROM dual
UNION ALL SELECT '1',  4, '입고', 1900,  120 FROM dual
UNION ALL SELECT '1',  5, '출고',  800, null FROM dual
UNION ALL SELECT '1',  6, '입고',  300,  130 FROM dual
UNION ALL SELECT '1',  7, '출고',  600, null FROM dual
UNION ALL SELECT '1',  8, '입고',  400,  140 FROM dual
UNION ALL SELECT '1',  9, '입고',  300,  130 FROM dual
UNION ALL SELECT '1', 10, '출고', 1000, null FROM dual
UNION ALL SELECT '1', 11, '출고',  900, null FROM dual
), 입출고자료 as
(
    select
        a.상품코드
        ,a.순번
        ,a.구분
        ,row_number() over ( partition by a.상품코드, a.구분 order by a.상품코드,a.구분,a.순번) 입출고번호
        ,a.수량 입출고수량
        ,a.단가 
    from    base_table a
)
, 입고자료 as
(
 select
    a.상품코드
    , a.순번 입고key
    , a.입출고번호 입고번호
    , a.입출고수량 입고수량
    , a.단가 입고단가
 from 입출고자료 a
 where   a.구분 = '입고'
)
, 출고자료 as
(
 select
    a.상품코드
    , a.순번 출고key
    , a.입출고번호 출고번호
    , a.입출고수량 출고수량
    , a.단가 입출고단가
 from 입출고자료 a
 where   a.구분 = '출고'
)
, base_recur ( 상품코드, 집행kye,출고key , 현재입고번호, 현재출고번호 ,다음입고번호, 다음출고번호 ,입고번호변경,출고번호변경
            , 입고수량, 출고수량_원수량,출고수량_차감수량,입고잔량, 출고잔량,입고단가,cnt) as
(
    select
        a.상품코드
        , a.입고key
        , b.출고key
        , a.입고번호 현재입고번호
        , b.출고번호 현재출고번호
        , case
            when b.출고번호 is null or nvl(a.입고수량,0) <= nvl(b.출고수량,0) then
                a.입고번호 + 1
            else
                a.입고번호
        end 다음입고번호
        , case
            when b.출고번호 is null or nvl(b.출고수량,0) <= nvl(a.입고수량,0) then
                b.출고번호 + 1
            else
                b.출고번호
        end 다음출고번호
        , case
            when b.출고번호 is null or nvl(a.입고수량,0) <= nvl(b.출고수량,0) then
                1
            else
                0
        end 입고번호변경
        , case
            when b.출고번호 is null or nvl(b.출고수량,0) <= nvl(a.입고수량,0) then
                1
            else
                0
        end 출고번호변경
        , a.입고수량
        , b.출고수량 출고수량_원수량
        , case
            when nvl(a.입고수량,0) - nvl(b.출고수량,0) >= 0 then
                b.출고수량
            else
                a.입고수량
        end 출고수량_차감수량
        , case
            when nvl(a.입고수량,0) - nvl(b.출고수량,0) >= 0 then
                nvl(a.입고수량,0) - nvl(b.출고수량,0)
            else
                0
        end 입고잔량
        , case
            when nvl(a.입고수량,0) - nvl(b.출고수량,0) >= 0 then
                0
            else
                nvl(b.출고수량,0) - nvl(a.입고수량,0)
        end 출고잔량
        ,a.입고단가
        ,1 cnt
    from    입고자료 a
            , 출고자료 b
    where   a.입고번호 = 1
    and     a.상품코드 = b.상품코드
    and     b.출고번호 = 1
    union all
    select
        a.상품코드
        , b1.입고key
        , b2.출고key
        , a.다음입고번호 현재입고번호
        , a.다음출고번호 현재출고번호
        , case
            when b2.출고번호 is null or
                ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) <=
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) then
                a.다음입고번호 + 1
            else
                a.다음입고번호
        end 다음입고번호
        , case
            when b2.출고번호 is null or
                ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) >=
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) then
                a.다음출고번호 + 1
            else
                a.다음출고번호
        end 다음출고번호
        , case
            when b2.출고번호 is null or
                ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) <=
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) then
                1
            else
                0
        end 입고번호변경
        , case
            when b2.출고번호 is null or
                ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) >=
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) then
                1
            else
                0
        end 출고번호변경
        , a.입고번호변경 * b1.입고번호 입고수량
        , a.출고번호변경 * b2.출고수량 출고수량_원수량
        , case
            when ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) >=
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) then
                ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) )
            else
                ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) )
        end 출고수량_차감수량
        , case
            when ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) >=
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) then
                ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) - 
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) )
            else
                0
        end 입고잔량
        , ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) - 
        nvl(
        case
            when ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) ) >=
                    ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) ) then
                ( nvl(a.출고잔량,0) + nvl(a.출고번호변경 * b2.출고수량,0) )
            else
                ( nvl(a.입고잔량,0) + nvl(a.입고번호변경 * b1.입고수량,0) )
        end
        ,0) 출고잔량
        ,b1.입고단가
        , a.cnt + 1
    from    base_recur a
            , 입고자료 b1
            , 출고자료 b2
    where   a.상품코드 = b1.상품코드
    and     a.다음입고번호 = b1.입고번호
    and     a.상품코드 = b2.상품코드
    and     a.다음출고번호 = b2.출고번호
)
, 출고단가 as
(
    select
        a.상품코드
        , a.출고key
        , sum(a.입고수량) 입고수량
        , sum(a.출고수량_차감수량) 출고수량
        , sum(a.출고수량_차감수량 * a.입고단가 ) / nullif(sum(a.출고수량_차감수량),0) 출고단가
        , sum(a.출고수량_차감수량 * a.입고단가 ) 출고금액
    from    base_recur a
    group by
        a.상품코드
        ,a.출고key
)
select
    a.상품코드
    , a.순번
    , a.구분
    , a.수량
    , case when a.구분 = '출고' then b.출고단가 else a.단가 end 입출고단가
    , nvl(a.수량,0) * case when a.구분 = '출고' then b.출고단가 else a.단가 end 입출고금액
    , sum(case when a.구분 = '출고' then -1 else 1 end * a.수량) over (partition by a.상품코드 order by a.상품코드,a.순번) 재고수량
    , sum(case when a.구분 = '출고' then -1 else 1 end * (nvl(a.수량,0) * case when a.구분 = '출고' then b.출고단가 else a.단가 end)) over (partition by a.상품코드 order by a.상품코드,a.순번) 재고금액
from    base_Table a
        , 출고단가 b
where   a.상품코드 = b.상품코드 (+)
and     a.순번 = b.출고key (+)
order by
    a.상품코드
    , a.순번
김흥수(protokhs)님이 2016-07-08 21:38에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41197오라클 쿼리문 SQL SERVER로 바꾸려면?? [2]
헤매이다
2016-07-18
7592
41196ㅂㅈㄷㅂㅈ [1]
강컴공
2016-07-12
7465
4119511g se 에 em 설치가 가능한가요?
남영민
2016-07-07
7479
41194실용성 있는 sql 퀴즈입니다. [5]
김흥수
2016-07-07
10539
41193작업시간그룹 기준 최초 최종 가져오기 문의 드립니다. [2]
김미림
2016-07-04
7943
41192오라클 쿼리에 버그가 있네요... [1]
김흥수
2016-07-03
7869
41191update sql 퀴즈입니다. [2]
김흥수
2016-06-30
7932
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다