pgsql-tree递归聚合计算

创建表并构造测试数据

–create table
CREATE TABLE “public”.“orgin_table” (
“id” int8 NOT NULL,
“pid” int8 NOT NULL,
“value” int4 NOT NULL,
“level” int4 NOT NULL,
PRIMARY KEY (“id”)
);
–构造数据,构造5级数据
insert into orgin_table select 0 as id,-1 as pid,floor(random()*20::integer) as value,0 as level;
insert into orgin_table select generate_series(1,10) as id,0 as pid,floor(random()*20::integer) as value,1 as level;
insert into orgin_table select generate_series(11,200) as id,1+floor(random()*9::integer) as pid,floor(random()*20::integer) as value,2 as level;
insert into orgin_table select generate_series(201,3000) as id,11+floor(random()*189::integer) as pid,floor(random()*20::integer) as value,3 as level;
insert into orgin_table select generate_series(3001,10000) as id,200+floor(random()*2800::integer) as pid,floor(random()*20::integer) as value,4 as level;

1、递归计算每一个节点的value并汇总

with recursive target_table as (
select * from orgin_table
union all
select o.id,o.pid,t.value,o.level from target_table t join
orgin_table o on t.pid=o.id
)
SELECT id,sum(value) from target_table group by id order by id;
测试结果：
最高级计算
select sum(value) from orgin_table;

使用递归后结果

2、递归计算路径

–从节点点开始
with recursive target_table as (
select id,pid,value,level,id::text as path from orgin_table where pid=’-1’
union all
select o.id,o.pid,o.value,o.level,(t.path||’>’||o.id)::text as path from target_table t join
orgin_table o on o.pid=t.id
)
–测试结果
SELECT * from target_table order by pid,id;