지난 번에 답해 드렸던 쿼리에서는 가중치라는 고려 사항이 없었기 때문에 비교적 쉽게 답을 드릴 수가 있었는데 이번에는 가중치가 추가됨으로써 쉽지가 않았습니다. 한참을 고민해도 답이 안 나와 다음과 같은 비효율적인 방법으로 해결 할 수 밖에 없었습니다. 혹시나 누군가가 하나의 쿼리로 구현해주신다면 상당히 좋은 사례가 될 것 같습니다.

[ 고려사항 ]

1. 사용된 테이블에 '가동률'이라는 컬럼은 존재하지 않는다고 가정 했습니다. 실제로 이러한 컬럼이 존재한다면 처음부터 값을 만들어서 넣는 것이 효과적인 방법입니다. 또는 temporary table을 만들어놓고 값을 집어넣어도 되겠지요. 여기에서는 테이블 T가 단지 service_id, upper_service, value, weight 만을 가지고 있으며 이것으로 즉석에서 '가동률'을 뽑아내고 싶다고 가정했습니다.

2. 1번 과 같이 말씀 드린 이유는 이 쿼리를 대용량의 데이터에 사용 했을 경우 많은 부담이 예상되기 때문입니다. 밑을 보시면 아시겠지만 생성된 함수는 간단한 recursive 함수입니다. recursive 하게 계속 함수를 호출하다 보면 자원 소모가 많아지며 선형 계산에 비해서 그 속도가 현저히 떨어집니다. 또한 root를 구하기 위해 전체를 recursive로 돌린 후 또한 그 하위 노드를 구하기 위해 동일한 작업을 하므로 상당히 비효율적입니다. 그러므로 아래의 쿼리로 도출된 값을 테이블에 update 하시거나 또는 필요한 컬럼만 선택해서 MVIEW로 만들어놓고 가끔 싱크를 하는 것이 나을 것 같습니다.

3. connect by를 사용한 쿼리로 계층적 표현을 한 다음 해당하는 값은 쿼리 내에서 함수를 써서 가져옵니다.

[ 솔루션 ]

create table T(service_id number, upper_service number, depth number, value number, weight number);

create or replace function get_value(p_id t.service_id%type)
return number
as
    ln_return   number default 0;
 ln_value    number default 0;
 ln_weight   number default 0;

begin
    select value, nvl(weight, 1)
        into ln_value, ln_weight
      from t
    where service_id = p_id;

 if ln_value is null then
     for c1 in (select service_id
                        from t
                    where upper_service = p_id) loop
            ln_return := ln_return+get_value(c1.service_id);
     end loop;
 else
     ln_return := ln_value;
 end if;

 return ln_return*ln_weight;
end;

 select substr(rpad('-',b.lev*2,'-')||b.service_id, 1, 10),
             b.value, b.weight,
            get_value(b.service_id) as sum
  from (select level lev, service_id,  value,  weight
                from t
                start with upper_service is null
          connect by prior service_id = upper_service) b