SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

PostgreSQLの再帰SQL

PostgreSQLの再帰SQL(1)
――再帰SQLの構文

20から30までの自然数を出力など

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (753.0 B)

 本連載ではPostgreSQL 8.4を使って、再帰SQLの入門事項や、Oracle Technology Networkでよく見かける問題の解法や、Oracleの階層問い合わせや再帰with句の機能を模倣する方法を、SQLのイメージを交えて解説します。本稿では、再帰SQLの構文などを扱います。

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 本連載ではPostgreSQL 8.4を使って、再帰SQLの入門事項や、Oracle Technology Network(OTN)でよく見かける問題の解法、Oracleの階層問い合わせや再帰with句の機能を模倣する方法を、SQLのイメージを交えて解説します。

 本稿では、再帰SQLの構文などを扱います。

対象読者

  • PostgreSQLで再帰SQLを使ってみたい方
  • 再帰SQLの理解を深めたい方

必要な環境

 本稿で扱うSQLは、PostgreSQL 8.4 beta 2で動作確認しました。その他、次の環境でも応用が可能です。

  • Oracle 11g R2以降
  • DB2
  • SQL Server 2005以降

1. 再帰SQLとは

 with句でselect文を実行して、そのselect文の結果を使ってselect文を実行して、そのselect文の結果を使ってselect文を実行して、……(以下続く)といったことを行うSQLです。

 PostgreSQL 8.4やOracle 11g R2以降やSQL Server 2005以降などで使用できます。再帰SQL、再帰クエリ、共通表式、CTE、再帰with句などと呼ばれます。再帰SQLの使い道としては、階層構造やグラフ構造のデータに対するデータ取得、行の補完、行間計算などが挙げられます。

2. with句(再帰なし)

 まずは、再帰のないwith句の構文に慣れておきましょう。再帰のないwith句の代表的な使い道としては、テスト用の仮想テーブルの作成です。ちょっとSQLを試したい時などに使います。下記がサンプルです。

仮想テーブルの作成と使用の例1
with tmp(ID,Val) as(
select 111,1 union all
select 222,1 union all
select 333,1)
select ID,Val from tmp;
出力結果
ID Val
111 1
222 1
333 1
仮想テーブルの作成と使用の例2
with tmp(ID,Val) as(
values(444,2),
      (555,2),
      (666,2))
select ID,Val from tmp;
出力結果
ID Val
444 2
555 2
666 2

 下記のようにインラインビューでも仮想テーブルを作れますが、with句であれば、SQLの最初の数行に仮想テーブルの値が集中し、編集が容易なため、with句がよく使われます。

インラインビューで仮想テーブル作成
select ID,Val
  from (values(444,2),
              (555,2),
              (666,2)) as tmp(ID,Val);

 複雑なselect文の結果同士での自己結合が必要なケースでwith句が使われることもあります。下記の例のように、with句の中に複雑なselect文を記述すれば、複雑なselect文は1回しか記述しなくて済みます。

with句の中に複雑なselect文を記述
wlth tmp as(
select ID,sum(Val) as sumVal
  from anyTable
 group by ID
having max(Val) = 100)
select a.ID,a.sumVal,b.ID,b.sumVal
  from tmp a,tmp b
 where a.sumVal <= b.sumVal;

次のページ
3. 20から30までの自然数を出力

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/4777 2010/02/08 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング