안녕하세요. 마농님~~
아래 소스중 이해 않되는 부분이 있습니다.

lv-1 PRECEDING -- 제가 이해하기에는 이전에 나왔던 부모의 데이타.
DECODE(1
, MAX(MAX(flag)) OVER(PARTITION BY root_id ORDER BY lv ROWS BETWEEN lv-1 PRECEDING AND 1 PRECEDING)
, 0, MAX(flag)
) v

아래 예제는 트리가 두개이고 각 트리의 루트가 한개 존재(여러개도 존재).
제대로 계산을 못하네요.
제가 마농님 쿼리를 이해하지 못해서 수정이 힘드네요.
도움 부탁드립니다.
감사합니다.


with t as
(
select 2 id, 1 upper_id,'155M' nm,'02' stat,1 CHNL from dual
union all select 6, 2 ,'51M','01',1 from dual
union all select 7, 2 ,'51M','01',1 from dual
union all select 8, 2 ,'51M','02',1 from dual
union all select 9, 2 ,'51M','05',1 from dual
union all select 10, 2 ,'51M','01',1 from dual
union all select 11, 2 ,'51M','01',1 from dual
union all select 12, 2 ,'51M','08',1 from dual
union all select 13, 2 ,'51M','01',1 from dual
union all select 14, 6 ,'2M','05',1 from dual
union all select 15, 6 ,'2M','08',1 from dual
union all select 16, 6 ,'2M','01',1 from dual
union all select 17, 6 ,'2M','03',1 from dual
union all select 18, 7 ,'2M','01',1 from dual
union all select 19, 7 ,'2M','01',1 from dual
union all select 20, 7 ,'2M','01',1 from dual
union all select 21, 7 ,'2M','01',1 from dual
union all select 22, 8 ,'2M','08',1 from dual
union all select 23, 8 ,'2M','03',1 from dual
union all select 24, 8 ,'2M','01',1 from dual
union all select 25, 8 ,'2M','01',1 from dual
union all select 26, 9 ,'2M','01',1 from dual
union all select 27, 9 ,'2M','01',1 from dual
union all select 28, 9 ,'2M','01',1 from dual
union all select 29, 9 ,'2M','01',1 from dual

union all select 2 , 1 ,'155M' ,'02' ,2  from dual
union all select 6, 2 ,'51M','01',2 from dual
union all select 7, 2 ,'51M','01',2 from dual
union all select 8, 2 ,'51M','02',2 from dual
union all select 9, 2 ,'51M','05',2 from dual
union all select 10, 2 ,'51M','01',2 from dual
union all select 11, 2 ,'51M','01',2 from dual
union all select 12, 2 ,'51M','08',2 from dual
union all select 13, 2 ,'51M','01',2 from dual
union all select 22, 8 ,'2M','08',2 from dual
union all select 23, 8 ,'2M','03',2 from dual
union all select 24, 8 ,'2M','01',2 from dual
union all select 25, 8 ,'2M','01',2 from dual
union all select 26, 9 ,'2M','01',2 from dual
union all select 27, 9 ,'2M','01',2 from dual
union all select 28, 9 ,'2M','01',2 from dual
union all select 29, 9 ,'2M','01',2 from dual
union all select 30, 10 ,'2M','05',2 from dual
union all select 31, 10 ,'2M','01',2 from dual
union all select 32, 10 ,'2M','01',2 from dual
union all select 33, 10 ,'2M','01',2 from dual
union all select 34, 11 ,'2M','03',2 from dual
union all select 35, 11 ,'2M','01',2 from dual
union all select 36, 11 ,'2M','08',2 from dual
union all select 37, 11 ,'2M','01',2 from dual
union all select 42, 13 ,'2M','01',2 from dual
union all select 43, 13 ,'2M','01',2 from dual
union all select 44, 13 ,'2M','01',2 from dual
union all select 45, 13 ,'2M','01',2 from dual
)
SELECT lv, nm
, SUM(v) cnt
FROM
(
SELECT lv, nm
, DECODE(1
, MAX(MAX(flag)) OVER(PARTITION BY root_id ORDER BY lv ROWS BETWEEN lv-1 PRECEDING AND 1 PRECEDING)
, 0, MAX(flag)
) v
FROM
(
SELECT nm
, LEVEL lv
, CONNECT_BY_ROOT(id) root_id
, DECODE(stat, '03', 1, '08', 1, 0) flag
FROM t
START WITH upper_id = 1
CONNECT BY PRIOR id = upper_id
)
GROUP BY root_id, lv, nm
)
GROUP BY lv, nm
ORDER BY lv, nm
;