Shoeisha Technology Media

CodeZine(コードジン)

記事種別から探す

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

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2010/02/08 14:00

ダウンロード SourceCode (753.0 B)

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

目次

はじめに

 本連載では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;

  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

All contents copyright © 2005-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5