Как создать оракул иерархический запрос, который преобразует разреженные данные в плотные данные

Question

Скажем, у вас есть дерево данных

A
|
+----B
|    |
|    +----C
|
+----D

представлять это в базе данных оракула мы могли бы иметь

create table mydata (id number(10), parent_id number(10), name varchar2(10))

и использовать иерархический запрос для обхода дерева.

Каков наилучший способ определения разреженного набора свойств, чтобы дочерние узлы наследовали значения свойств от ближайшего родительского узла?

В приведенном выше примере, скажем, мы хотим добавить владельца и цвет в дерево - как можно изменить иерархический запрос для предоставления унаследованных значений?

Моей первой мыслью было создать новую таблицу

create table myproperties (id number(10), owner varchar2(30), colour varchar2(30));

Мы бы хотели, чтобы значения в этой таблице также были редкими - поэтому строка в myproperties может указывать "владельца", но оставить "цвет" пустым. Данный узел в mydata будет иметь объединенный набор всех ненулевых родительских свойств.

Затем выполните аналитический запрос, чтобы заполнить пробелы:

select * from (
    select
        id, groupid, parent_id, 
        last_value(owner ignore nulls) over (partition by groupid order by l desc) owner,
        last_value(colour ignore nulls) over (partition by groupid order by l desc) colour
    from
        (
            -- query from leaf to root
            select
                id, connect_by_root id groupid, level l, p.owner, p.colour
            from
                mydata d left outer join myproperties p on p.id=d.id
            start with
                id in (select id from mydata)
            connect by prior
                parent_id=id
        )
)
where
    id=groupid -- filter out redundant rows required by the analytic function

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

Answer 1

Я бы предложил использовать рекурсивный WITH пункт для построения иерархии и использования DENSE_RANK определить соответствующие свойства:

WITH mydata AS
  (SELECT 1 AS id,NULL AS parent_id,'A' AS name FROM DUAL
   UNION ALL SELECT 2,1,'B' FROM DUAL
   UNION ALL SELECT 3,2,'C' FROM DUAL
   UNION ALL SELECT 4,1,'D' FROM DUAL),
myproperties AS
  (SELECT 1 AS id,'me' AS owner, 'red' AS colour FROM DUAL
   UNION ALL SELECT 2,'you','blue' FROM DUAL
   UNION ALL SELECT 3,'all',NULL FROM DUAL
   UNION ALL SELECT 4,NULL,'green' FROM DUAL),
myhierarchy (id,ancestor,depth) AS --recursive
  (SELECT d.id,d.id,0 FROM mydata d
   UNION ALL SELECT d.id,h.id,h.depth+1
               FROM myhierarchy h
                 INNER JOIN mydata d ON d.parent_id = h.id)
SELECT h.id
      ,MAX(p.owner) KEEP (DENSE_RANK FIRST ORDER BY NVL2(p.owner,1,2),h.depth) owner
      ,MAX(p.colour) KEEP (DENSE_RANK FIRST ORDER BY NVL2(p.colour,1,2),h.depth) colour
  FROM myhierarchy h
    INNER JOIN myproperties p ON p.id=h.ancestor
  GROUP BY h.id;

Обновление: я добавил NVL2(*,1,2) в ORDER BY пункт о DENSE_RANK иметь нулевые значения в конце. Значения NULL не будут перезаписывать значения свойств.