SQL Server实现最短路径的搜索算法

数据库 SQL Server 数据库运维 算法
在表RelationGraph中,有三个字段(ID,Node,RelatedNode),其中Node和RelatedNode两个字段描述两个节点的连接关系;现在要求,找出从节点"p"至节点"j",最短路径(即经过的节点最少)。

这是去年的问题了,今天在整理邮件的时候才发现这个问题,感觉顶有意思的,特记录下来。

图1.

解析

为了能够更好的描述表RelationGraph中字段Node和 RelatedNode的关系,我在这里特意使用一个图形来描述,如图2.

图2.

在图2,可清晰的看出各个节点直接如何相连,也可以清楚的看出节点"p"至节点"j"的的几种可能路径。

从上面可以看出第2种可能路径,经过的节点最少。

为了解决开始的问题,我参考了两种方法,

第1方法是,

参考单源最短路径算法:Dijkstra(迪杰斯特拉)算法,主要特点是以起始点为中心向外层层扩展,直到扩展到终点为止。

图3.

第2方法是,

针对第1种方法的改进,就是采用多源点方法,这里就是以节点"p"和节点"j"为中心向外层扩展,直到两圆外切点,如图4. :

图4.

实现

在接下来,我就描述在SQL Server中,如何实现。当然我这里采用的前面说的第2种方法,以"P"和"J"为始点像中心外层层扩展。

