Progressively Fusing Independent Probabilities using Proprietary SQL Windowing (Oracle version)

This is the sequel post to this proposal how to estimate the lifetime of spare parts using ordinary query language semantics.

This time, we are interested in propagating the hence obtained individual probabities up a more complex material hierarchy. For which we start by the simplifying assumption, that our only variable (“age” could also be named or measured as general “wear and tear”) does already take into account all the influences to a given material’s span of life. And when combining two materials into some higher structure, we assume that they do not influence each other’s durability alltoomuch.

create table STRUCTURED_MATERIAL (
  composite_material int references MATERIAL(material) not null,
  component_material int references MATERIAL(material) not null
);

insert into STRUCTURED_MATERIAL values (4,1);
insert into STRUCTURED_MATERIAL values (4,2);

insert into STRUCTURED_MATERIAL values (5,4);
insert into STRUCTURED_MATERIAL values (5,3);

Unfortunately, even under these assumptions fusing the individual probabilities turns out to require a “progressive” calculation, i.e., a formula which cannot be stated in terms of an ANSI SQL aggregation.

union_of_indep_probabities

Fortunately, the expressiveness of the typical Used-Defined-Function (UDF) frameworks, such as the one of the Oracle Database, is well able to handle that requirement even under parallel/distributed execution settings:

/* Oracle needs a type/object definition as the UDF-base */
create type ProbabilityUnionImpl as object
(
  probability NUMBER, -- memory just keeps current probability
  static function ODCIAggregateInitialize(sctx IN OUT ProbabilityUnionImpl) return number,
  member function ODCIAggregateIterate(self IN OUT ProbabilityUnionImpl
                                     , value IN number) return number,
  member function ODCIAggregateTerminate(self IN ProbabilityUnionImpl
                                       , returnValue OUT number
                                       , flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT ProbabilityUnionImpl
                                   , ctx2 IN ProbabilityUnionImpl) return number
);
/
/* and here are the method implementations */
create or replace type body ProbabilityUnionImpl is 

/* We start with an empty probability */
static function ODCIAggregateInitialize(sctx IN OUT ProbabilityUnionImpl) return number is 
begin
  sctx := ProbabilityUnionImpl(0);
  return ODCIConst.Success;
end;

/* Local iteration implements fusion under independence */
member function ODCIAggregateIterate(self IN OUT ProbabilityUnionImpl
                                   , value IN number) return number is
begin
  self.probability:=self.probability+value-self.probability*value;
  return ODCIConst.Success;
end;

/* At the end, just return the stored probability */
member function ODCIAggregateTerminate(self IN ProbabilityUnionImpl
                                     , returnValue OUT number
                                     , flags IN number) return number is
begin
  returnValue := self.probability;
  return ODCIConst.Success;
end;

/* Distributed/Parallel merge, same case as local iteration */
member function ODCIAggregateMerge(self IN OUT ProbabilityUnionImpl
                                 , ctx2 IN ProbabilityUnionImpl) return number is
begin
  self.probability:=self.probability+ctx2.probability-self.probability*ctx2.probability;
  return ODCIConst.Success;
end;

end;
/

/* finally, the aggregation function is defined in terms of the type */
create function union_indep_prop (input number) return number 
PARALLEL_ENABLE AGGREGATE USING ProbabilityUnionImpl;

With that backing installed, we are now able to complete our estimation table even for complex materials. Since we are Oracle-specific anyway, we also use the established recursion syntax when flattening the bill of material tree.

insert into REPAIR_ESTIMATIONS
with
  /* we just need the root - leaf material relations */
  DECOMPOSITION as (
  select 
         CONNECT_BY_ROOT 
         composite.composite_material as root_material
       , composite.component_material as component_material
       , level as depth
    from STRUCTURED_MATERIAL composite
 connect by prior composite.component_material=composite.composite_material 
)
 /* for each age of an individual age, find all corresponding probabilities of 
    sibling materials for fusion */
 select DECOMPOSITION.root_material
      , REPAIR.age
      , lead(REPAIR.age-1) over (partition by DECOMPOSITION.root_material order by REPAIR.age) as next_age
      , union_indep_prop(ALL_REPAIRS.probability) as probability
      , union_indep_prop(ALL_REPAIRS.lower_bound_95) as lower_bound_95
      , union_indep_prop(ALL_REPAIRS.upper_bound_95) as upper_bound_95
      , max(DECOMPOSITION.depth) as depth
  from DECOMPOSITION
    join REPAIR_ESTIMATIONS REPAIR on REPAIR.material=DECOMPOSITION.component_material and REPAIR.depth=0
    join DECOMPOSITION ALL_PARTS on ALL_PARTS.root_material=DECOMPOSITION.root_material 
    join REPAIR_ESTIMATIONS ALL_REPAIRS on ALL_REPAIRS.material=ALL_PARTS.component_material and
         REPAIR.age between ALL_REPAIRS.age and coalesce(ALL_REPAIRS.next_age, REPAIR.age)
 group by DECOMPOSITION.root_material
        , REPAIR.age
;

Stay tuned for the upcoming Teradata formulation.

Advertisements

2 thoughts on “Progressively Fusing Independent Probabilities using Proprietary SQL Windowing (Oracle version)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s