데이터베이스 질문과 답변
안녕하세요 쿼리를 만들어야 하는데 잘 되네요.
가능한지가 궁금하네요.
| 매수가 | 수량 | 평균단가 | ||
| 매수 | 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 순으로 입력 되었다고 생각하고 할수 있는 방법이 있을까요?
-- 평균단가 위에거 맞나요? 공식틀리면 수정해서 사용하세요.
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
댓글 감사합니다.
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
;
댓글 감사합니다.
쿼리 분석을 좀 해야겠습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.