这里提供有表RelactionGraph的create& Insert数据的脚本:

  1. use TestDB     
  2. go 
  3. if object_id('RelactionGraph') Is not null drop table RelactionGraph 
  4. create table RelactionGraph(ID int identity,Item nvarchar(50),RelactionItemnvarchar(20),constraint PK_RelactionGraph primary key(ID)) 
  5. go 
  6. create nonclustered index IX_RelactionGraph_Item on RelactionGraph(Item)include(RelactionItem) 
  7. create nonclustered index IX_RelactionGraph_RelactionItem on RelactionGraph(RelactionItem)include(Item) 
  8. go 
  9. insert into RelactionGraph (Item, RelactionItem ) values 
  10.     ('a','b'),('a','c'),('a','d'),('a','e'), 
  11.     ('b','f'),('b','g'),('b','h'), 
  12.     ('c','i'),('c','j'), 
  13.     ('f','k'),('f','l'), 
  14.     ('k','o'),('k','p'), 
  15.     ('o','i'),('o','l'
  16. go 

编写一个存储过程up_GetPath

  1. use TestDB 
  2. go 
  3. exec dbo.up_GetPath 
  4.         @Node = 'p'
  5. @RelatedNode = 'j' 
  6. go 

上面的存储过程,主要分为两大部分,第1部分是实现如何搜索,第2部分实现如何构造返回结果。其中第1部分的代码根据前面的方法2,通过@Node 和 @RelatedNode 两个节点向外层搜索,每次搜索返回的节点都保存至临时表#1和#2,再判断临时表#1和#2有没有出现切点,如果出现就说明已找到最短的路径(经过多节点数最少),否则就继续循环搜索,直到循环至***的搜索深度(@MaxLevel smallint=100)或找到切点。要是到100层都没搜索到切点,将放弃搜索。这里使用***可搜索深度@MaxLevel,目的是控制由于数据量大可能会导致性能差,因为在这里数据量与搜索性能成反比。代码中还说到一个正向和反向搜索,主要是相对Node 和 RelatedNode来说,它们两者互为参照对象,进行向外搜索使用。

下面是存储过程的执行:

 

你可以根据需要来,赋予@Node 和 @RelatedNode不同的值。

  1. use TestDB 
  2. go 
  3. --Procedure: 
  4. if object_id('up_GetPath') Is not null 
  5.     Drop proc up_GetPath 
  6. go 
  7. create proc up_GetPath 
  8.     @Node nvarchar(50), 
  9.     @RelatedNode nvarchar(50) 
  10. As 
  11. set nocount on 
  12. declare 
  13.     @level smallint =1, --当前搜索的深度 
  14.     @MaxLevel smallint=100, --***可搜索深度 
  15.     @Node_WhileFlag bit=1, --以@Node作为中心进行搜索时候,作为能否循环搜索的标记 
  16.     @RelatedNode_WhileFlag bit=1 --以@RelatedNode作为中心进行搜索时候,作为能否循环搜索的标记 
  17. --如果直接找到两个Node存在直接关系就直接返回 
  18. if Exists(select 1 from RelationGraph where (Node=@Node And RelatedNode=@RelatedNode) 
  19. or(Node=@RelatedNode And RelatedNode=@Node) ) or @Node=@RelatedNode 
  20. begin 
  21.     select convert(nvarchar(2000),@Node + ' --> '+ @RelatedNode) AsRelationGraphPath,convert(smallint,0) As StopCount 
  22.     return 
  23. end 
  24. -- 
  25. if object_id('tempdb..#1') Is not null Drop Table #1 --临时表#1,存储的是以@Node作为中心向外扩展的各节点数据 
  26. if object_id('tempdb..#2') Is not null Drop Table #2 --临时表#2,存储的是以@RelatedNode作为中心向外扩展的各节点数据 
  27. create table #1( 
  28.     Node nvarchar(50),--相对源点 
  29.     RelatedNode nvarchar(50), --相对目标 
  30.     Level smallint --深度 
  31.     ) 
  32. create table #2(Node nvarchar(50),RelatedNode nvarchar(50),Level smallint) 
  33. insert into #1 ( Node, RelatedNode, Level ) 
  34.     select Node, RelatedNode, @level from RelationGraph a where a.Node =@Node union --正向:以@Node作为源查询 
  35.     select RelatedNode, Node, @level from RelationGraph a where a.RelatedNode = @Node --反向:以@Node作为目标进行查询 
  36. set @Node_WhileFlag=sign(@@rowcount) 
  37. insert into #2 ( Node, RelatedNode, Level ) 
  38.     select Node, RelatedNode, @level from RelationGraph a where a.Node =@RelatedNode union --正向:以@RelatedNode作为源查询 
  39.     select RelatedNode, Node, @level from RelationGraph a where a.RelatedNode = @RelatedNode--反向:以@RelatedNode作为目标进行查询 
  40. set @RelatedNode_WhileFlag=sign(@@rowcount) 
  41. --如果在表RelationGraph中找不到@Node 或 @RelatedNode 数据,就直接跳过后面的While过程 
  42. if not exists(select 1 from #1) or not exists(select 1 from #2) 
  43. begin 
  44.     goto While_Out 
  45. end 
  46. while not exists(select 1 from #1 a inner join #2 b on b.RelatedNode=a.RelatedNode) --判断是否出现切点 
  47.      and (@Node_WhileFlag|@RelatedNode_WhileFlag)>0 --判断是否能搜索 
  48.      And @level<@MaxLevel --控制深度 
  49. begin 
  50.     if @Node_WhileFlag >0 
  51.     begin     
  52.         insert into #1 ( Node, RelatedNode, Level ) 
  53.             --正向 
  54.             select a.Node,a.RelatedNode,@level+1 
  55.                 From RelationGraph a 
  56.                 where exists(select 1 from #1 where RelatedNode=a.Node And Level=@level) And 
  57.                     Not exists(select 1 from #1 where Node=a.Node)             
  58.             union 
  59.             --反向 
  60.             select a.RelatedNode,a.Node,@level+1 
  61.                 From RelationGraph a 
  62.                 where exists(select 1 from #1 where RelatedNode=a.RelatedNode AndLevel=@level) And 
  63.                     Not exists(select 1 from #1 where Node=a.RelatedNode) 
  64.         set @Node_WhileFlag=sign(@@rowcount) 
  65.    end 
  66.     if @RelatedNode_WhileFlag >0 
  67.     begin         
  68.         insert into #2 ( Node, RelatedNode, Level ) 
  69.            --正向 
  70.             select a.Node,a.RelatedNode,@level+1 
  71.                 From RelationGraph a 
  72.                 where exists(select 1 from #2 where RelatedNode=a.Node And Level=@level) And 
  73.                     Not exists(select 1 from #2 where Node=a.Node) 
  74.             union 
  75.             --反向 
  76.             select a.RelatedNode,a.Node,@level+1 
  77.                 From RelationGraph a 
  78.                 where exists(select 1 from #2 where RelatedNode=a.RelatedNode AndLevel=@level) And 
  79.                     Not exists(select 1 from #2 where Node=a.RelatedNode) 
  80.         set @RelatedNode_WhileFlag=sign(@@rowcount) 
  81.   end 
  82.     select @level+=1 
  83. end 
  84. While_Out: 
  85. --下面是构造返回的结果路径 
  86. if object_id('tempdb..#Path1') Is not null Drop Table #Path1 
  87. if object_id('tempdb..#Path2') Is not null Drop Table #Path2 
  88. ;with cte_path1 As 
  89. select a.Node,a.RelatedNode,Level,convert(nvarchar(2000),a.Node+' -> '+a.RelatedNode) AsRelationGraphPath,Convert(smallint,1) As PathLevel 
  90. From #1 a where exists(select 1 from #2where RelatedNode=a.RelatedNode) 
  91. union all 
  92. select b.Node,a.RelatedNode,b.Level,convert(nvarchar(2000),b.Node+' -> '+a.RelationGraphPath) As RelationGraphPath ,Convert(smallint,a.PathLevel+1) 
  93. As PathLevel 
  94.     from cte_path1 a 
  95.         inner join #1 b on b.RelatedNode=a.Node 
  96.             and b.Level=a.Level-1 
  97. select * Into #Path1 from cte_path1 
  98. ;with cte_path2 As 
  99. select a.Node,a.RelatedNode,Level,convert(nvarchar(2000),a.Node) AsRelationGraphPath,Convert(smallint,1) As PathLevel 
  100. From #2 a where exists(select 1 from #1where RelatedNode=a.RelatedNode) 
  101. union all 
  102. select b.Node,a.RelatedNode,b.Level,convert(nvarchar(2000),a.RelationGraphPath+' -> '+b.Node) As RelationGraphPath ,Convert(smallint,a.PathLevel+1) 
  103.     from cte_path2 a 
  104.         inner join #2 b on b.RelatedNode=a.Node 
  105.             and b.Level=a.Level-1 
  106. select * Into #Path2 from cte_path2 
  107. ;with cte_result As 
  108. select a.RelationGraphPath+' -> '+b.RelationGraphPath AsRelationGraphPath,a.PathLevel+b.PathLevel -1 
  109. As StopCount,rank() over(order bya.PathLevel+b.PathLevel) As Result_row 
  110.     From #Path1 a 
  111.         inner join #Path2 b on b.RelatedNode=a.RelatedNode 
  112.             and b.Level=1 
  113.     where a.Level=1 
  114. )     
  115. select distinct RelationGraphPath,StopCount From cte_result where Result_row=1 
  116. go 

扩展

前面的例子,可扩展至城市的公交路线,提供两个站点,搜索经过这两个站点最少站点公交路线;可以扩展至社区的人际关系的搜索,如一个人与另一个人想认识,那么他们直接要经过多少个人才可以。除了人与人直接有直接的朋友、亲戚关联,还可以通过人与物有关联找到人与人关联,如几个作家通过出版一个本,那么就说明这几个人可以通过某一本书的作者列表中找到他们存在共同出版书籍的关联,这为搜索两个人认识路径提供参考。这问题可能会非常大复杂,但可以这样的扩展。

小结

这里只是找两个节点的所有路径中,节点数最少的路径,在实际的应用中,可能会碰到比这里更复杂的情况。在其他的环境或场景可能会带有长度,时间,多节点,多作用域等一些信息。无论如何,一般都要参考一些原理,算法来实现。

原文链接:http://www.cnblogs.com/wghao/archive/2013/04/23/3036965.html

【编辑推荐】

 

责任编辑:彭凡 来源: 博客园
相关推荐

2011-05-17 13:58:37

最短路径

2011-12-19 12:39:37

Java

2021-05-10 08:07:40

图算法路径顶点

2023-05-30 07:58:01

谷歌搜索算法

2021-03-10 09:50:15

算法Dijkstra短路问题

2018-10-12 15:15:45

电商搜索算法

2012-02-29 13:32:28

Java

2021-08-26 17:36:42

Floyd算法数据结构

2015-07-16 14:25:56

SDN网络感知服务

2015-12-07 17:07:36

SDN网络流量

2011-06-01 09:27:00

OSPF路由路由器

2019-03-29 09:40:38

数据结构算法前端

2012-08-24 09:16:53

App Store

2019-10-29 15:22:24

Google算法搜索

2021-09-04 23:40:53

算法程序员前端

2022-09-24 09:03:55

前端单元测试冒泡排序

2014-03-26 09:04:42

算法Floyd最短算法

2011-04-11 16:32:28

路径C++

2023-02-09 07:39:01

2021-11-10 09:17:18

程序员排序算法搜索算法
点赞
收藏

51CTO技术栈公众号