每个级别的ltree子值之和

Question

我有一个带有ltree路径和名称列的properties表。Ltree路径包含父节点的ids，即"7968.7969.7987.8000“。每个树节点都有一些数值的reports。我需要为ltree中的每个节点找到子节点值的和。圆括号中的路径

        (1) A 
          |
     __________________________________
     |                |                |
(1.2)B            (1.3)C            (1.4)D
|
|________________________
        |                |
(1.2.5) E         (1.2.6) F

报告

property_id | value
    1       |   1 
    2       |   4
    3       |   19
    4       |   21
    5       |   9
    6       |   11

我需要找到像

full_path | subtree_sum
   A      |     60     (1 + 4 + 19 + 21 + 9 + 11)
   A.B    |     24     (4 + 9 + 11)  
   A.C    |     19     (19)
   A.D    |     21     (21)
   A.B.E  |     9      (9)
   A.B.F  |     11     (11)

Answer 1

给你：

select 
    p.path,
    sum(r.value)
from properties p
left join properties sub on sub.path::text like p.path::text||'%'
left join reports r on r.property_id=sub.id
group by 1; 

它怎麽工作?

对于在我们的查询p中调用的每个节点，我们通过加入sub来检索它的所有子节点(包括它本身)。为了加入，我们使用了like运算符，它允许我们使用p路径作为前缀。快速示例将使您对like运算符有一些了解(%是通配符)：

select 'prefix1' like 'prefix1%'; --true
select 'prefix1.something' like 'prefix1%'; --true
select 'prefix2' like 'prefix1%'; --false
select 'prefix2.something' like 'prefix1%'; --false

最后一步是将每个子节点的值连接起来，并将其加起来并按第一列分组。

编辑：

我受过一点教育，找到了更好的解决办法：

select 
    p.path,
    sum(r.value)
from properties p
left join properties sub on sub.path <@ p.path
left join reports r on r.property_id=sub.id
group by 1; 

因为<@操作符使用了现有的GiST索引，所以它更好。