探索PostgreSQL 14新特性--SEARCH和CYCLE
PG14的SEARCH和CYCLE新功能大大簡化了遞歸查詢的方式，本文給出一些基于旅行計劃的示例。
建立資料庫
本文示例基于任何PG14或更新版本的資料庫。使用Aiven分支的服務及CLI。下面是建立資料庫的CLI指令：
avn service create holidays-pg \
--service-type pg \
--plan hobbyist \
--cloud google-europe-west3 \
-c pg_version=14 上述在google-europe-west3區建立一個PG14（-c pg_version=14）的服務，名為holidays-pg，并使用最小的hobbyist計劃。足夠我們測試。使用avn service versions指令檢查提供工具的版本。
需要等待幾分鐘以上PG14服務啟動。可以通過以下方式關注服務建立任務的進度：
avn service wait holidays-pg
使用下面指令連接配接到holidays-pg的服務：
avn service cli holidays-pg
建立資料集
我們想要穿越歐洲，在預算範圍内參觀一些注意城市。看看這是之前讨論的背包問題的變體？使用類似的政策可以解決明顯不同的問題總是很有趣。
為存儲我們想要通路的城市，建立一個cities表，并用我們選擇的位置填充。
create table cities(
city_id int primary key,
city_name varchar
);
insert into cities values (0, 'Rome'),
(1, 'London'),
(2, 'Paris'),
(3, 'Helsinki'),
(4, 'Barcelona'); 我們如何在城市之間旅行？很簡單，前往一個旅行預訂網站，找到連接配接以及每次旅行的費用。通常會傳回這樣的圖表：
為了在PG中表示上述資訊，建立一個trips表存儲出發地city_a_id和目的地city_b_id，以及歐洲旅行費用price_in_eur。
create table trips(
trip_id int primary key,
city_a_id int references cities(city_id),
city_b_id int references cities(city_id),
price_in_eur int
); insert into trips values
(1, 0, 1, 200),
(2, 0, 2, 250),
(3, 0, 3, 150),
(4, 1, 0, 120),
(5, 1, 3, 350),
(6, 1, 4, 450),
(7, 2, 0, 170),
(8, 2, 3, 320),
(9, 3, 0, 50),
(10, 3, 4, 120),
(11, 4, 0, 30),
(12, 4, 1, 500); Trips表包含所有可用的路徑以及相關成本。例如，id為2的路徑從Rome（city_id:0）到Paris（city_id:2），成本為250歐元。
計劃行程
我們的旅程需要從某個地方開始，既然知道條條道路通羅馬，可以選擇eternal city作為起點。為了檢視可以去哪裡旅行，可以在cities和trips表做join。
select
src.city_name,
dst.city_name,
trips.price_in_eur
from cities src
join trips on src.city_id = trips.city_a_id
join cities dst on trips.city_b_id = dst.city_id
where src.city_name='Rome'; 結果顯示上圖相同資訊：可以達到London、Paris和Helsinki：
city_name | city_name | price_in_eur
-----------+-----------+--------------
Rome | London | 200
Rome | Paris | 250
Rome | Helsinki | 150
(3 rows) 為旅程添加更多節點
好了，下一步去哪裡？我們可以利用遞歸查詢的強大功能來周遊所有可能的組合。有無限多錢，我們就可以永遠環遊世界。用資料庫術語來翻譯，意味着遞歸查詢可能會查詢無限循環。為了模仿現實生活并使我們免于無限循環，讓我們設定一個800歐元的總體預算來支付我們所有旅行。
可以像這樣編寫旅程的遞歸查詢：
with recursive trip_journey(
city_id,
trip_id,
total_price_in_eur,
journey_stops
)
as (
select
city_id as city_id,
null::int as trip_id,
0 price_in_eur,
ARRAY[city_name] as journey_name
from cities
where city_id=0
UNION ALL
select
trips.city_b_id,
trips.trip_id,
tj.total_price_in_eur + trips.price_in_eur,
tj.journey_stops || city_b.city_name
from trip_journey tj join trips on tj.city_id = trips.city_a_id
join cities city_a on trips.city_a_id = city_a.city_id
join cities city_b on trips.city_b_id = city_b.city_id
where tj.total_price_in_eur + trips.price_in_eur < 800
)
select * from trip_journey; 讓我們分解下。第一部分陳述起點：我們要從Rome(city_id=0)開始。如果不旅行那麼trip_id是null，總成本是0.
select
city_id as city_id,
null::int as trip_id,
0 price_in_eur,
ARRAY[city_name] as journey_name
from cities
where city_id=0 然後我們開始添加行程，使用遞歸部分，将先前定義的trip_journey與trips表join以發現所有可能的目的地和相關成本。
UNION ALL
select
trips.city_b_id,
trips.trip_id,
tj.total_price_in_eur + trips.price_in_eur,
tj.journey_stops || city_b.city_name
from trip_journey tj join trips on tj.city_id = trips.city_a_id
join cities city_a on trips.city_a_id = city_a.city_id
join cities city_b on trips.city_b_id = city_b.city_id
where tj.total_price_in_eur + trips.price_in_eur < 800 将city_b.city_name添加到journey_stops中，然後計算總代價：tj.total_price_in_eur + trips.price_in_eur。最後，我們在where條件中限制總成本800。這個查詢檢索89行，從Rome開始直到{Rome,Helsinki,Rome,Helsinki,Rome,Helsinki,Barcelona,Rome}:
city_id | trip_id | total_price_in_eur | journey_stops
---------+---------+--------------------+-----------------------------------------------------------------
0 | | 0 | {Rome}
1 | 1 | 200 | {Rome,London}
2 | 2 | 250 | {Rome,Paris}
3 | 3 | 150 | {Rome,Helsinki}
0 | 4 | 320 | {Rome,London,Rome}
3 | 5 | 550 | {Rome,London,Helsinki}
....
4 | 10 | 770 | {Rome,Helsinki,Rome,Helsinki,Barcelona,Rome,Helsinki,Barcelona}
0 | 11 | 700 | {Rome,Helsinki,Rome,Helsinki,Rome,Helsinki,Barcelona,Rome}
(89 rows) 使用SEARCH選項定義探索路徑
上面的89行很好地總結了我們可以采取的所有可能的路徑。但是該資料集是怎麼排序的？在PG14中，SEARCH選項提供了一種新的方法定義遞歸查詢：
1) 基于stops的個數進行排序，可以使用BREADTH選項。我們會首先看到0 stops的trips然後1依次向後。
2) 如果想基于trip路徑進行排序，可以使用DEPTH選項。我們将看到旅程在每一步擴充，例如{Rome}-> {Rome->London} -> {Rome->London->Helsinki}直到找到旅程最大深度，然後探索樹的連續分支。
為了在我們的資料集上提供一個示例，隻需要将最後一條select *from trip_journey語句替換以下内容：
SEARCH BREADTH FIRST BY city_id SET ordercol
select * from trip_journey order by ordercol limit 15; 我們将查詢限制為僅傳回前15行（limit 15），這可以節省計算。因為我們不會掃描整個組合，但仍使我們能夠示範該功能。因為我們正在使用BREADTH選項，是以結果集按stops排序：
city_id | trip_id | total_price_in_eur | journey_stops | ordercol
---------+---------+--------------------+--------------------------------+----------
0 | | 0 | {Rome} | (0,0)
1 | 1 | 200 | {Rome,London} | (1,1)
2 | 2 | 250 | {Rome,Paris} | (1,2)
3 | 3 | 150 | {Rome,Helsinki} | (1,3)
0 | 4 | 320 | {Rome,London,Rome} | (2,0)
0 | 9 | 200 | {Rome,Helsinki,Rome} | (2,0)
0 | 7 | 420 | {Rome,Paris,Rome} | (2,0)
3 | 5 | 550 | {Rome,London,Helsinki} | (2,3)
3 | 8 | 570 | {Rome,Paris,Helsinki} | (2,3)
4 | 6 | 650 | {Rome,London,Barcelona} | (2,4)
4 | 10 | 270 | {Rome,Helsinki,Barcelona} | (2,4)
0 | 9 | 600 | {Rome,London,Helsinki,Rome} | (3,0)
0 | 11 | 300 | {Rome,Helsinki,Barcelona,Rome} | (3,0)
0 | 9 | 620 | {Rome,Paris,Helsinki,Rome} | (3,0)
0 | 11 | 680 | {Rome,London,Barcelona,Rome} | (3,0)
(15 rows) 該ordercol列包含一個元組（A，B），其中第一項代表級别，第二項代表最新的city_id。例如（2，0）說明旅行包括2此行程，并以Rome（city_id=0）結束。stops列包含相同資訊{Rome,Paris,Rome}。
如果使用DEPTH替換BREADTH，可以得到以trip路徑排序的前15個trip。
city_id | trip_id | total_price_in_eur | journey_stops | ordercol
---------+---------+--------------------+-----------------------------------------------------+-------------------------------
0 | | 0 | {Rome} | {(0)}
1 | 1 | 200 | {Rome,London} | {(0),(1)}
0 | 4 | 320 | {Rome,London,Rome} | {(0),(1),(0)}
1 | 1 | 520 | {Rome,London,Rome,London} | {(0),(1),(0),(1)}
0 | 4 | 640 | {Rome,London,Rome,London,Rome} | {(0),(1),(0),(1),(0)}
3 | 3 | 790 | {Rome,London,Rome,London,Rome,Helsinki} | {(0),(1),(0),(1),(0),(3)}
2 | 2 | 570 | {Rome,London,Rome,Paris} | {(0),(1),(0),(2)}
0 | 7 | 740 | {Rome,London,Rome,Paris,Rome} | {(0),(1),(0),(2),(0)}
3 | 3 | 470 | {Rome,London,Rome,Helsinki} | {(0),(1),(0),(3)}
0 | 9 | 520 | {Rome,London,Rome,Helsinki,Rome} | {(0),(1),(0),(3),(0)}
1 | 1 | 720 | {Rome,London,Rome,Helsinki,Rome,London} | {(0),(1),(0),(3),(0),(1)}
2 | 2 | 770 | {Rome,London,Rome,Helsinki,Rome,Paris} | {(0),(1),(0),(3),(0),(2)}
3 | 3 | 670 | {Rome,London,Rome,Helsinki,Rome,Helsinki} | {(0),(1),(0),(3),(0),(3)}
0 | 9 | 720 | {Rome,London,Rome,Helsinki,Rome,Helsinki,Rome} | {(0),(1),(0),(3),(0),(3),(0)}
4 | 10 | 790 | {Rome,London,Rome,Helsinki,Rome,Helsinki,Barcelona} | {(0),(1),(0),(3),(0),(3),(4)}
(15 rows) ordercol列包含city_id的拼接清單，例如{(0),(1),(0),(2)}表示，我們經過Rome->London->Rome->Paris。傳回行的順序依賴于ordercol。
通過CYCLE選項避免循環
Rome->London->Rome->Paris的旅途是不是很美好？你可能不喜歡多次經過一個城市。循環時一種非常低效的旅行方式，我們應該盡可能避免。PG14的CYCLE選項提供了一種跳過他們的方法。
在原始遞歸查詢中，使用下面語句替換select * from trip_journey：
CYCLE city_id SET is_cycle USING journey_ids
select * from trip_journey where is_cycle=false; 以上為遞歸查詢添加了幾列：
1) journey_ids以數組ARRAY形式包含city_id的序列
2) is_cycle通過檢查目前city_id是否已經在journey_ids列中來标記循環。
過濾is_cycle=false後的查詢結果提供了可以支付得起的無循環旅程清單：
city_id | trip_id | total_price_in_eur | journey_stops | is_cycle | journey_ids
---------+---------+--------------------+----------------------------------+----------+-------------------
0 | | 0 | {Rome} | f | {(0)}
1 | 1 | 200 | {Rome,London} | f | {(0),(1)}
2 | 2 | 250 | {Rome,Paris} | f | {(0),(2)}
3 | 3 | 150 | {Rome,Helsinki} | f | {(0),(3)}
3 | 5 | 550 | {Rome,London,Helsinki} | f | {(0),(1),(3)}
4 | 6 | 650 | {Rome,London,Barcelona} | f | {(0),(1),(4)}
3 | 8 | 570 | {Rome,Paris,Helsinki} | f | {(0),(2),(3)}
4 | 10 | 270 | {Rome,Helsinki,Barcelona} | f | {(0),(3),(4)}
4 | 10 | 690 | {Rome,Paris,Helsinki,Barcelona} | f | {(0),(2),(3),(4)}
4 | 10 | 670 | {Rome,London,Helsinki,Barcelona} | f | {(0),(1),(3),(4)}
1 | 12 | 770 | {Rome,Helsinki,Barcelona,London} | f | {(0),(3),(4),(1)}
(11 rows) 避免循環後，可以比較旅程：{Rome,Helsinki,Barcelona,London} 和{Rome,London,Helsinki,Barcelona}包含相同城市，但是第一個便宜100歐元。
回國
每次旅行都有一個讓您很高興的回家時刻，但若檢查上面的旅行，由于避免了循環，是以我們不可能再回到Rome。
為實作這一點，在原始查詢中，需要考慮與trips表的額外join。将傳回成本添加到每個旅程中，可以用下面查詢：
with recursive trip_journey(
city_id,
trip_id,
total_price_in_eur,
journey_stops,
journey_prices,
return_price
)
as (
select
city_id as city_id,
null::int,
0 price_in_eur,
ARRAY[city_name] as journey_name,
ARRAY[0::int] as journey_price,
0 return_price
from cities
where city_id=0
UNION ALL
select
trips.city_b_id,
trips.trip_id,
tj.total_price_in_eur + trips.price_in_eur,
tj.journey_stops || city_b.city_name,
tj.journey_prices || trips.price_in_eur,
return_trips.price_in_eur
from trip_journey tj join trips on tj.city_id = trips.city_a_id
join cities city_a on trips.city_a_id = city_a.city_id
join cities city_b on trips.city_b_id = city_b.city_id
join trips return_trips on trips.city_b_id = return_trips.city_a_id and return_trips.city_b_id = 0
where tj.total_price_in_eur + trips.price_in_eur + return_trips.price_in_eur < 800
) CYCLE city_id SET is_cycle USING journey_ids
select * from trip_journey where is_cycle=false; join trips return_trips on trips.city_b_id = return_trips.city_a_id and return_trips.city_b_id = 0部分確定我們前往Rome的一個回程。tj.total_price_in_eur + trips.price_in_eur + return_trips.price_in_eur < 800語句進行預算檢查。
city_id | trip_id | total_price_in_eur | journey_stops | journey_prices | return_price | is_cycle | journey_ids
---------+---------+--------------------+----------------------------------+-----------------+--------------+----------+-------------------
0 | | 0 | {Rome} | {0} | 0 | f | {(0)}
1 | 1 | 200 | {Rome,London} | {0,200} | 120 | f | {(0),(1)}
2 | 2 | 250 | {Rome,Paris} | {0,250} | 170 | f | {(0),(2)}
3 | 3 | 150 | {Rome,Helsinki} | {0,150} | 50 | f | {(0),(3)}
3 | 5 | 550 | {Rome,London,Helsinki} | {0,200,350} | 50 | f | {(0),(1),(3)}
4 | 6 | 650 | {Rome,London,Barcelona} | {0,200,450} | 30 | f | {(0),(1),(4)}
3 | 8 | 570 | {Rome,Paris,Helsinki} | {0,250,320} | 50 | f | {(0),(2),(3)}
4 | 10 | 270 | {Rome,Helsinki,Barcelona} | {0,150,120} | 30 | f | {(0),(3),(4)}
4 | 10 | 690 | {Rome,Paris,Helsinki,Barcelona} | {0,250,320,120} | 30 | f | {(0),(2),(3),(4)}
4 | 10 | 670 | {Rome,London,Helsinki,Barcelona} | {0,200,350,120} | 30 | f | {(0),(1),(3),(4)}
(10 rows) ![【python】【資料處理】畫多元資料分布圖[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)