Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

相関サブクエリで行と行を比較する

集合指向言語としてのSQL:その3

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

ダウンロード サンプルコード (2.0 KB)

SQLで同一行内の列同士の比較をすることは簡単です。それに比べて、異なる行を比較対象に使うことは、そう簡単ではありません。しかしそれは、SQLでは行間比較を記述できないという意味ではありません。本稿では、相関サブクエリを利用した行間比較の応用例を紹介します。

目次

はじめに

 SQLでは、同じ行内の列同士を比較することは簡単にできます。普通にWHERE句に「col_1 = col_2」のように書けばよいだけですから。一方、異なる行の間で列同士を比較することは、それほど簡単ではありません。ですがそれは、SQLで行間比較ができないということではありません。手続き型言語とはかなり異なる発想に基づいていますが、SQLでもそうした処理を記述することが可能です。

 SQLで行間比較をする際に威力を発揮するのが相関サブクエリ、特に自己結合と組み合わせた「自己相関サブクエリ」です。本稿では、この技術を使った行間比較の応用方法を、具体例を通して解説します。

稼働環境

  • Oracle
  • SQL Server
  • DB2
  • PostgreSQL
  • MySQL(バージョン4.1以上)

対象読者

 相関サブクエリの基本的な使い方を知っている方。CASE式、自己結合、スカラ・サブクエリについての知識があると望ましいです。とりわけ、自己結合と親和性が高い技術なので、未読の方は『自己結合の使い方』を先に読むと理解が増すでしょう。

成長・後退・現状維持

 行間比較が必要になる代表的な業務要件として、経時的なデータを記録したテーブルを使って、時系列分析を行うケースがあります。例えば、ある会社の年商を記録する次のようなテーブルを考えます。

Sales
年度(year) 年商(億円)(sale)
1990 50
1991 51
1992 52
1993 52
1994 50
1995 50
1996 49
1997 55
年商の推移
年商の推移

 このデータを使って、「前年に比べて年商が増えたのか、減ったのか、変わらなかったのか」をSQLで出力します。試しに「変わらなかった」パターンを求めてみます。この場合、テーブルから現状維持の年、つまり93年と95年を求めます。手続き型言語の考え方に従えば、

  1. 年度で昇順にソートする。
  2. ループさせて1行ずつ直前の行のsale列と比較する

 というやり方になります。しかしもちろん、SQLでこんな発想をしてはいけません。こういうときは、「Sales」テーブルとは別に、「前年の行」を保持する集合(S2)を、もう1つ追加しましょう。

前年と年商が同じ年度を求める その1:相関サブクエリの利用
SELECT year,sale
  FROM Sales S1
 WHERE sale = (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1)
 ORDER BY year;
結果
year   sale
-----  -----
1993   52
1995   50
 

 サブクエリ内の「S2.year = S1.year - 1」という条件によって、比較対象の行を1行「ずらして」いるわけです。相関サブクエリと自己結合は同値変換可能な場合が多いので、自己結合で書けば次のようになります。

前年と年商が同じ年度を求める その2:自己結合の利用
SELECT S1.year, S1.sale
  FROM Sales S1, 
       Sales S2
 WHERE S2.sale = S1.sale
   AND S2.year = S1.year - 1
 ORDER BY year;

 どちらの方がパフォーマンスが良いかというのは、一概には言えません。環境によって左右されるので、比較してみてください。

 では次にこれを応用して、各年度について、前年に比べて成長したのか、後退したのか、それとも現状維持だったのかを一度に求めてみましょう。

前年との比較結果を全年度について一覧表示する


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

著者プロフィール

  • ミック(ミック)

    主にOracleを使ったデータウェアハウス業務に従事するDBエンジニア。 HPのコンテンツ『リレーショナル・データベースの世界』。 著書: 『達人に学ぶ SQL徹底指南書』(翔泳社、2008) 訳書: ジョー・セルコ『SQLパズル 第2版』(翔泳社、2007) 講演資料:...

バックナンバー

連載:達人に学ぶSQL

もっと読む

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