SQL 遞迴思想

小至尖尖發表於2024-04-05
SQL遞迴

目前很缺遞迴思維，主要是演算法程式碼寫得少，本篇記錄下最近思考的內容。以 PostgreSQL 程式碼舉例（主要是非常喜歡這款效能小鋼炮資料庫）。
樹狀查詢不多說，很簡單大家基本都會，主要講 cte 程式碼遞迴實現不同需求。
以下所有內容都是我個人理解，不對之處請各位讀者多指教！

cte 語法簡介

以PG舉例，如果是ORACLE的話需要去掉RECURSIVE關鍵字

WITH RECURSIVE cte_tb (column_list) AS (
  -- 開始條件
  SELECT ...
  UNION ALL　　　　
  -- 遞迴邏輯程式碼和結束遞迴邏輯的程式碼
  SELECT ... FROM cte_tb WHERE ...
)
SELECT * FROM cte_tb;

PG使用 cte 遞迴實現層級查詢

scott=> WITH RECURSIVE T(LV, EMPNO, ENAME, MGR) AS (
scott(> SELECT 1 AS LV, EMPNO, ENAME, MGR FROM EMP WHERE MGR IS NULL -- 根節點，（開始條件）
scott(> UNION ALL
scott(> SELECT T.LV + 1, E.EMPNO, E.ENAME, E.MGR FROM EMP E
scott(>     INNER JOIN T ON T.EMPNO = E.MGR -- e表屬於子節點，t表屬於上層節點 t.EMPNO = e.MGR  相當於 prior empno = mgr; （遞迴條件），如果 t.EMPNO = e.MGR  匹配不上了就返回NULL （遞迴結束條件）
scott(> )
scott-> SELECT *
scott-> FROM T;
 lv | empno | ename  | mgr  
----+-------+--------+------
  1 |  7839 | KING   |     
  2 |  7566 | JONES  | 7839
  2 |  7698 | BLAKE  | 7839
  2 |  7782 | CLARK  | 7839
  3 |  7499 | ALLEN  | 7698
  3 |  7521 | WARD   | 7698
  3 |  7654 | MARTIN | 7698
  3 |  7788 | SCOTT  | 7566
  3 |  7844 | TURNER | 7698
  3 |  7900 | JAMES  | 7698
  3 |  7902 | FORD   | 7566
  3 |  7934 | MILLER | 7782
  4 |  7369 | SMITH  | 7902
  4 |  7876 | ADAMS  | 7788
(14 rows)

Time: 0.396 ms

cte 遞迴核心思想

一、使用 cte 遞迴，一定要滿足以下三個條件：　　

  1. 開始條件。　　
  2. 遞迴條件。　　
  3. 遞迴結束條件。

二、遞迴重要的思想：

  1. 大問題拆小問題，這個比較難，（怎麼拆、小問題之間的邏輯如何關聯上，遞迴結束條件如何滿足）等， 這也主要是我缺乏遞迴思維原因。　　
  2. 遞迴和迴圈的思路是高度相似：　　　　　　
    1. 迴圈需要 開始條件、結束條件、迴圈邏輯。　　　　
    2. 遞迴需要 開始條件、結束條件、遞迴邏輯+呼叫自身邏輯。

案例一、cte 遞迴實現數字遞增：

with RECURSIVE x(seq) as (
    SELECT 1 as seq                            -- SELECT 1 as seq from DUAL 遞迴開始條件
    UNION ALL
    SELECT x.seq + 1  as seq from x            -- x.seq + 1 from x          遞迴條件（每次執行 + 1 ） 呼叫自身
    WHERE x.seq < 10                           -- x.seq < 10                遞迴結束條件 
)
SELECT * FROM x ORDER BY 1;

 seq 
-----
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
(10 rows)

Time: 0.700 ms

上面這個案例很像迴圈，但是總體實現起來整體的思路會比迴圈稍微複雜那麼一丟丟。

其實在 PG 來說實現數字遞增的方式很多，例如：序列、SERIAL 、PLPG/SQL for 迴圈， 均能實現類似效果，上面案例案例讓各位讀者初步感受下。

案例二、cte 遞迴實現distinct效果

distinct sql

select distinct col from tt2;

  col   
--------
 C
 JAVA
 PL/SQL
 Python
(4 rows)

Time: 255.794 ms

使用CTE遞迴的方式實現

 WITH RECURSIVE t(col) as (
 
     (SELECT col from tt2 ORDER BY col LIMIT 1)                                      --   遞迴開始條件。
     UNION ALL
     SELECT (SELECT col FROM tt2 WHERE tt2.COL > t.COL order by tt2.COL LIMIT 1)     --   tt2.COL > t.COL 大問題拆小問題 ，遞迴邏輯
     FROM t WHERE t.COL IS NOT NULL                                                  --   遞迴結束條件
 )
 SELECT * FROM t WHERE t.COL is not NULL ;
 
  col   
--------
 C
 JAVA
 PL/SQL
 Python
(4 rows)

Time: 0.871 ms

這個案例引用的是德哥的思路，PG 15 上對 distinct 運算元最佳化過（支援並行），一千萬行資料 265 ms 就能跑出結果。

但是如果使用 cte 遞迴的話，根本不需要並行，0.8 ms 便能出結果，秒殺最佳化器演算法。

這個 order by tt2.col 非常牛逼，神來之筆，相當於進一步最佳化了整個遞迴的演算法模型。

基於德哥的思路做了修改

 WITH RECURSIVE t(col) as (
 
     (SELECT col from tt2 ORDER BY col LIMIT 1)
     UNION ALL
     SELECT (SELECT col FROM tt2 WHERE tt2.COL > t.COL GROUP BY tt2.COL LIMIT 1) FROM t WHERE t.COL IS NOT NULL
 )
 SELECT * FROM t WHERE t.COL is not NULL ;
 
  col   
--------
 C
 JAVA
 PL/SQL
 Python
(4 rows)

Time: 0.432 ms

order by 改成 group by 是借鑑德哥思路，我自己想的改良版，速度提升了 0.4ms , 不過總體來說差不多，有真實案例看場景使用。

案例三、cte 遞迴實現階乘演算法：

 WITH  RECURSIVE  factorial (n, factorial_val) AS (
     (SELECT 1 n, 1 factorial_val )                                -- 遞迴開始條件 : 1的階乘為1
     UNION ALL
     SELECT f.n + 1, (f.n + 1) * f.factorial_val                     /* 遞迴邏輯      (1 + 1) * 1  = 2
                                                                                     (2 + 1) * 2  = 6
                                                                                     (3 + 1) * 6  = 24
                                                                                     (4 + 1) * 24 = 120
                                                                        */
 
     FROM factorial f
     WHERE f.n < 5                                                 -- 結束遞迴條件，算 5 的階乘
 )
 SELECT max(factorial_val)  FROM factorial;
 
 max 
-----
 120
(1 row)

Time: 0.395 ms

CTE 遞迴也能實現階乘的邏輯，由於 PG 上是沒階乘函式的，可以將上面邏輯封裝到一個函式里面進行使用，程式碼如下：

CREATE OR REPLACE FUNCTION factorial(num BIGINT)
    RETURNS BIGINT AS $$
DECLARE
    result BIGINT;
BEGIN
    WITH RECURSIVE factorial (n, factorial_val) AS (
        (SELECT 1::INT as n , 1::int as factorial_val)
        UNION ALL
        SELECT f.n + 1, (f.n + 1) * f.factorial_val
        FROM factorial f
        WHERE f.n < num
    )
    SELECT max(factorial_val) INTO result FROM factorial;

    RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

結束語

cte 遞迴的技巧在任何資料庫都通用，我這裡只是使用了PG作為演示案例，遞迴不僅僅是樹狀查詢，理論上來說，只要能拆解邏輯（這也是最難的），所有SQL邏輯都能使用遞迴來表達。

但是這玩意是個雙刃劍，不是所有場景都能使用，假如一個列的選擇性很高，例如主鍵，如果使用遞迴來進行匹配查詢的話，那絕對是SB行為，線性遞迴的時間複雜度是O(n)，速度取決於你的資料量。

沒有最好的演算法，只有最合適的演算法。不過有遞迴思維的話，確實能解決很多日常和工作中不同型別的事物。

相關文章