저희 매출,매입,재고를 이용해서 이동평균법을 구하고싶습니다.

현재 테이블은

현재 재고(wonga_080621final),매출(sed100,sem100),매입1(sed200,sem200),매입2(sdm300) 테이블을 이용해서 이동평균을 구하는 아래와 같은 쿼리를 짜보았습니다.

 SELECT a.*,
        Round(CASE WHEN la = 0 THEN
                 CASE WHEN gubun = 1 THEN
                          CASE WHEN (price = 0 AND ipgo_price <> 0)  THEN
                                  ipgo_price
                               WHEN (price <> 0 AND ipgo_price = 0) THEN
                                  price
                               WHEN (price = 0 AND ipgo_price = 0 ) THEN
                                  0
                          ELSE

                            (((CASE WHEN qty <= 0 THEN 0 ELSE qty END * price) + (ipgo_qty * ipgo_price)) /Decode(qty2,0,1,qty2))
                          END
                 ELSE
                         price
                 END
            ELSE
                 CASE WHEN gubun = 1 THEN
                          CASE WHEN (price = 0 AND ipgo_price <> 0)  THEN
                                  ipgo_price
                               WHEN (price <> 0 AND ipgo_price = 0) THEN
                                  price
                               WHEN (price = 0 AND ipgo_price = 0 ) THEN
                                  0
                          ELSE

                            (((CASE WHEN qty <= 0 THEN 0 ELSE qty END * price) + (ipgo_qty * ipgo_price)) /Decode(qty2,0,1,qty2))
                          END
                 ELSE
                          price  
                 END
            END,0) l_price
 FROM  
   (SELECT prod_cd,Decode(la,0,qty,a_qty) qty,price,ipgo_date,ipgo_qty,sale_qty,ipgo_price,
          sale_price,gubun,ro,la,sum_ipgo_qty,sum_sale_qty,qty2,a_qty
    FROM
        (SELECT prod_cd,qty,price,ipgo_date,ipgo_qty,sale_qty,ipgo_price,sale_price,gubun,
                         ro,la,sum_ipgo_qty,sum_sale_qty,qty2,
                        Lag(qty2,1,0) over(PARTITION BY prod_cd ORDER BY prod_cd,ipgo_date) a_qty

       FROM
               (SELECT prod_cd,qty,price,ipgo_date,ipgo_qty,sale_qty,ipgo_price,sale_price,gubun,
                                 ro,la,sum_ipgo_qty,sum_sale_qty,(qty+sum_ipgo_qty-sum_sale_qty) qty2
               FROM 
                            (SELECT prod_cd,qty,price,ipgo_date,ipgo_qty,sale_qty,ipgo_price,
                                             sale_price,gubun,ro,
                                             Lag(gubun,1,0) over (PARTITION BY prod_cd ORDER BY prod_cd,ipgo_date) la,
                               Sum(ipgo_qty) over (PARTITION BY prod_cd ORDER BY prod_cd,ipgo_date,ro) sum_ipgo_qty,
                               Sum(sale_qty) over (PARTITION BY prod_cd ORDER BY prod_cd,ipgo_date,ro) sum_sale_qty
                             FROM 
                                       (SELECT a.prod_cd,b.qty,b.price,a.ipgo_date,a.ipgo_qty,a.sale_qty,a.ipgo_price,
                                                         a.sale_price,a.gubun,Row_Number() over(PARTITION BY a.prod_cd ORDER  BY  a.prod_cd,ipgo_date  DESC,gubun DESC) ro,
                                    Lag(a.gubun,1,0) over (PARTITION BY a.prod_cd ORDER BY a.prod_cd,ipgo_date) la            
                                      FROM
                                           (SELECT ipgo_date,prod_cd,ipgo_qty,sale_qty,ipgo_price,sale_price,gubun

                                           FROM
                                                (SELECT a.ipgo_date ipgo_date,a.prod_cd prod_cd ,  
                                                                 sum(decode(b.ipgo_type,’2’,-1,’7’,-1,1)*a.ipgo_qty) ipgo_qty,
                                                                 0 sale_qty,a.price ipgo_price,0 sale_price,1 gubun 
                                                FROM sem200_2 b ,sed200_2 a          
                                                WHERE a.gubun      = b.gubun
                                               AND a.saupjang_cd = b.saupjang_cd
                                               AND a.shop                = b.shop
                                               AND a.ipgo_date       = b.ipgo_date
                                               AND a.ipgo_seq        = b.ipgo_seq
                                               AND a.ipgo_date between ’20080621’ and ’20080731’
                                              GROUP BY a.ipgo_date,a.prod_cd,a.price)
                                              UNION ALL
                                              (SELECT a.sale_date,a.prod_cd,0,
                                                                (decode(b.sale_type,’2’,-1,’4’,-1,1)*a.sale_qty) sale_qty, 0,a.price,2
                                              FROM sem100 b , sed100 a
                                              WHERE a.gubun      = b.gubun
                                              AND a.saupjang_cd = b.saupjang_cd
                                              AND a.shop               = b.shop
                                              AND a.sale_date      = b.sale_date
                                              AND a.sale_seq       = b.sale_seq
                                              AND a.sale_date between ’20080621’ and ’20080731’)
                                             UNION ALL
                                             (SELECT ip_day,prod_cd,(Decode(gu_bun,’2’,-1,1) * qty),0,
                                                               nvl(wonga,0),0,1
                                             FROM sdm300
                                             WHERE ip_day between ’20080621’ and ’20080731’)) a ,  wonga_080621final b  
                                          WHERE a.prod_cd  = b.prod_cd)
                    ) ORDER BY prod_cd,ipgo_date,ro)) ) a

위의 쿼리 결과물을 파일첨부로 첨부합니다.

엑셀 결과물을 보면 기간동안 첫번째 매입이 일어 났을때는 평균이 잘 구해집니다.

하지만 첫번째 매입이 일어났을때 구한 원가를 다음 매입이 일어날때까지 다음 로우에

기초원가로 넣고 싶습니다. 그래야 다음 매입이 일어났을때 평균을 구한 원가로

다시 평균을 구할수 있기 때문입니다. lag를 사용하면 다음 로우에 원가를 집어넣는 것이

가능 하지만 다음 매입이 일어날때까지 평균 원가를 기초원가에 남아있게 할수가 없습니다.

어떻게 쿼리상에 방법이 없을지 고수님들의 자문 부탁드립니다.