KingbaseES的SQL语句-CTE递归

背景

从上下级关系表中，任意一个节点数据出发，可以获得该节点的上级或下级。CTE的递归语法，或者 connect by 与 start with的 查询语法，能够实现这个需求。

当我们需要制作上下级关系的数据时，也可以使用CTE的递归语法。

举例

一个父节点拥有随机数量的子节点，通过控制层级数和随机数的上限，可以实现海量的数据集合。

drop table t_level1;
create table t_level1
(
    id  int primary key,
    pid int
);

create index t_level1_pid on t_level1 (pid);

--Sequence For Primary key
create sequence seq_level1;

select count(*) from t_level1;
truncate t_level1;

--方法一：使用表作为record type，生成record数组
with recursive rec as (
    --Root Node
    select array_agg((id, pid)::t_level3) as recs,
           5                              as max_lvl, --最大level
           5                              as rec_cnt  --最大子节点数
    from (select nextval('seq_level1') as id, null as pid) t
    union all
    --Children Node, Random Number
    select array_agg((id, pid)::t_level3) as recs,
           recp.max_lvl                   as max_lvl,
           rec_cnt                        as rec_cnt
    from (select (unnest(rec.recs)).id as pid,
                 rec.max_lvl - 1       as max_lvl,
                 rec_cnt               as rec_cnt
          from rec
          where rec.max_lvl > 1) as recp
       , lateral (select nextval('seq_level1') as id
                  from generate_series(1, (random() * rec_cnt)::int)
                  where pid > 0) t2
    group by pid, max_lvl, rec_cnt
)
select (unnest(recs)).*
from rec;

--方法二：使用主键作为type，生成主键类型数组
with recursive rec as (
    --Root Node
    select array_agg(nextval('seq_level1')) as ids,
           null::bigint                     as pid,
           5                                as max_lvl, --最大level
           5                               as rec_cnt  --最大子节点数 
    union all
    --Children Node, Random Number
    select (select array_agg(nextval('seq_level1')) as ids
            from generate_series(1, (random() * rec_cnt)::int)
           )       as ids,
           pid     as pid,
           max_lvl as max_lvl,
           rec_cnt as rec_cnt
    from (select unnest(rec.ids) as pid,
                 rec.max_lvl - 1 as max_lvl,
                 rec_cnt         as rec_cnt
          from rec
          where rec.max_lvl > 1) as recp
)
select unnest(ids) as id, pid, max_lvl
from rec;