tableA
seq | s_lv | amt |
1 | 1 | |
2 | 2 | |
3 | 3 | 100 |
4 | 3 | 150 |
5 | 1 | |
6 | 2 | |
7 | 3 | 100 |
8 | 2 | |
9 | 3 | 100 |
원하는 결과
seq | s_lv | amt |
1 | 1 | 250 |
2 | 2 | 250 |
3 | 3 | 100 |
4 | 3 | 150 |
5 | 1 | 200 |
6 | 2 | 100 |
7 | 3 | 100 |
8 | 2 | 100 |
9 | 3 | 100 |
tableA의 데이터로 자신의 하위단계의 실제 값의 합을 구하고 싶은데 잘 안되네요..
with tableA as ( select 1 as seq, 1 as s_lv, null amt from dual union all select 2, 2, null from dual union all select 3, 3, 100 from dual union all select 4, 3, 150 from dual union all select 5, 1, null from dual union all select 6, 2, null from dual union all select 7, 3, 100 from dual union all select 8, 2, null from dual union all select 9, 3, 100 from dual ) select seq, new_seq, s_lv, amt , sum(amt) over(partition by new_seq order by seq desc) sum_amt from ( select seq, s_lv, amt , last_value(case when s_lv = 1 then seq else null end ignore nulls) over(order by seq) new_seq from tableA ) order by seq
이렇게 하면 new_seq가 5인것의 데이터는 전체 누계가 되어버리네요..
원하는결과의 주황색 부분처림 seq가 6일때 100, 8일때 100이나오게 하고싶습니다.
고수님들의 조언 부탁합니다.
WITH t AS ( SELECT 1 seq, 1 s_lv, null amt FROM dual UNION ALL SELECT 2, 2, null FROM dual UNION ALL SELECT 3, 3, 100 FROM dual UNION ALL SELECT 4, 3, 150 FROM dual UNION ALL SELECT 5, 1, null FROM dual UNION ALL SELECT 6, 2, null FROM dual UNION ALL SELECT 7, 3, 100 FROM dual UNION ALL SELECT 8, 2, null FROM dual UNION ALL SELECT 9, 3, 100 FROM dual ) SELECT a.seq, a.s_lv, a.amt , SUM(a.amt) OVER(ORDER BY a.seq ROWS BETWEEN CURRENT ROW AND NVL(MIN(b.seq) - a.seq - 1, 99) FOLLOWING) s FROM t a , t b WHERE a.seq < b.seq (+) AND a.s_lv >= b.s_lv(+) GROUP BY a.seq, a.s_lv, a.amt ;
SELECT seq, s_lv, amt , (SELECT SUM(amt) FROM t START WITH seq = a.seq CONNECT BY PRIOR seq + 1 = seq AND s_lv > a.s_lv ) x FROM t a ;