PostgreSQL 務實應用（一/5）樹形層級

項目中，經常會碰到多級的樹形結構數據，如地區信息，省、市、區、街道等，或客戶關係信息上三級，下三級等。

實際項目中，我們可能碰到以下兩種需求：

• 一條記錄中呈現路徑：省 - 市 - 區 - 街道
• 一條記錄中呈現上幾級：上級，上上級，上上上級

此情此景，使用 WITH RECURSIVE 遞歸查詢再合適不過。

遞歸查詢

在介紹 WITH RECURSIVE 之前，我們先介紹一個 WITH

通過 WITH ，我們可以在寫查詢語句之前，為一個需要用到的子查詢定義一個臨時的別名，該別名可以查詢語句中使用，從而簡化查詢語句。如下示例：

WITH 臨時別名 AS(
    SELECT 1 a, 2 b, 3 c  
)
-- 下邊是查詢語句，像使用表或視圖一樣使用了“臨時別名”
select a * a, b * b, c * c from 臨時別名;

接下來我們再來看遞歸 RECURSIVE ，我們知道，遞歸形式上就是自己會調用自己，對於數據集而言，遞歸通常需要兩個條件：

1. 有入口數據，即遞歸開始的數據記錄
2. 有遞歸結束的條件，即在關聯自己的同時有終止的條件

這第 2 點，原因很簡單，關聯自己產生的新數據記錄又會成為遞歸關聯中的數據。通常 WHERE 子句給出這個條件。

PostgreSQL 中，WITH RECURSIVE 即表示遞歸查詢，“臨時別名”在定義自身的子查詢體中也可以使用。

我們來看一個示例：

-- 定義了一個臨時別名 cet
WITH RECURSIVE cet AS (
  -- 遞歸初始的數據，id=1, name="name 1", pid=null （最頂層父id為空） 
  SELECT 1 AS id, 'name 1' AS name, cast(null AS int) AS pid
  UNION ALL
  -- 聯合 cet 自己，id 遞增，結束條件為 id < 10。如果不加這個 WHERE 會如何？
  SELECT id+1, 'name ' || (id+1), id FROM cet WHERE id < 10
)
SELECT * FROM cet;

輸出以下結果：

搞定需求

上邊的示例中的結果集就是一個典型的樹形層級結構，我們以這個數據結果為例來完成文章開頭提到的兩個需求，首先我們把這個結果集變成一個視圖以便當源數據用。

-- 創建一個名字為 view_tree_test 的視圖
CREATE VIEW view_tree_test as
WITH RECURSIVE cet AS (
    SELECT 1 AS id, 'name 1' AS name, cast(null AS int) AS pid
  UNION ALL
    SELECT id+1, 'name ' || (id+1), id FROM cet WHERE id < 10
)
SELECT * FROM cet;

我們以 view_tree_test 作為數據源來完成兩大需求。

需求一，顯示路徑

WITH RECURSIVE tpath AS(
    SELECT id, name, pid, name as path from view_tree_test where pid is null
    UNION ALL
    -- 聯合子節點
    SELECT a.id, a.name, a.pid, 
           tpath.path || '-' || a.name  -- 父路徑拼接當前節點名稱形成路徑
      FROM view_tree_test a, tpath      -- 關聯已有結果，查詢其子節點
     WHERE a.pid = tpath.id
) 
SELECT * FROM tpath;

查詢結果可見其效果：

需求二，顯示每第記錄的上三級

WITH RECURSIVE uuup AS(
    -- 初始值，頂層的上級均置為空，需要幾級置幾個空
    SELECT id, name, '' 上級, '' 上上級, '' 上上上級 from view_tree_test where pid is null
    UNION ALL
    -- 聯合子節點
    SELECT a.id, a.name, 
           -- 父級即為上級，父的上級為上上級
           uuup.name 上級, uuup.上級 上上級, uuup.上上級 上上上級
      FROM view_tree_test a, uuup      -- 關聯已有結果，查詢其子節點
     WHERE a.pid = uuup.id
) 
SELECT * FROM uuup;

順利呈現相關的上三級，需要更多上級也是 SO EASY！

小結

PostgreSQL 提供了相當多實用的數據處理方式，讓數據處理起來很是便捷。樹形結構是應用中經常會使用到，使用遞歸查詢能方便的處理跨層級的計算，還有很多想象空間哦。