순차적으로 차감하는 쿼리 0 2 5,114

by axelhoon [SQL Query] [2024.06.07 14:47:05]


WITH t AS ( SELECT 'aaa' item, '2024-07-01' due_dt, 500 pnt UNION ALL

SELECT 'aaa' item, '2024-08-01' due_dt, 400 pnt UNION ALL

SELECT 'aaa' item, '2024-09-01' due_dt, 100 pnt )

SELECT *

FROM t

ORDER BY regdt asc;

예를들면 600 pnt를 현재기준(2024-06-01)으로 due_dt 가 가까운거부터 차감하고

남은 pnt 를 나타내는 쿼리 구하려고 합니다.

결과

item due_dt    pnt remain

aaa 2024-07-01 500 0

aaa 2024-08-01 400 300

aaa 2024-09-01 100 100

감사합니다.

by v상이v [2024.06.07 16:41:29]
WITH t AS (
SELECT 'aaa' item, '2024-07-01' due_dt, 500 pnt UNION ALL
SELECT 'aaa' item, '2024-08-01' due_dt, 400 pnt UNION ALL
SELECT 'aaa' item, '2024-09-01' due_dt, 100 pnt )
SELECT item, due_dt, pnt, 
		case when SUM(pnt) over(order by due_dt asc) - 600 < 0 then 0
			 else SUM(pnt) over(order by due_dt asc) - 600
		end remain
FROM t
ORDER BY due_dt asc;

 


by 마농 [2024.06.10 08:51:07]
WITH t AS
(
SELECT 'aaa' item, '2024-07-01' due_dt, 500 pnt
UNION ALL SELECT 'aaa', '2024-08-01', 400 pnt
UNION ALL SELECT 'aaa', '2024-09-01', 100 pnt
)
SELECT item, due_dt, pnt
     , CASE WHEN 600 > s THEN 0
            WHEN 600 > s - pnt THEN s - 600
            ELSE pnt END remain
  FROM (SELECT item, due_dt, pnt
             , SUM(pnt) OVER(PARTITION BY item ORDER BY due_dt) s
             , 600 v
          FROM t
        ) a
;

 

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