図でイメージするOracle DatabaseのSQL全集

第7回 再帰with句
Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治(やまぎし けんじ)ACE

SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。

目次

第1部 再帰with句の使用例

第2部 データの探索

第3部 枝切り

第4部 階層問い合わせの機能を模倣

第5部 バックトラック問題

今回のテーマ

今回は、下記のOracleのSQL文の評価順序においての、1番目のfrom句でのインラインビューに相当する、with句の使用例と、私のSQLのイメージを解説します。

1番目 from句
2番目 where句 (結合条件)
3番目 start with句
4番目 connect by句
5番目 where句 (行のフィルタ条件)
6番目 group by句
7番目 having句
8番目 model句
9番目 select句
10番目 union、minus、intersectなどの集合演算
11番目 order by句

動作確認環境

Oracle Database 11g Release 11.2.0.1.0 (windows 32ビット版)

枝切り(ノード数の総合計)
のみ、 Oracle Database 11g Express Edition Release 11.2.0.2.0 (windows 32ビット版)

with句とは

インラインビューを作成

with句は、select文において、インラインビューを作成するという用途で使われます。

with句は、再帰のないwith句と、再帰のあるwith句(再帰with句)があります。
再帰のないwith句は、Oracle9iから使用可能で、再帰with句は、Oracle11gR2から使用可能です。

階層問い合わせに関する知識があると、再帰with句を理解しやすいです。

参考リソース
図でイメージするOracleのSQL全集 第6回 階層問い合わせ

▲ ページTOPに戻る

再帰のないwith句

SQLの可読性の向上

再帰のないwith句の主な使い道は、3つあります。
1つは、テスト用の仮想テーブルの作成です。

-- テスト用の仮想テーブルの作成
with work(Val1,Va2) as(
select 1,8 from dual union all
select 2,9 from dual union all
select 6,1 from dual)
select sum(Val1) as sumVal1,
sum(Va2) as sumVal2
  from work;
出力結果
sumVal1  sumVal2
-------  -------
      9       18

2つは、select文の結果を自己結合などで複数回使いたい時の2度書き防止です。
津島博士も、パフォーマンス講座 第11回 良いSQLについて(2)で紹介されてますね。

create table NonRecWithSample(ID,Val) as
select 'AAA',1 from dual union all
select 'AAA',3 from dual union all
select 'BBB',5 from dual union all
select 'CCC',1 from dual union all
select 'DDD',2 from dual union all
select 'DDD',7 from dual;
-- select文の2度書き防止
with tmp as(
select ID,sum(Val) as sumVal
  from NonRecWithSample
group by ID)
select a.ID as a_ID,a.sumVal as a_SumVal,
b.ID as b_ID,b.sumVal as b_SumVal
  from tmp a Join tmp b
    on a.ID < b.ID
   and a.sumVal < b.sumVal
order by a_ID,b_ID;
出力結果
a_ID  a_SumVal  b_ID  b_SumVal
----  --------  ----  --------
AAA          4  BBB          5
AAA          4  DDD          9
BBB          5  DDD          9
CCC          1  DDD          9

3つは、インラインビューを含むSQLを、上から下に読めるようにすることによる、可読性の向上です。

-- インラインビューを含むSQL
select ID,Val
from (select ID,Val,
      count(*) over(partition by ID) as cnt
      from NonRecWithSample)
where cnt=1;
-- 上から下に読めるようにしたSQL
with tmp as(
select ID,Val,
count(*) over(partition by ID) as cnt
  from NonRecWithSample)
select ID,Val
  from tmp
 where cnt=1;

▲ ページTOPに戻る

1から5までの整数を出力

基本的な再帰with句の使用例

再帰with句を使って、1から5までの整数を出力してみます。

-- 1から5までの整数を出力
with rec(Val) as(
select 1 from dual
union all
select Val+1
  from rec
 where Val+1 <= 5)
select Val from rec;
出力結果
Val
---
  1
  2
  3
  4
  5

再帰with句では、union allの上を非再帰項と呼び、union allの下を再帰項と呼びます。

再帰with句は、最初に非再帰項のselect文を実行して、
そのselect文の結果を使って再帰項のselect文を実行して、
そのselect文の結果を使って再帰項のselect文を実行して、
そのselect文の結果を使って再帰項のselect文を実行して、(以下続く・・・)
といった処理を、再帰項のselect文の結果が0件になるまで行います。

上記のSQLでは、最初に、非再帰項のselect 1 from dualで1行作成してます。
次に再帰項において、select句のVal+1とwhere句のVal+1 <= 5で
2から5までの整数を持つ行を作成してます。

非再帰項の段階での、SQLのイメージは下記となります。
非再帰項による木の根の作成をイメージしてます。

非再帰項の段階のイメージ

再帰項の段階での、SQLのイメージは下記となります。
再帰項による木のノードの作成をイメージしてます。

再帰項の段階のイメージ

再帰with句の処理をイメージする際には、非再帰項と再帰項で2段階に分けてイメージすると分かりやすいです。
また、階層問い合わせのstart with句が、再帰with句の非再帰項に相当し、階層問い合わせのconnect by句が、再帰with句の再帰項に相当すると考えると理解しやすいです。

▲ ページTOPに戻る

再帰with句で行の分割

1行を複数行に分割

再帰with句で1行を複数行に分割してみます。

create table RowToRows(ID,FromVal,ToVal) as
select 'AA',2,4 from dual union all
select 'BB',8,9 from dual union all
select 'CC',3,5 from dual;

IDごとに、FromValからToValまでの整数の行を作成します。

-- 1行を複数行に分割
with rec(ID,FromVal,ToVal) as(
select ID,FromVal,ToVal
  from RowToRows
union all
select ID,FromVal+1,ToVal
  from rec
 where FromVal+1 <= ToVal)
select ID,FromVal as Val from rec
order by ID,Val;
出力結果
ID  Val
--  ---
AA    2
AA    3
AA    4
BB    8
BB    9
CC    3
CC    4
CC    5

再帰項でToValを上限として数値の加算を行って、行を再帰的に作成してます。

非再帰項の段階での、SQLのイメージは下記となります。
非再帰項による木の根の作成をイメージしてます。

非再帰項の段階のイメージ

再帰項の段階での、SQLのイメージは下記となります。
再帰項による木のノードの作成をイメージしてます。

再帰項の段階のイメージ

▲ ページTOPに戻る

Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve