这篇文章主要给大家介绍了关于PostgreSQL图(graph)的递归查询的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用PostgreSQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧

背景

在树形递归查询这篇文章,我记录了使用CTE语法查询树形结构的办法。在一个树形结构中,每一个节点最多有一个上级,可以有任意个数的下级。

在实际中,我们还会遇到对图(graph)的查询,图和树的最大区别是,图的节点可以有任意个数的上级和下级。如下图所示

因为图可能存在loop结构(上图红色箭头),所以在使用CTE递归的过程中,必须要破环(break loop),否则算法就会进入无限递归,永不结束。

存储和查询图结构,目前当红数据库是neo4j,但是当数据量只有十几万条的时候,PostgreSQL完全可以胜任。

构造样本数据

-- 每一条有向关系边都存在上游,下游两个节点
drop table if exists demo.t_rel;
create table if not exists demo.t_rel(up int , down int);

-- 唯一约束,避免插入相同的关系
alter table demo.t_rel add constraint udx_t_rel unique (up, down);

insert into demo.t_rel values(6,5),(3,7),(5,1),(1,2),(5,2),(5,7),(7,2),(2,4),(7,4);

-- 构造一条环数据,7-2-4-7
delete from demo.t_rel where up=4 and down=7;
insert into demo.t_rel values(4,7);

递归查询

指定节点的下级

常见的一个是,给定一个节点,查询这个节点的所有下级节点和路径。使用破环的算法关键如下

    使用数组保存当前的路径信息。 计算下一个节点之前,判断该节点是否已经存在于路径上。如果是,就说明该点是环的起点,必须排除这个节点来达到破环的效果。 起始节点和最大深度,都是可选的。如果忽略这两个条件,就会返回完整的图信息。
with recursive 
downstream as
(
	select 1 as lvl, r.up, r.down, 
			-- 保存当前路径
			array[]::int[] || r.up || r.down as trace 
		from demo.t_rel r  
	where r.up = 7 -- 指定起点
	union all
	select ds.lvl +1, r.up, r.down, ds.trace || r.down
		from demo.t_rel r , downstream ds 
	where r.up = ds.down
		-- 破环
		and not r.down = any(ds.trace)
		and ds.lvl < 20 -- 最大深度
)
select * from downstream ds;

上面以节点7为开始,返回下级的所有节点和路径信息,如下。

-- 可以看到并没有包括7-2-4-7这条环。
 lvl | up | down | trace
-----+----+------+---------
 1 | 7 | 2 | {7,2}
 1 | 7 | 4 | {7,4}
 2 | 2 | 4 | {7,2,4}
(3 rows)

指定节点的所有关联

在社交网络的中,我们根据一个特定的节点,查询所有的关系网。在本文的样本数据中,我们的需求就变成,同时查询指定节点的所有上级和下级。

为了方便后面的测试,我们封装一个函数

drop function if exists f_get_rel;

/*
取得某个节点的相关联节点,和路径信息。
@start_node 起始节点。
@direct_flag 查询方向,-1:查找上级;1:查找下级; 0:查找上下级;
@max_depth 递归深度,即查找最多几级关系。
*/
create or replace function f_get_rel(start_node int, direct_flag int=1, max_depth int=20) 
	returns table (direct int, cur_depth int, up_node int, down_node int, trace int[])
as $$
begin

	return query 
		with recursive 
		downstream as
		(
			select 1 as lvl, r.up, r.down, array[]::int[] || r.up || r.down as trace 
				from demo.t_rel r 
			where r.up = start_node
				and direct_flag in (0, 1)
			union all
			select ds.lvl +1, r.up, r.down, ds.trace || r.down
				from demo.t_rel r , downstream ds 
			where r.up = ds.down
				and not r.down = any(ds.trace)
				and ds.lvl < max_depth
		),
		upstream as
		(
			select 1 as lvl, r.up, r.down, array[]::int[] || r.up || r.down as trace 
				from demo.t_rel r 
			where r.down = start_node
				and direct_flag in (0, -1)
			union all
			select us.lvl +1, r.up, r.down, r.up || us.trace 
				from demo.t_rel r , upstream us 
			where r.down = us.up
				and not r.up = any(us.trace)
				and us.lvl < max_depth
		)
		select -1, us.* from upstream us 
			union all 
		select 1, ds.* from downstream ds
		order by 1 desc, lvl, up, down
	;

end;
$$ language plpgsql strict;

测试一下,查询节点7的所有3度关联节点信息,如下

dap=# select * from demo.f_get_rel(7,0,3);
 direct | cur_depth | up_node | down_node | trace
