*Справочник "элементов"
CREATE CURSOR Bar (Id I, Name C(20))
INSERT INTO Bar (Id, Name) VALUES (1, "A")
INSERT INTO Bar (Id, Name) VALUES (2, "B")
INSERT INTO Bar (Id, Name) VALUES (3, "C")
INSERT INTO Bar (Id, Name) VALUES (4, "D")
INSERT INTO Bar (Id, Name) VALUES (5, "E")

*Справочник "конструкций"
CREATE CURSOR Foo (Id I, Name C(20))
INSERT INTO Foo (Id, Name) VALUES (10, "A")
INSERT INTO Foo (Id, Name) VALUES (20, "B")
INSERT INTO Foo (Id, Name) VALUES (30, "C")
INSERT INTO Foo (Id, Name) VALUES (40, "A-B")
INSERT INTO Foo (Id, Name) VALUES (50, "A-C")
INSERT INTO Foo (Id, Name) VALUES (60, "A-B-C")
INSERT INTO Foo (Id, Name) VALUES (70, "A-B-D")

*Состав "конструкции"
CREATE CURSOR FooItem (Id I, FooId I, BarId I)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (100, 10, 1)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (200, 20, 2)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (300, 30, 3)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (400, 40, 1)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (500, 40, 2)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (600, 50, 1)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (700, 50, 3)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (800, 60, 1)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (900, 60, 2)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (1000, 60, 3)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (1100, 70, 1)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (1200, 70, 2)
INSERT INTO FooItem (Id, FooId, BarId) VALUES (1300, 70, 4)

*Рабочая таблица. Поле KnownFooId - это эталонные данные
* - т.е. то что надо получить в результате.
*Реально код конструкции неизвестен 
CREATE CURSOR Real (Id I, KnownFooId I)
INSERT INTO Real (Id, KnownFooId) VALUES (-1, 10)
INSERT INTO Real (Id, KnownFooId) VALUES (-2, 20)
INSERT INTO Real (Id, KnownFooId) VALUES (-3, 30)
INSERT INTO Real (Id, KnownFooId) VALUES (-4, 40)
INSERT INTO Real (Id, KnownFooId) VALUES (-5, 50)
INSERT INTO Real (Id, KnownFooId) VALUES (-6, 60)
INSERT INTO Real (Id, KnownFooId) VALUES (-7, 70)
INSERT INTO Real (Id, KnownFooId) VALUES (-8, 40)

*Рабочая таблица - "использованные элементы"
CREATE CURSOR RealItem (Id I, RealId I, BarId I)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-10, -1, 1)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-20, -2, 2)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-30, -3, 3)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-40, -4, 2)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-50, -4, 1)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-60, -5, 1)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-70, -5, 3)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-80, -6, 1)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-90, -6, 3)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-100, -6, 2)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-110, -7, 4)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-120, -7, 2)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-130, -7, 1)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-140, -8, 1)
INSERT INTO RealItem (Id, RealId, BarId) VALUES (-150, -8, 2)

SELECT x.RealId as Id, y.FooId as KnownFooId FROM ;
    (SELECT RealId, ;
            AVG(CAST(BarId as numeric(12,3))) as f1, ;
            AVG(CAST(BarId*BarId as numeric(12,3))) as f2, ;
            AVG(CAST(BarId*BarId*BarId as numeric(12,3))) as f3 ;
        FROM RealItem GROUP BY RealId) x ;
JOIN ;
    (SELECT FooId, ;
            AVG(CAST(BarId as numeric(12,3))) as f1, ;
            AVG(CAST(BarId*BarId as numeric(12,3))) as f2, ;
            AVG(CAST(BarId*BarId*BarId as numeric(12,3))) as f3 ;
        FROM FooItem GROUP BY FooId) y ;
    ON x.f1=y.f1 AND x.f2=y.f2 AND x.f3=y.f3 ;
    ORDER BY x.RealId desc

AVG(CAST(BarId*BarId as numeric(12,3)))

AVG(CAST(BarId as Float)*CAST(BarId as Float))

select t1.RealId, t1.FooId from ;
    (select RealId, FooId, count(distinct r.BarId) as cnt ;
        from RealItem r join FooItem f on r.BarId = f.BarId ;
        group by RealId, FooId) t1 join ;
    (select RealId, count(distinct BarId) as cnt ;
        from RealItem group by RealId) t2 ;
            on t1.RealId = t2.RealId and t1.cnt = t2.cnt join ;
    (select FooId, count(distinct BarId) as cnt ;
        from FooItem group by FooId) t3 ;
            on t1.FooId = t3.FooId and t1.cnt = t3.cnt

