株式会社コーソル

コーソルDatabaseエンジニアのブログ

技術ブログ

クリスマスツリーで再帰的WITH SQLを学ぶ

Oracle ACE Proの渡部です。
この記事は、JPOUG Advent Calendar 2022 25日目の記事です。24日目は Kazuhiro Takahashi さんの記事『非同期グローバル索引メンテナンスについて考える』でした。

12月 1日から24日まで記事を執筆いただいたみなさま、どうもありがとうございました。

この記事が JPOUG Advent Calendar 2022 の最後の記事となります。
X'masということで、クリスマスツリーを出力するSQLを題材にして、以前から解説をまとめておきたかった再帰的WITHについて記載したいと思います。

再帰的WITHを用いたクリスマスツリーの出力

「お遊び」として、SQLを使ってクリスマスツリーを出力する例が、インターネット上でいくつか公開されています。

やり方は色々あるのですが、今回はある程度有益な技術的なトピックを含めたい想いもあり、以下の再帰的WITHを用いた例を元にして再帰的WITHについて説明したいと思います。

WITH tree(lev, xmas) AS (
  SELECT 1 lev,      RPAD(' ', 10, ' ') || '*' xmas 
      FROM dual
  UNION ALL
  SELECT tree.lev+1, 
         RPAD(' ', 10 - tree.lev, ' ') || 
           RPAD('^', tree.lev + 1, '^') || 
           LPAD('^', tree.lev, '^')  xmas
      FROM tree
      WHERE tree.lev < 10
)
SELECT '    Merry Christmas!' AS "Merry Christmas!" FROM dual
  UNION ALL
SELECT xmas FROM TREE
  UNION ALL
SELECT '         | |' FROM dual
  UNION ALL
SELECT '      ~~/   \~~' FROM dual;

再帰的WITHとは

再帰的WITHとは、WITH句を用いて定義した名前付きサブクエリを「再帰的」に呼び出せる機能です。「再帰的」とは、数学やコンピュータサイエンス、プログラミングで使用される概念で、ある関数が自身の関数を呼び出すような一種の入れ子構造を持つ定義方法のことです。

事実、上記のSQLでは、サブクエリtreeの定義（"WITH tree(lev, xmas) AS ( ... )"）の中で、tree（＝自サブクエリ）を呼び出しています。

再帰的WITHの動作

では、再帰的WITHの動作を見てゆきましょう。ただ、上記のSQLには再帰的WITH以外の余計な部分があるため、シンプルにしたSQL（以下「SQL1」と呼ぶ）を用いて動作を説明します。

WITH tree(lev, xmas) AS (
  SELECT 1 lev,      LPAD('^', 1,          '^') xmas
      FROM dual
  UNION ALL
  SELECT tree.lev+1, LPAD('^', tree.lev+1, '^') xmas
      FROM tree
      WHERE tree.lev < 3
)
SELECT lev, xmas FROM tree;

SQL1の実行結果は以下のとおりです。

       LEV XMAS
---------- ----------------------------------------
         1 ^
         2 ^^
         3 ^^^

なお、LPADは 引数1の左側に引数3の文字を連続的に埋め込んで引数3の桁数にして戻すファクションです。

SQL> SELECT LPAD('X', 2, '-') FROM DUAL;

LP
--
-X

SQL> SELECT LPAD('X', 3, '-') FROM DUAL;

LPA
---
--X

SQL> SELECT LPAD('X', 4, '-') FROM DUAL;

LPAD
----
---X

アンカーメンバーと再帰的メンバー

Oracleの再帰的WITHは、UNION ALLの上下にアンカーメンバーと再帰的メンバーと呼ばれるクエリを書くことで定義されます。

それぞれの役割および特徴は以下のとおりです。

アンカーメンバー

  • 最初に実行され、処理のスタート地点を定義する
  • アンカーメンバーの実行結果は、再帰的メンバーの最初の実行に渡される

再帰的メンバー

  • WITH句を用いて定義した名前付きサブクエリを参照する。
  • 再帰的メンバーのある実行における実行結果は、WITH句を用いて定義した名前付きサブクエリの参照を通じて、再帰的メンバーの次の実行に渡される
  • 実行結果が空になるまで繰り返し実行される

説明があまりピンと来ないかもしれませんが、以下の説明に進んで下さい。

SQL1が実行される流れ

SQL1の「WITH句を用いて定義した名前付きサブクエリtree」が実行される流れを図に起こしました。

以下の流れで処理が実行される点を理解してください。

  1. まず、アンカーメンバーが実行される
  2. 次に、再帰的メンバーが実行される（1回目）。このとき、アンカーメンバーの実行結果がtreeへの参照を介して再帰的メンバーに渡される。
  3. 再帰的メンバーの実行結果が「空」かどうかがチェックされる。「空」でない場合は、再帰的メンバーの次の実行に移る（今回は「空」でない）。
  4. 再帰メンバーが実行される（2回目）。このとき、1回目の再帰的メンバーの実行結果がtreeへの参照を介して再帰的メンバーに渡される。
  5. 再帰的メンバーの実行結果が「空」かどうかがチェックされる。「空」でない場合は、再帰的メンバーの次の実行に移る（今回は「空」でない）。
  6. 再帰メンバーが実行される（3回目）。このとき、2回目の再帰的メンバーの実行結果がtreeへの参照を介して再帰的メンバーに渡される。
  7. 再帰的メンバーの実行結果が「空」かどうかがチェックされる。「空」でない場合は再帰的メンバーの次の実行に移るが、今回は「空」なので再帰的メンバーの実行を終了する。
  8. 各メンバーの実行結果を和集合で結合する（UNION ALL）。結合した結果が、WITH句を用いて定義した名前付きサブクエリtreeの実行結果となる

なお、このあと、SQL1の最後の1行であるSELECT lev, xmas FROM treeが実行されますが、これはtreeの実行結果をそのまま返す形になります。

これで、再帰的WITHの動作を理解できたので、冒頭に記載したSQLの動作も理解できるハズです！

補足

それでは皆様よいお年を！！

関連記事

プロフィール

On7tWW6m1Ul4

渡部 亮太

・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数

カテゴリー

アーカイブ

一覧へ戻る