--------+-----------+---------+-----------+-----------
  1 |   1 |  7 |   2 | {7,2}
  1 |   1 |  7 |   4 | {7,4}
  1 |   2 |  2 |   4 | {7,2,4}
  -1 |   1 |  3 |   7 | {3,7}
  -1 |   1 |  4 |   7 | {4,7}
  -1 |   1 |  5 |   7 | {5,7}
  -1 |   2 |  2 |   4 | {2,4,7}
  -1 |   2 |  6 |   5 | {6,5,7}
  -1 |   3 |  1 |   2 | {1,2,4,7}
  -1 |   3 |  5 |   2 | {5,2,4,7}
(10 rows)

图形显示结果

ECharts模板

在没有集成图形界面之前,使用ECharts的示例代码(地址),可以直观的查看关系图谱。对官方样表进行微调之后,代码如下
注意 代码中的 data 和 links 部分需要进行替换

option = {
 title: {
  text: '数据图谱'
 },
 tooltip: {},
 animationDurationUpdate: 1500,
 animationEasingUpdate: 'quinticInOut',
 series : [
  {
   type: 'graph',
   layout: 'force',
   force: {
     repulsion: 1000
    },
   focusNodeAdjacency: true,
   symbolSize: 30,
   roam: true,
   label: {
    normal: {
     show: true
    }
   },
   edgeSymbol: ['circle', 'arrow'],
   edgeSymbolSize: [4, 10],
   edgeLabel: {
    normal: {
     textStyle: {
      fontSize: 20
     }
    }
   },
   data: [
    { name:"2", draggable: true, symbolSize:20},
   ],
   links: [
    { source:"2", target:"4"},
   ],

  }
 ]
};

造显示用数据

构造 data 部分

-- 根据节点的关联点数量,设置图形大小
with rel as (select * from f_get_rel(7,0,2)),
	up_nodes as (select up_node, count(distinct down_node) as out_cnt from rel group by up_node),
	down_nodes as (select down_node, count(distinct up_node) as in_cnt from rel group by down_node),
	node_cnt as ( select up_node as node, out_cnt as cnt from up_nodes union all select * from down_nodes )
select '{ name:"' || n.node || '", draggable: true, symbolSize:' || sum(n.cnt) * 10 || '},' as node
	from node_cnt n
group by n.node
order by 1;

构造 links 部分

select distinct r.up_node, r.down_node, '{ source:"'|| r.up_node ||'", target:"'|| r.down_node ||'"},' as links 
	from f_get_rel(7,0,3) r
order by r.up_node	;

图形显示

把构造的data和links替换到ECharts代码里面

查询节点7的所有2度关联节点信息,结果显示如下

查询节点7的所有关联节点信息(不限层级数),结果显示如下

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对爱安网的支持。

最新资讯
中概股阳光动力的泡沫:四天暴涨50% 故事有了新“套路”

中概股阳光动力的泡沫

阳光动力的真实经营状况难以为外界所知,叠加实控人彭小
华为将整合汽车和消费者业务 再度重申不造车

华为将整合汽车和消费

这意味着,汽车BU在华为公司的架构中平移到了另一个板块
走过爆发和冷却 动力电池再生产行业进入成熟发展期

走过爆发和冷却 动力

动力电池循环产业的发展进程并不全与新能源汽车发展的
图书编辑控诉粉圈控评:流量造假的祸害已“出圈”

图书编辑控诉粉圈控评

在社交媒体常见的养号与控评现象,蔓延到书籍点评领域,让
面部识别技术法律缺口亟待补上

面部识别技术法律缺口

随着人脸识别等新兴技术的普及并走进万千大众的生活,围
国内第三代半导体迎窗口期 今年氮化镓、碳化硅产值或达70亿元

国内第三代半导体迎窗

据充电头网统计,在智能手机行业中,目前已有华为、小米、
最新文章
PostgreSQL基础知识之SQL操作符实践指南

PostgreSQL基础知识之

这篇文章主要给大家介绍了关于PostgreSQL基础知识之SQ
pgsql查询优化之模糊查询实例详解

pgsql查询优化之模糊

这篇文章主要给大家介绍了关于pgsql查询优化之模糊查
Ubuntu PostgreSQL安装和配置的介绍

Ubuntu PostgreSQL安

今天小编就为大家分享一篇关于Ubuntu PostgreSQL安装
PostgreSQL实现一个通用标签系统

PostgreSQL实现一个通

这篇文章主要给大家介绍了关于利用PostgreSQL实现一个
PostgreSQL中使用数组改进性能实例代码

PostgreSQL中使用数组

这篇文章主要给大家介绍了关于PostgreSQL中使用数组改
Postgresql主从异步流复制方案的深入探究

Postgresql主从异步流

这篇文章主要给大家介绍了关于Postgresql主从异步流复