Oracle 带休假属性计数的分层查询

各位专家好。我想计算分层查询中leaves属性计数的总和

create table hq_test (parent_id NUMBER, child_id NUMBER);

INSERT INTO hq_test (parent_id, child_id)  VALUES (25,26);
INSERT INTO hq_test (parent_id, child_id)  VALUES (25,27); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (26,28); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (26,29); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (26,30); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (27,31); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (27,32); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (27,33); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (30,34); 
INSERT INTO hq_test (parent_id, child_id)  VALUES (NULL,25);  

create table hq_test_attr (id NUMBER, val VARCHAR2(100));

INSERT INTO hq_test_attr (id, val) VALUES (26, 'A');
INSERT INTO hq_test_attr (id, val) VALUES (26, 'B');
INSERT INTO hq_test_attr (id, val) VALUES (27, 'C');
INSERT INTO hq_test_attr (id, val) VALUES (27, 'D');
INSERT INTO hq_test_attr (id, val) VALUES (28, 'E');
INSERT INTO hq_test_attr (id, val) VALUES (28, 'F');
INSERT INTO hq_test_attr (id, val) VALUES (28, 'G');
INSERT INTO hq_test_attr (id, val) VALUES (29, 'H');
INSERT INTO hq_test_attr (id, val) VALUES (30, 'I');
INSERT INTO hq_test_attr (id, val) VALUES (31, 'J');
INSERT INTO hq_test_attr (id, val) VALUES (31, 'K');
INSERT INTO hq_test_attr (id, val) VALUES (32, 'L');
INSERT INTO hq_test_attr (id, val) VALUES (33, 'M');
INSERT INTO hq_test_attr (id, val) VALUES (33, 'N');
INSERT INTO hq_test_attr (id, val) VALUES (33, 'O');
复制

我想获取每个id的输出，其中包含其属性的计数以及在整个层次结构中行走的其叶子属性的总计数。例如: 33有3个属性 (M，N和O)，但没有叶子; 27的属性计数为2，叶子属性计数为6 (属性计数为31、32和33)。
这可以通过标量子查询来实现，但效果很差。这可以w/ sql吗？

Rgds
科恩

所以你想要每个节点下面的子树中所有属性的总和，包括它自己？

在处理树木时，我发现有一个图表显示你想要的东西是有帮助的。所以我们开始:



方框中的数字是节点ID，后跟其属性的编号 (在括号中)。

每个框上方的数字是其下方子树中所有属性的总和。

但是我们如何计算呢？

首先，使用深度优先搜索为每个节点分配一个序列号。在我们的图中每个框的左侧显示这些显示:



要获得节点下方的总数，您需要将其本身的值累加到树中相同级别或更低级别的下一个节点，但不包括该节点。

例如，节点26在搜索中是第二。您需要添加它和27之间的所有属性，这是7。所以它的总数是节点2和6之间的一切。

这就是概念。让我们把它翻译成SQL。

首先你需要计算每个节点有多少属性。这是一个标准的外部联接 (某些节点没有属性) 和分组:

  select h.parent_id, h.child_id, count(a.id) c
  from   hq_test h
  left   join hq_test_attr a
  on     h.child_id = a.id
  group  by h.parent_id, h.child_id;
复制

接下来你需要建造这棵树。我已经做到了这一点使用递归与这使您能够指定深度优先搜索 (默认值)，并按照访问它们的顺序为每个节点分配序列号:

tree (par, chd, c, tot, pth, lev) as (
  select parent_id, child_id, c, c,
         child_id || '',
         1 lev
  from   counts c
  where  parent_id is null
  union all
  select c.parent_id, c.child_id, c.c, 0 ,
         pth || '|' || c.child_id,
         lev + 1 lev
  from   tree t
  join   counts c
  on     t.chd = c.parent_id
) search depth first by chd set seq
复制

这就是有趣的地方。您希望 “期待” 到下一个节点，其中级别 <= 当前级别。您可以使用子查询来找到它，例如:

select min(s.seq) from tree s
where  s.seq > t.seq 
and    s.lev <= t.lev
复制

但是此查询将针对树中的每个节点运行。这可能不会很好地扩展。

幸运的是你在12摄氏度。所以模式匹配可以来拯救!:)

要使用它，请将子树变量定义为级别大于匹配中第一行的行:

pattern ( strt subtree* )
define 
  subtree as lev > first (lev)
复制

您需要为表格中的每一行执行此操作。但是默认情况下，match_recognize完成匹配后，转到数据集中的下一行。这意味着它将从根开始，遍历树一次，然后停止!

要解决此问题，您需要 “返回” 第二行。第三次，第四次，等等。为此，请添加:

after match skip to next row
复制

这使它能够查找每行的所有子树。

要完成所有工作，您需要做的是在 “度量” 子句中定义输出中所需的列。要获得总属性，请将所有计数求和。对于您希望看到的任何其他内容，例如父级，子级等，返回每一列的第一 ()。

把它们放在一起，你会得到:

with counts as (
  select h.parent_id, h.child_id, count(a.id) c
  from   hq_test h
  left   join hq_test_attr a
  on     h.child_id = a.id
  group  by h.parent_id, h.child_id
), tree (par, chd, c, tot, pth, lev) as (
  select parent_id, child_id, c, c,
         child_id || '',
         1 lev
  from   counts c
  where  parent_id is null
  union all
  select c.parent_id, c.child_id, c.c, 0 ,
         pth || '|' || c.child_id,
         lev + 1 lev
  from   tree t
  join   counts c
  on     t.chd = c.parent_id
) search depth first by chd set seq
  select * from tree
  match_recognize (
    order by seq
    measures
      first(chd) as child_id,
      first(par) as parent_id,
      first(pth) as pth,
      first (lev) as lev,
      first(c) as attrs,
      sum(c) as subtot,
      first(seq) as seq
    after match skip to next row
    pattern ( strt subtree* )
    define 
      subtree as lev > first (lev)
  )
  order  by seq;

CHILD_ID   PARENT_ID   PTH           LEV   ATTRS   SUBTOT   SEQ   
        25             25                1       0       15     1 
        26          25 25|26             2       2        7     2 
        28          26 25|26|28          3       3        3     3 
        29          26 25|26|29          3       1        1     4 
        30          26 25|26|30          3       1        1     5 
        34          30 25|26|30|34       4       0        0     6 
        27          25 25|27             2       2        8     7 
        31          27 25|27|31          3       2        2     8 
        32          27 25|27|32          3       1        1     9 
        33          27 25|27|33          3       3        3    10 
复制