CONNECT BY для двух таблиц с двумя JOINS

У меня есть 3 таблицы:

  • два с иерархическими структурами
    (например, "измерения" рекурсивного типа иерархии);
  • один с суммирующими данными (например, «факты» со столбцом X).

Они здесь:

  1. DIM1 (ID1, РОДИТЕЛЬ2, ИМЯ1)
  2. DIM2 (ID2, РОДИТЕЛЬ2, ИМЯ2)
  3. ФАКТЫ (ID1, ID2, X)

Пример таблицы DIM1:

--        1   0  DIM1  
----      2   1  DIM1-A  
------    3   2  DIM1-A-A  
--------  4   3  DIM1-A-A-A  
--------  5   3  DIM1-A-A-B  
------    6   2  DIM1-A-B  
--------  7   6  DIM1-A-B-A  
--------  8   6  DIM1-A-B-B  
------    9   2  DIM1-A-C  
----     10   1  DIM1-B  
------   11  10  DIM1-B-C  
------   12  10  DIM1-B-D  
----     13   1  DIM1-C

Пример таблицы DIM2:

--        1   0  DIM2  
----      2   1  DIM2-A  
------    3   2  DIM2-A-A  
--------  4   3  DIM2-A-A-A  
--------  5   3  DIM2-A-A-B  
--------  6   3  DIM2-A-B-C  
------    7   2  DIM2-A-B  
----      8   1  DIM2-B  
----      9   1  DIM2-C

Пример таблицы FACTS:

 1   1  100  
 1   2   30  
 1   3  500  
 --    ................  
 13  9  200

И я хотел бы создать единственный SELECT, где я укажу родителя для DIM1 (например, ID1=2 для DIM1-A) и родителя для DIM2 (например, ID2=2 для DIM2-A), а SELECT будет генерировать такой отчет:

Name_of_1 Name_of_2 Sum_of_X  
--------- --------- ----------  
DIM1-A-A  DIM2-A-A  (some sum)  
DIM1-A-A  DIM2-A-B  (some sum)  
DIM1-A-B  DIM2-A-A  (some sum)  
DIM1-A-B  DIM2-A-B  (some sum)  
DIM1-A-C  DIM2-A-A  (some sum)  
DIM1-A-C  DIM2-A-B  (some sum)

Я хотел бы использовать CONNECT BY фразу, START WITH фразу, SUM фразу, GROUP BY фразу и OUTER или INNER (?) JOIN. Мне не нужны другие расширения Oracle 10.2.

Другими словами: только с "классическим" SQL и
только с расширениями Oracle для иерархических запросов.

Является ли это возможным?

Я провел несколько экспериментов с вопросом в
смешивании вместе Соединение, внутреннее соединение и суммирование с помощью Oracle

(где есть очень хорошее решение, но только для одной
таблицы измерений ("Задачи"), а мне нужно JOIN двух измерений таблицы в одну таблицу фактов), но мне это не удалось.


oracle aggregate hierarchical
person mirek    schedule 10.09.2010    source источник

Ответы (2)


arrow_upward
2
arrow_downward

«Некоторая сумма» не очень описательная, поэтому я не понимаю, зачем вам вообще нужно CONNECT BY.

SELECT  dim1.name, dim2.name, x
FROM    (
        SELECT  id1, id2, SUM(x) AS x
        FROM    facts
        GROUP BY
                id1, id2
        ) f
JOIN    dim1
ON      dim1.id = f.id1
JOIN    dim2
ON      dim2.id = f.id2
person Quassnoi    schedule 10.09.2010

arrow_upward
0
arrow_downward

Я думаю, что вы пытаетесь получить сумму значений в таблице фактов для всех дочерних элементов указанных строк, сгруппированных по самым верхним дочерним элементам. Это будет означать, что в приведенном выше примере результаты для первой строки будут представлять собой сумму любых пересечений (DIM1-A-A, DIM1-A-A-A, DIM1-A-A-B) и (DIM2-A-A, DIM2-A-A-A, DIM2-A-A-B, DIM3 -A-A-C), найденные в таблице FACTS. С этим предположением я пришел к следующему решению:

SELECT root_name1, root_name2, SUM(X)
FROM ( SELECT CONNECT_BY_ROOT(name1) AS root_name,
             id1
         FROM dim1
      CONNECT BY parent1 = PRIOR id1
      START WITH parent1 = 2) d1
     CROSS JOIN
     ( SELECT CONNECT_BY_ROOT(name2) AS root_name,
              id2
         FROM dim2
      CONNECT BY parent2 = PRIOR id2
      START WITH parent2 = 2) d2
     LEFT OUTER JOIN
     facts
     ON     d1.id1 = facts.id1
        AND d2.id2 = facts.id2
GROUP BY root_name1, root_name2

(Это также предполагает, что столбцы FACTS имеют имена ID1, ID2 и X.)

person Allan    schedule 10.09.2010