用SQL解決有向圖問題

一個常見的高階電腦科學問題可以在“有向圖”的範疇之下描述。有向圖是由一組向量和邊所連線的一組有限的節點。例如，一個節點可以想象為一座“城市”，而每個向量可以想象為兩座城市間的一個“航線”。

有很多演算法和論文講到如何解決每種可能路線的遍歷問題以及尋找最短路徑或者最小代價路徑的問題。這些演算法中大部分都是過程化的，或者是使用遞迴方面來解決的。然而 SQL 的宣告性語言使得解決複雜的有向圖問題更加容易，而且不需要很多程式碼。

讓我們以兩座城市之間的航線為例子，建立一個表儲存一些假想資料：

(
code char(3) constraint airports_pk primary key,
description varchar2(200)
);

insert into airports values ('LHR','London Heathrow, UK');
insert into airports values ('JFK','New York-Kennedy, USA');
insert into airports values ('GRU','Sao Paulo, Brazil');

create table fares
(
depart char(3),
arrive char(3),
price number,
constraint fares_pk primary key (depart,arrive),
constraint fares_depart_fk foreign key (depart) references airports,
constraint fares_arrive_fk foreign key (arrive) references airports
);

insert into fares values('LHR','JFK',700);
insert into fares values('JFK','GRU',600);
insert into fares values('LHR','GRU',1500);
insert into fares values('GRU','LHR',1600);

不能使用CONNECT BY 語法來解決如何從倫敦到聖保羅，因為在圖中有資料產生一個環（從聖保羅飛回）：

ERROR:
ORA-01436: CONNECT BY loop in user data

要解決有向圖問題，我們需要建立一個臨時表來儲存兩個節點之間所有可能的路徑。我們必須注意不復制已經處理過的路徑，而且在這種情況下，我們不想路徑走回開始處的同一個地點。我還希望跟蹤到達目的地所需航程的數目，以及所走路線的描述。

臨時表使用以下指令碼建立：

(
depart char(3),
arrive char(3),
hops integer,
route varchar2(30),
price number,
constraint faretemp_pk primary key (depart,arrive)
);

一個簡單的檢視可以在稍微簡化這個例子中使用的程式碼。檢視可以根據 fares 表中的單個航程計算從 faretemp 表中的一個路徑到達一下一個航程的資料：

as
select src.depart,
dst.arrive,
src.hops+1 hops,
src.route||','||dst.arrive route,
src.price + dst.price price
from faretemp src,fares dst
where src.arrive = dst.depart
and dst.arrive != src.depart;
/
show errors;

這 個演算法相當簡單。首先，使用 fares 表中的資料填充 faretemp 表，作為初始的航程。然後，取到我們剛才插入的所有資料，使用它們建立所有可能的二航程（two-hop）路徑。重複這一過程，直至在兩個節點之間建立了 新路徑。迴圈過程將在節點間所有可能的路徑都被描述之後退出。如果我們只對某個開始條件感興趣，那麼我們還可以限制第一次的插入從而減少裝載資料的量。下 面是發現路徑的程式碼：

begin
-- initial connections
insert into faretemp
select depart,arrive,1,depart||','||arrive,price from fares;
while sql%rowcount > 0 loop
insert into faretemp
select depart,arrive,hops,route,price from nexthop
where (depart,arrive)
not in (select depart,arrive from faretemp);
end loop;
end;
/
show errors;

select * from faretemp order by depart,arrive;

可以在表 A 中檢視輸出。

前面的資料有一個小問題。資料是點之間最短路徑（最小航程數）的集合。然而，從倫敦到聖保羅的航程卻不是最便宜的一個。

要解決最便宜的費用問題，需要對我們的迴圈做一個改進，當在一個航程中發現一個更便宜的路線時使用這個路線代替原來的路線。修改後的程式碼如下：

declare
l_count integer;
begin
-- initial connections
insert into faretemp
select depart,arrive,1,depart||','||arrive,price from fares;
l_count := sql%rowcount;
while l_count > 0 loop
update faretemp
set (hops,route,price) =
(select hops,route,price from nexthop
where depart = faretemp.depart
and arrive = faretemp.arrive)
where (depart,arrive) in
(select depart,arrive from nexthop
where price < faretemp.price);
l_count := sql%rowcount;
insert into faretemp
select depart,arrive,hops,route,price from nexthop
where (depart,arrive)
not in (select depart,arrive from faretemp);
l_count := l_count + sql%rowcount;
end loop;
end;
/
show errors;

select * from faretemp order by depart,arrive;

可能在中檢視輸出。

演算法發現LHR、JFK、GRU 路線比 LHR、GRU 路線便宜，所以用前者代替了後者。迴圈將在沒有更便宜的費用，並且沒有其它可能路線時退出。

[@more@]