LoginSignup
144

More than 5 years have passed since last update.

再帰SQL -図解-

Last updated at Posted at 2018-01-09

まえがき

再帰SQLを使うと、テーブルに一時的に名前を付けることで、再帰処理(ループ)を実現できます。どのように実行されるのか難しかったため図解してみます。

with句

メインクエリの中で同じサブクエリを何度も呼び出している場合に使われるのがwith句です。with句を使うとサブクエリに名前をつけることができるので、メインクエリから何度でも呼び出すことができます。便宜上、with句によって作られる一時テーブルをwithテーブルと呼ぶことにします。with句を利用したクエリは、以下のように評価が進みます。

  1. with句を評価し、withテーブルを作成する
  2. メインクエリを実行する。

まずは簡単な例でwith句の使い方を見てみましょう。営業マンたちの月別売上を表す営業成績テーブルを考えます。

【営業成績テーブル】

名前 月次売上
田中太郎 1月 1000
田中太郎 2月 2500
田中太郎 3月 3000
鈴木次郎 1月 5000
鈴木次郎 2月 1000
鈴木次郎 3月 1500
山本三郎 1月 3000
山本三郎 2月 2200
山本三郎 3月 4000

【問】
月次売上の合計が一番高い人は誰でしょう?
【答】

WITH    成績サマリ (名前, 売上合計)
AS
(SELECT 名前, SUM(月次売上)
FROM    営業成績
GROUP   BY 名前)
--メインクエリ
SELECT  P1.名前, P1.売上合計
FROM    成績サマリ P1
WHERE   P1.売上合計
    = (SELECT   MAX(P2.売上合計)
        FROM    成績サマリ P2);

【解説】
まず、with句で、営業マンごとの売上合計を表す成績サマリテーブルを作成します。

【成績サマリテーブル】

名前 売上合計
田中太郎 6500
鈴木次郎 7500
山本三郎 9200

そして、メインクエリの中で、成績サマリテーブルから売上合計が一番高い行を取得しています。
with句を使い、一時的に作成したテーブルに名前を付けることによって、メインクエリの中で何度でも呼び出すことができるようになっていますね。

再帰SQL

再帰SQLは、前に行った処理の結果を利用して同じ処理を繰り返す場合に使われます。例えば木構造のデータを格納しているテーブルで、根から葉までたどるような場合です。
再帰SQLは、with句の拡張であるwith recursive句を利用します。with recursive句は、UNION ALLより上にある非再帰項と、UNION ALLより下にある再帰項から成っており、再帰項のクエリは何度も実行されます。
with recursive句の実行時は、withテーブル、ワークテーブル、結果テーブルという3つのテーブルが利用されます。初期状態ではこれら3つのテーブルは空です。評価は以下のように進みます。

  1. 非再帰項を評価し、withテーブルと結果テーブルに代入する (この時点でwithテーブルには名前が付けられるので、再帰項から呼び出しが可能になる。)
  2. 再帰項を評価し、その結果をワークテーブルに代入する
  3. ワークテーブルが空でなければ、
    • ワークテーブルを結果テーブルにUNION(またはUNION ALL)し、
    • withテーブルをワークテーブルで置き換え、
    • ワークテーブルを空にし、2から繰り返す
  4. 結果テーブルをwithテーブルに置き換える

図解するとこんな感じです。
image.png
image.png
image.png
image.png
image.png
image.png
image.png

例として、以下の図のような上司と部下というリレーションを持っている営業マンを考えます。
image.png

営業成績テーブルは上の図の関係を隣接リストモデルで表しており、名前と上司、そして年次売上を格納しています。

【営業成績テーブル】

名前 上司名 年次売上
部長 NULL 15000
課長A 部長 12000
課長B 部長 13000
田中太郎 課長A 6500
鈴木次郎 課長A 7500
山本三郎 課長B 9200

【問】
各営業マンの年次売上を求めなさい。ただし、部下の売上や部下の部下の売上も自分の売上に加算してよい。
【答】