Select ;
    template.FooId ;
    ,realBase.RealId ;
    ,template.BarId_1 ;
    ,template.BarId_2 ;
    ,template.BarId_3 ;
from ;
    ( ;
    select  ;
        column1.FooId ;
        ,column1.BarId_1 ;
        ,(select min(fi2.BarId) from FooItem fi2  ;
            where fi2.FooId=column1.FooId and fi2.BarId > column1.BarId_1) as BarId_2 ;
        ,(select max(fi3.BarId) from FooItem fi3  ;
            where fi3.FooId=column1.FooId and fi3.BarId > column1.BarId_1 ;
            having max(fi3.BarId) <> min(fi3.BarId)) as BarId_3 ;
    from ( ;
        select  ;
            fi1.FooId,  ;
            min(fi1.BarId) as BarId_1 ;
        from FooItem fi1 ;
        group by fi1.FooId) as column1 ;
    ) as template ;
inner join ;
    ( ;
    select  ;
        column1.RealId ;
        ,column1.BarId_1 ;
        ,(select min(ri2.BarId) from RealItem ri2  ;
            where ri2.RealId=column1.RealId and ri2.BarId > column1.BarId_1) as BarId_2 ;
        ,(select max(ri3.BarId) from RealItem ri3  ;
            where ri3.RealId=column1.RealId and ri3.BarId > column1.BarId_1 ;
            having max(ri3.BarId) <> min(ri3.BarId)) as BarId_3 ;
    from ( ;
        select  ;
            ri1.RealId,  ;
            min(ri1.BarId) as BarId_1 ;
        from RealItem ri1 ;
        group by ri1.RealId) as column1 ;
    ) as realbase ;
on template.BarId_1 = realBase.BarId_1  ;
    and nvl(template.BarId_2,0)=nvl(realBase.BarId_2,0) ;
    and nvl(template.BarId_3,0)=nvl(realBase.BarId_3,0) 

SELECT x.RealId as Id, y.FooId as KnownFooId FROM ;
    (SELECT RealId, ;
            MIN(BarId) as f1, ;
            MAX(BarId) as f2, ;
            SUM(BarId) as f3 ;
        FROM RealItem GROUP BY RealId) x ;
JOIN ;
    (SELECT FooId, ;
            MIN(BarId) as f1, ;
            MAX(BarId) as f2, ;
            SUM(BarId) as f3 ;
        FROM FooItem GROUP BY FooId) y ;
    ON x.f1=y.f1 AND x.f2=y.f2 AND x.f3=y.f3 ;
    ORDER BY x.RealId desc

SELECT x.RealId as Id, y.FooId as KnownFooId, x.h1, x.h2, x.h3 FROM ;
    (SELECT r1.RealId, ;
        MIN(r1.BarId) as h1, ;
        (SELECT r2.BarId FROM RealItem r2 WHERE r2.RealId=r1.RealId ;
            AND 1=(SELECT COUNT(*) FROM RealItem r3 WHERE r3.RealId=r2.RealId AND r3.BarId<r2.BarId)) as h2, ;
        (SELECT r2.BarId FROM RealItem r2 WHERE r2.RealId=r1.RealId ;
            AND 2=(SELECT COUNT(*) FROM RealItem r3 WHERE r3.RealId=r2.RealId AND r3.BarId<r2.BarId)) as h3 ;
        FROM RealItem r1 GROUP BY r1.RealId) x ;
JOIN ;
    (SELECT f1.FooId, ;
        MIN(f1.BarId) as h1, ;
        (SELECT f2.BarId FROM FooItem f2 WHERE f2.FooId=f1.FooId ;
            AND 1=(SELECT COUNT(*) FROM FooItem f3 WHERE f3.FooId=f2.FooId AND f3.BarId<f2.BarId)) as h2, ;
        (SELECT f2.BarId FROM FooItem f2 WHERE f2.FooId=f1.FooId ;
            AND 2=(SELECT COUNT(*) FROM FooItem f3 WHERE f3.FooId=f2.FooId AND f3.BarId<f2.BarId)) as h3 ;
        FROM FooItem f1 GROUP BY f1.FooId) y ;
ON x.h1=y.h1 AND NVL(x.h2,0)=NVL(y.h2,0) AND NVL(x.h3,0)=NVL(y.h3,0) ;
ORDER BY x.RealId desc