쿼리 어떻게 만들어야 할까요 0 4 177

by 최지용 [SQL Query] 쿼리 [2018.06.12 17:53:18]


안녕하세요 쿼리를 만들어야 하는데 잘 되네요. 

가능한지가 궁금하네요.

  매수가 수량   평균단가
매수 1000 500   1000
매수 1200 500   1100
매수 1300 1000   1200
매수 1400 2000   1300
매도 1500 -1000   1300
매수 1500 800   1342.11
매수 1600 200   1355
1800 -1000   1355
매수 1700 800   1427.63
매수 1800 300   1454.88
매수 1900 400   1494.44
매수 2000 200   1515.96

 

매수 매도 구분값으로 매수 평단가를 구하려고 하는데. 노란색 부분 매도일경우는 평단가가 바뀌면 안된다고 할때 어떻게 처리를 해야 할까요?

WITH CTE
AS
(
SELECT 1 ORD, 'B' GUBUN, 1000 PRICE, 500 VOL FROM DUAL UNION ALL
SELECT 2 ORD, 'B' GUBUN, 1200 PRICE, 500 VOL FROM DUAL UNION ALL
SELECT 3 ORD, 'B' GUBUN, 1300 PRICE, 1000 VOL FROM DUAL UNION ALL
SELECT 4 ORD, 'B' GUBUN, 1400 PRICE, 2000 VOLUME FROM DUAL UNION ALL
SELECT 5 ORD, 'S' GUBUN, 1500 PRICE, -1000 VOLUME FROM DUAL UNION ALL
SELECT 6 ORD, 'B' GUBUN, 1500 PRICE, 800 VOLUME FROM DUAL UNION ALL
SELECT 7 ORD, 'B' GUBUN, 1600 PRICE, 200 VOLUME FROM DUAL UNION ALL
SELECT 8 ORD, 'S' GUBUN, 1800 PRICE, -1000 VOLUME FROM DUAL UNION ALL
SELECT 9 ORD, 'B' GUBUN, 1700 PRICE, 800 VOLUME FROM DUAL UNION ALL
SELECT 10 ORD, 'B' GUBUN, 1800 PRICE, 300 VOLUME FROM DUAL UNION ALL
SELECT 11 ORD, 'B' GUBUN, 1900 PRICE, 400 VOLUME FROM DUAL UNION ALL
SELECT 12 ORD, 'B' GUBUN, 2000 PRICE, 200 VOLUME FROM DUAL 
)

SELECT * FROM CTE;

소트는 ORD 순으로 입력 되었다고 생각하고 할수 있는 방법이 있을까요?

by 우리집아찌 [2018.06.12 18:21:24]
-- 평균단가 위에거 맞나요? 공식틀리면 수정해서 사용하세요.
WITH CTE
AS
(
SELECT 1 ORD, 'B' GUBUN, 1000 PRICE, 500 VOL FROM DUAL UNION ALL
SELECT 2 ORD, 'B' GUBUN, 1200 PRICE, 500 VOL FROM DUAL UNION ALL
SELECT 3 ORD, 'B' GUBUN, 1300 PRICE, 1000 VOL FROM DUAL UNION ALL
SELECT 4 ORD, 'B' GUBUN, 1400 PRICE, 2000 VOLUME FROM DUAL UNION ALL
SELECT 5 ORD, 'S' GUBUN, 1500 PRICE, -1000 VOLUME FROM DUAL UNION ALL
SELECT 6 ORD, 'B' GUBUN, 1500 PRICE, 800 VOLUME FROM DUAL UNION ALL
SELECT 7 ORD, 'B' GUBUN, 1600 PRICE, 200 VOLUME FROM DUAL UNION ALL
SELECT 8 ORD, 'S' GUBUN, 1800 PRICE, -1000 VOLUME FROM DUAL UNION ALL
SELECT 9 ORD, 'B' GUBUN, 1700 PRICE, 800 VOLUME FROM DUAL UNION ALL
SELECT 10 ORD, 'B' GUBUN, 1800 PRICE, 300 VOLUME FROM DUAL UNION ALL
SELECT 11 ORD, 'B' GUBUN, 1900 PRICE, 400 VOLUME FROM DUAL UNION ALL
SELECT 12 ORD, 'B' GUBUN, 2000 PRICE, 200 VOLUME FROM DUAL 
)
 SELECT ORD 
      , "매도/매수"
      , LAST_VALUE ("평균단가")IGNORE NULLS OVER(ORDER BY ORD DESC ) "평균단가"
   FROM (SELECT ORD 
             , CASE WHEN GUBUN = 'B' THEN '매수' 
                    WHEN GUBUN = 'S' THEN '매도' 
                END "매도/매수"
             , ROUND(CASE WHEN GUBUN = 'B' THEN  SUM(CASE WHEN GUBUN = 'B' THEN PRICE*VOL END ) OVER(ORDER BY ORD) 
                                                     / SUM(CASE WHEN GUBUN = 'B' THEN VOL END ) OVER(ORDER BY ORD)
                      END) "평균단가"
          FROM CTE 
         ORDER BY ORD , GUBUN
        ) 
   ORDER BY ORD
 

 


by 최지용 [2018.06.12 19:49:38]

댓글 감사합니다.


by 마농 [2018.06.12 18:30:49]
WITH cte AS
(
SELECT 1 ord, 'B' gubun, 1000 price, 500 vol FROM dual
UNION ALL SELECT  2, 'B', 1200,   500 FROM dual
UNION ALL SELECT  3, 'B', 1300,  1000 FROM dual
UNION ALL SELECT  4, 'B', 1400,  2000 FROM dual
UNION ALL SELECT  5, 'S', 1500, -1000 FROM dual
UNION ALL SELECT  6, 'B', 1500,   800 FROM dual
UNION ALL SELECT  7, 'B', 1600,   200 FROM dual
UNION ALL SELECT  8, 'S', 1800, -1000 FROM dual
UNION ALL SELECT  9, 'B', 1700,   800 FROM dual
UNION ALL SELECT 10, 'B', 1800,   300 FROM dual
UNION ALL SELECT 11, 'B', 1900,   400 FROM dual
UNION ALL SELECT 12, 'B', 2000,   200 FROM dual
)
, tmp(ord, gubun, price, vol, price_avg, vol_remain) AS
(
SELECT ord, gubun, price, vol
     , price price_avg
     , vol   vol_remain
  FROM cte
 WHERE ord = 1
 UNION ALL
SELECT a.ord, a.gubun, a.price, a.vol
     , DECODE(a.gubun, 'S', b.price_avg
       , ROUND((b.price_avg * b.vol_remain + a.price * a.vol) / (b.vol_remain + a.vol), 2)
       ) price_avg
     , b.vol_remain + a.vol vol_remain
  FROM cte a
     , tmp b
 WHERE a.ord = b.ord + 1
)
SELECT * FROM tmp
;

 


by 최지용 [2018.06.12 19:52:02]

댓글 감사합니다.

쿼리 분석을 좀 해야겠습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입