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.
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.
