5,6행은 잘못 예시를 잘못 작성하신듯.. 5행이 50이 넘으니 100 그대로 나와야할 것 같네요.
with t as ( select 1 rn, 100 val from dual union all select 2 rn, 40 val from dual union all select 3 rn, 40 val from dual union all select 4 rn, 50 val from dual union all select 5 rn, 100 val from dual union all select 6 rn, 10 val from dual ) select rn , decode(row_number() over (partition by fg order by rn),1,sum(val) over(partition by fg),0) val from ( select rn, val, fg + case when val >= 50 then max(rn) over() else 0 end fg from ( select rn, val, sum(case when val >= 50 then 1 end ) over(order by rn) fg from t ) ) order by rn
WITH t AS ( SELECT 1 rn, 100 v FROM dual UNION ALL SELECT 2, 40 FROM dual UNION ALL SELECT 3, 40 FROM dual UNION ALL SELECT 4, 50 FROM dual UNION ALL SELECT 5, 10 FROM dual UNION ALL SELECT 6, 100 FROM dual ) , tmp(rn, v, gb, flag) AS ( SELECT rn , v , 1 gb , 1 flag FROM t WHERE rn = 1 UNION ALL SELECT c.rn , CASE WHEN p.v < 50 THEN p.v ELSE 0 END + c.v AS v , CASE WHEN p.v < 50 THEN 0 ELSE 1 END + p.gb AS gb , CASE WHEN p.v < 50 THEN 0 ELSE 1 END AS flag FROM tmp p , t c WHERE c.rn = p.rn + 1 ) SELECT rn , DECODE(flag, 1, MAX(v) OVER(PARTITION BY gb), 0) v FROM tmp ;