질문드립니다 ㅠㅠ 0 5 451

by 로로키 [SQL Query] [2021.06.21 16:13:40]


SUM OVER 을 사용해보려했는데 무엇을 구분해서 잡아야할지 감이 안잡히네요 ㅠㅠ

오라클 사용중입니다!

by 랑에1 [2021.06.21 16:54:09]
SELECT date, name, type, price
     , SUM(price) OVER(ORDER BY date) AS remain_price
FROM 
(
	SELECT * FROM 적립 UNION ALL
	SELECT * FROM 만료 UNION ALL 
	SELECT * FROM 사용 UNION ALL 
	SELECT * FROM 취소 
)

 


by 우리집아찌 [2021.06.21 16:58:32]

결과값 sql에 sun(price) over(order by date ) 하면 될거같아요


by 마농 [2021.06.21 17:05:52]
WITH t AS
(
SELECT '2021/03/21' dat, '가입축하금' nam, '적립' typ, 10000 price FROM dual
UNION ALL SELECT '2021/04/28', '생일축하금'    , '적립',   8000 FROM dual
UNION ALL SELECT '2021/05/05', '가정의달포인트', '적립',   3000 FROM dual
UNION ALL SELECT '2021/06/08', '룰렛포인트'    , '적립',  10000 FROM dual
UNION ALL SELECT '2021/04/21', '가입축하금'    , '만료', -10000 FROM dual
UNION ALL SELECT '2021/04/29', '지오다노'      , '사용',  -5000 FROM dual
UNION ALL SELECT '2021/05/08', '스타벅스'      , '사용',  -3500 FROM dual
UNION ALL SELECT '2021/05/22', '지오다노'      , '취소',  -5000 FROM dual
)
SELECT dat, nam, typ, price
     , SUM(DECODE(typ, '취소', -price, price)) OVER(ORDER BY dat) remain_price
  FROM t
;

 


by 랑에1 [2021.06.21 17:14:13]

헙 취소가;;


by 로로키 [2021.06.21 17:45:16]

너무너무감사합니다ㅠㅠㅠㅠㅠ 

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