일별 누계를 구하고 싶습니다. 0 1 525

by ohworld5 [SQL Query] postgres [2019.03.19 11:32:25]


아래와 같은 테이블에서 밑에 결과처럼 누계를 구하고 싶습니다.

ab1

ab2 ab3 Date deposit withdraw
et-bc3 token CHIKARINBG 2017/3/14 50000000000 0
et-bc3 token CHIKARINBG 2017/3/15 0 50000000000
et-bc4 token CHIKARINBG 2017/3/15 50000000000 0
et-bc4 token CHIKARINBG 2017/3/18 0 800000000
et-bc4 token CHIKARINBG 2017/3/22 0 200000000
et-bc4 token CHIKARINBG 2017/3/30 0 100000000
et-bc4 token CHIKARINBG 2017/4/11 0 400000000
et-bc4 token CHIKARINBG 2017/4/14 0 300000000

 

ab1 ab2 ab3 Date deposit withdraw deposit - withdraw total
et-bc3 token CHIKARINBG 2017/3/14 50000000000 0 50000000000 50000000000
et-bc3 token CHIKARINBG 2017/3/15 0 50000000000 -50000000000 0
et-bc4 token CHIKARINBG 2017/3/15 50000000000 0 50000000000 50000000000
et-bc4 token CHIKARINBG 2017/3/18 0 800000000 -800000000 49200000000
et-bc4 token CHIKARINBG 2017/3/22 0 200000000 -200000000 49000000000
et-bc4 token CHIKARINBG 2017/3/30 0 100000000 -100000000 48900000000
et-bc4 token CHIKARINBG 2017/4/11 0 400000000 -400000000 48500000000
et-bc4 token CHIKARINBG 2017/4/14 0 300000000 -300000000 48200000000
by 마농 [2019.03.19 14:24:53]
WITH t AS
(
SELECT 'et-bc3' ab1, 'token' ab2, 'CHIKARINBG' ab3, '2017/3/14' Date, 50000000000 deposit, 0 withdraw
UNION ALL SELECT 'et-bc3', 'token', 'CHIKARINBG', '2017/3/15',           0, 50000000000
UNION ALL SELECT 'et-bc4', 'token', 'CHIKARINBG', '2017/3/15', 50000000000,           0
UNION ALL SELECT 'et-bc4', 'token', 'CHIKARINBG', '2017/3/18',           0,   800000000
UNION ALL SELECT 'et-bc4', 'token', 'CHIKARINBG', '2017/3/22',           0,   200000000
UNION ALL SELECT 'et-bc4', 'token', 'CHIKARINBG', '2017/3/30',           0,   100000000
UNION ALL SELECT 'et-bc4', 'token', 'CHIKARINBG', '2017/4/11',           0,   400000000
UNION ALL SELECT 'et-bc4', 'token', 'CHIKARINBG', '2017/4/14',           0,   300000000
)
SELECT ab1
     , ab2
     , ab3
     , Date
     , deposit
     , withdraw
     , deposit - withdraw x
     , SUM(deposit - withdraw) OVER(PARTITION BY ab1, ab2, ab3 ORDER BY Date) total
  FROM t
;

 

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