pgsql-tree递归聚合计算

wxnj2008

已于 2022-02-14 16:56:31 修改

阅读量641

点赞数

CC 4.0 BY-SA版权

文章标签：数据库开发

于 2022-02-14 16:49:52 首次发布

本文链接：https://blog.youkuaiyun.com/weixing2008/article/details/122925740

创建表并构造测试数据

–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;