10個SQL技巧之二：使用遞迴SQL生成資料

banq發表於2019-01-03
SQL遞迴

公用表表示式（也稱為：CTE，如在Oracle中也稱為子查詢因子，）是在SQL中宣告變數的唯一方法（除了只有PostgreSQL和Sybase SQL Anywhere支援得WINDOW模糊子句）。
這是一個強大的概念。非常強大。請考慮以下宣告：

-- Table variables
WITH
  t1(v1, v2) AS (SELECT 1, 2),
  t2(w1, w2) AS (
    SELECT v1 * 2, v2 * 2
    FROM t1
  )
SELECT *
FROM t1, t2


它產生了：

v1 v2 w1 w2 
----------------- 
 1 2 2 4

使用簡單的WITH子句，您可以指定表變數列表（記住：一切都是表），它們甚至可能相互依賴。
這很容易理解。這使得CTE（公用表格表示式）已經非常有用，但真正令人敬畏的是它們被允許遞迴！考慮以下PostgreSQL示例：

WITH RECURSIVE t(v) AS (
  SELECT 1     -- Seed Row
  UNION ALL
  SELECT v + 1 -- Recursion
  FROM t
)
SELECT v
FROM t
LIMIT 5


它產生了：

v 
- 
1 
2 
3 
4 
5


它是如何工作的？一旦你看到很多關鍵詞，它就相對容易了。您定義一個公共表表示式，它只有兩個UNION ALL子查詢。
第一次UNION ALL個子查詢是我通常所說的“種子行”。它“種子”（初始化）遞迴。它可以產生一行或幾行，之後我們會遞迴。記住：一切都是一個表，所以我們的遞迴將發生在整個表上，而不是單個行/值。
第二次UNION ALL個子查詢是遞迴發生的地方。仔細觀察，你會發現它是從中選擇的t。即允許第二個子查詢從我們即將宣佈的CTE中進行選擇。遞迴。因此它也可以訪問v列，它已經被使用它的CTE宣告過的。
在我們的示例中，我們使用行播種遞迴(1)，然後透過新增來遞迴v + 1。然後透過設定a LIMIT 5（謹防可能無限的遞迴 - 就像Java 8 Streams一樣）在使用現場停止遞迴。

附註：圖靈完整性
遞迴CTE使SQL：1999圖靈完成，這意味著任何程式都可以用SQL編寫！（如果你夠瘋狂的話）
一個經常出現在部落格上的令人印象深刻的例子：Mandelbrot Set，例如http://explainextended.com/2013/12/31/happy-new-year-5/上顯示：

WITH RECURSIVE q(r, i, rx, ix, g) AS (
  SELECT r::DOUBLE PRECISION * 0.02, i::DOUBLE PRECISION * 0.02, 
        .0::DOUBLE PRECISION      , .0::DOUBLE PRECISION, 0
  FROM generate_series(-60, 20) r, generate_series(-50, 50) i
  UNION ALL
  SELECT r, i, CASE WHEN abs(rx * rx + ix * ix) <= 2 THEN rx * rx - ix * ix END + r, 
               CASE WHEN abs(rx * rx + ix * ix) <= 2 THEN 2 * rx * ix END + i, g + 1
  FROM q
  WHERE rx IS NOT NULL AND g < 99
)
SELECT array_to_string(array_agg(s ORDER BY r), '')
FROM (
  SELECT i, r, substring(' .:-=+*#%@', max(g) / 10 + 1, 1) s
  FROM q
  GROUP BY i, r
) q
GROUP BY i
ORDER BY i


在PostgreSQL上執行上面的內容，你會得到類似的東西：

                             ..：-....... = = * = :: - @@@@@ :::：。@ .. * - 。=。
                             ... = ... = ... :: +％@：@@@@@@@@@@@@@ + *＃= =：+ - 。..-   
                             。：。：= :: * .... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ..：。
                             ... * @@@@ = @：@@@@@@@@@@@@@@@@@@@@@@@@@@ = = ....：。...： ：。
                              :: @@@@@： - @@@@@@@@@@@@@@@@@@@@@@@@@@@@：@ ..-：@ = * ::: 。
                              .- @@@@@ - @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ = @@@@ = .. ：
                              ... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 。
                             ....： - * @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@： ：   
                            ..... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ..  
                          ..... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ - ： ......   
                         .--：+。@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@ ...   
                         == @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@  -  ..   
                         .. + @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@ - ＃。  
                         ... = + @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@ .. 
                         - 。=  -  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@ ..：
                        。*％：@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@：@  - 
 。..：... ..- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@ 
.............. ....- @@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@％@ =
- = - .....-：。.......... :: @@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@ .. 
.. =：-.... = @ + .. = .... ..... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@ :. 
：+ @@ :: @ == @ - *：％：+ .......：@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@。
:: @@@ - @@@@@@@@@ - ：= .....：@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@：
：@@@@@@@@@@@@@@@ =： .....％@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@ 
：@@@@@@@@@@@@@@@@@ -...：@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@： - 
：@@@@@@ @@@@@@@@@@@@@ - ..％@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@。
％@@@@@@@@@@@@@@@@@@@ -..- @@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@。
@@@@@@@@@@@@@@@@@@@@@ :: + @@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ +
@@@@@@@@@@@@@@@@@@@@@@：@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@ .. 
@@@@@@@@@@@@@@@@@@@ @@@ - @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@  - 
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@。 


令人印象深刻，是吧？

相關文章