SQLで数列を扱う

はじめに

　関係モデルのデータ構造には、「順序」という概念がありません。必然的に、その実装であるリレーショナル・データベースのテーブルやビューにも、（たてまえ上）行列の順序がありません。同様にSQLも、順序集合を扱うことを直接的な目的とはしていません。

　そのため、SQLでの順序集合の扱い方は、最初から順序を扱うことを目的とした手続き型言語とファイルシステムのアプローチとはかなり異質なものになります。しかし、異質ではあるものの、そこに確固たる原理が存在することも確かです。一言で言えば、集合と述語――特に「量化子」（quantifier）と呼ばれる特別な述語の使い方が鍵になります。

　本稿では、SQLを使って、数列や日付などの順序を持つデータを扱う方法を解説します。単にTipsを列挙するだけでなく、できれば、解法に共通する基本的な原理を取り出し、未知の問題に取り組むときにも適用できる一般的な指針としてまとめてみたい、と考えています。

稼働環境

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

対象読者

　自己結合と相関サブクエリを多用するので、この2つについての知識があると望ましいです。これらについて知らない方は、 「自己結合の使い方」「相関サブクエリで行と行を比較する」を先に読むと理解が増すでしょう。

連番を作ろう

　SQLで連番を作ることを考えましょう。最近では多くの実装がシーケンス・オブジェクトを持っているので、連番を1個づつ順に取得する場合なら、これを使うことができます。しかし、1つのSQLで、任意の大きさの連番が欲しい場合はどうすればよいでしょうか。例えば、0から99までの数をずらっと100行作りたい、という場合です。実装依存でよいならば、CONNECT BY（Oracle）やWITH句（DB2、SQLServer）を使う方法がありますが、ここでは実装非依存の方法に限定します。

　この問題を考える前に、突然ですが、ちょっと次のクイズを考えてください。

問題：00から99までの100個の数の中には、0、1、2……、9の各数字は、それぞれ何個含まれているか？

　1桁の数は前ゼロをつけて「01」、「07」のように表記します。紙には書かず、頭の中だけで考えてみてください。それでは、スタート。

　……できましたか？ 正解は、どの数字も20個。例えば「1」の文字について、一の位と十の位にそれぞれ現れる箇所を数えます。すると、一の位が1の数が10個、十の位が1の数も10個。「11」はどちらにも含まれますが、この数だけは1を2個含むので、ダブルカウントにはなりません。

　何が言いたいかと言うと、ある数を「文字」として見た場合、それは各位を構成する数字を組み合わせて作られる集合として把握できる、ということです。クイズタイム終わり。

　さて、本題に戻りましょう。まず、各位の構成要素となる数字を保持する「数字テーブル」を作ります。これは10行固定の読取専用テーブルです。どんな巨大な数も、この10個の数字を組み合わせて作られることは明らかです。

　すると、0～99までの数は、2つのDigits集合の直積を取ることで作れます。

SELECT D1.digit + (D2.digit * 10)  AS seq
  FROM Digits D1, Digits D2
ORDER BY seq;

seq
-----
 0
 1
 2
：
：
：
98
99

　D1が一の位、D2が十の位を表します。後は同じ要領でD3、D4･･････と追加していけば、何桁の数でもお好みで作れます。また、0を除外したり、「542」のような中途半端な数で止めたい場合は、WHERE句に条件を追加しましょう。

SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100) AS seq
  FROM Digits D1, Digits D2, Digits D3
 WHERE D1.digit + (D2.digit * 10) + (D3.digit * 100) BETWEEN 1 AND 542
ORDER BY seq;

　お気づきでしょうが、この連番の生成方法は、見事に数の「順序」という性質を無視しています。この連載でも何度か取り上げたノイマン型の順序数の定義と比較してみると、その相違が際立ちます。再帰集合を使った定義では、0を定義して初めて「次の数」である1が得られ、1を定義した後に2が得られる、という順序がありました（それゆえ、ランキングや累計のような順序関係を記述するのに適していたのです）。

　一方、順序の概念を捨てて、数を「数字の組み合わせ」に過ぎないと見なしたのがこのアプローチです。その意味でSQL的な方法ではあります。

　このクエリをビューとして保存しておくことで、簡単なSELECT文でいつでも連番を取得できるようになります。

CREATE VIEW Sequence (seq)
AS SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100)
     FROM Digits D1, Digits D2, Digits D3;

SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 100
ORDER BY seq;

　これはいろいろな用途に使える便利なビューですから、1つ作っておくと、多くの局面で役に立ちます。

欠番を全部求める

　以前、「HAVING句の力」で、連番の歯抜けを探す方法を紹介しました。そのときの解法は、歯抜けが複数あった場合は、その最小値だけを取得するものでした。しかし、この記事を読んだ方の中には「どうせなら欠番を全部求めたい」という欲張りな願望を持った人もいるのではないでしょうか。

　お任せください。前問で作ったシーケンス・ビューを使えば、そんな欲張りさんの要求も見事かなえられます。0～nまでの歯抜けのない自然数の集合が自在に作れるわけですから、後は比較したいテーブルと差集合演算をするだけです。そしてSQLで差集合を求める方法は、豊富に用意されています。EXCEPT演算子を持っている実装なら朝飯前、NOT EXISTSやNO INを使ってもいいでしょう。はたまた外部結合を使うなんていう変り種まであります。

　サンプルに、次のような歯抜けの連番を持つテーブルがあるとします。

　最小値が1、最大値が12ですから、作るシーケンスの範囲もこれに合わせます。下のクエリはいずれも、欠番の3、9、10を返します。

SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
EXCEPT
SELECT seq FROM SeqTbl;

SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
   AND seq NOT IN (SELECT seq FROM SeqTbl);

seq
-----
3
9
10

　前回で不満が残った方も、これですっきり解決でしょう。

　また、実行コストは高くなりますが、BETWEEN述語の引数を一般化して、検証したいテーブルの最小値と最大値を動的に組み込むこともできます。

SELECT seq
  FROM Sequence
 WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl)
               AND (SELECT MAX(seq) FROM SeqTbl)
EXCEPT
SELECT seq FROM SeqTbl;

　これは、下限の値が必ずしも固定的でないテーブルを調べる場合などに便利です。2つのサブクエリは、非相関なので1度しか実行されませんし、seq列にインデックスがあれば、極値関数を高速化できます。