with recursive 成績サマリ(部下名, 上司名, 売上総和) AS (
--非再帰項
SELECT  営業成績.名前, 営業成績.名前, 営業成績.年次売上
FROM    営業成績
UNION ALL
--再帰項
SELECT  営業成績.名前, 成績サマリ.上司名, 年次売上
FROM    営業成績, 成績サマリ
WHERE   営業成績.上司名 = 成績サマリ.部下名)
--メインクエリ
SELECT      上司名,SUM(売上総和) 売上総和
FROM        成績サマリ
GROUP BY    上司名;

【解説】
まず、非再帰項を評価し、成績サマリテーブルと結果テーブルに代入します。

【成績サマリテーブル①】

部下名 上司 年次売上
部長 部長 15000
課長A 課長A 12000
課長B 課長B 13000
田中太郎 田中太郎 6500
鈴木次郎 鈴木次郎 7500
山本三郎 山本三郎 9200

【結果テーブル①】

部下名 上司 年次売上
部長 部長 15000
課長A 課長A 12000
課長B 課長B 13000
田中太郎 田中太郎 6500
鈴木次郎 鈴木次郎 7500
山本三郎 山本三郎 9200

次に、再帰項を評価します。営業成績テーブルの上司名と、成績サマリテーブルの部下名で結合するのでワークテーブルは次のようになります。

【ワークテーブル①】

部下名 上司 年次売上
課長A 部長 12000
課長B 部長 13000
田中太郎 課長A 6500
鈴木次郎 課長A 7500
山本三郎 課長B 9200

ワークテーブルは空ではないので、結果テーブルにUNION ALLします。成績サマリテーブルをワークテーブルに置き換えます。

【結果テーブル②】

部下名 上司 年次売上
部長 部長 15000
課長A 課長A 12000
課長B 課長B 13000
田中太郎 田中太郎 6500
鈴木次郎 鈴木次郎 7500
山本三郎 山本三郎 9200
課長A 部長 12000
課長B 部長 13000
田中太郎 課長A 6500
鈴木次郎 課長A 7500
山本三郎 課長B 9200

【成績サマリテーブル②】

部下名 上司 年次売上
課長A 部長 12000
課長B 部長 13000
田中太郎 課長A 6500
鈴木次郎 課長A 7500
山本三郎 課長B 9200

そして再度、再帰項を評価します。

【ワークテーブル②】

部下名 上司 年次売上
田中太郎 部長 6500
鈴木次郎 部長 7500
山本三郎 部長 9200

ワークテーブルは空ではないので、結果テーブルにUNION ALLします。成績サマリテーブルをワークテーブルに置き換えます。

【結果テーブル③】

部下名 上司 年次売上
部長 部長 15000
課長A 課長A 12000
課長B 課長B 13000
田中太郎 田中太郎 6500
鈴木次郎 鈴木次郎 7500
山本三郎 山本三郎 9200
課長A 部長 12000
課長B 部長 13000
田中太郎 課長A 6500
鈴木次郎 課長A 7500
山本三郎 課長B 9200
田中太郎 部長 6500
鈴木次郎 部長 7500
山本三郎 部長 9200

【成績サマリテーブル③】

部下名 上司 年次売上
田中太郎 部長 6500
鈴木次郎 部長 7500
山本三郎 部長 9200

そして再度、再帰項を評価します。すると、ワークテーブルは空になります。なので、成績サマリテーブルは出来上がった結果テーブルで置き換えます。ここでwith句の処理は終了です。
最後に、メインクエリで総和をとれば完成です。

補足1

with recursive句を使ったクエリでは以下が成り立ちます。

  1. N番目のwithテーブル = N-1番目のワークテーブル
  2. N番目の結果テーブルはN番目までのwithテーブルのUNION[ALL]になっていること

再帰項を評価する際は、前回の再帰項の評価結果を参照していることと、評価結果がどんどん積みあがっていることがわかりますね。

補足2

今回with recursive句で作成した成績サマリテーブルは閉包テーブルという構造をしています。隣接リストモデルから閉包テーブルへの変換に必要なのは、営業成績テーブルを部下名と上司名で自己結合することを繰り返すことです。部下の部下は自分の部下、その部下も自分の部下、、、ということをうまく表現できていますね。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
144