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

はじめに

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

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

稼働環境

• Oracle
• SQL Server
• DB2
• PostgreSQL
• MySQL（バージョン4.1以上）

対象読者

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

成長・後退・現状維持

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

年商の推移

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

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

　というやり方になります。しかしもちろん、SQLでこんな発想をしてはいけません。こういうときは、「Sales」テーブルとは別に、「前年の行」を保持する集合（S2）を、もう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行「ずらして」いるわけです。相関サブクエリと自己結合は同値変換可能な場合が多いので、自己結合で書けば次のようになります。

SELECT S1.year, S1.sale
  FROM Sales S1, 
       Sales S2
 WHERE S2.sale = S1.sale
   AND S2.year = S1.year - 1
 ORDER BY year;

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

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

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