断絶区間を求める

最後に、連番を扱うための「これぞSQL」という集合指向的な技法を紹介して終わりましょう。

表10は1から始まる連番テーブル…だったのですが、諸事情により途中に歯抜けが生じて、いささか汚い状態となっています。考えてほしいのは、歯抜けのカタマリを図7のように表示するクエリです。

gap_start  ～   gap_end
--------- ---- -------
        2  ～        2
        5  ～        6
       10  ～       11

集合指向的な解法

方法はいくつかありますが、ここでは『SQLパズル第2版』の「パズル9　席空いてますか？」で紹介されているR・レムレーのアイデアを詳しく解説しましょう。

手続き型言語であれば、1行ずつテーブルから読み出し、当該の行と次の行の数値の差分が1でなければ、その間には欠番がある、という行レベルの判定処理を記述します。

一方、伝統的なSQLでは当該の行を起点に考えるのは同じですが、処理の単位が「集合」になります。リスト14でN2.numは「ある行のN1.numの数値より大きな数値の集合（上界集合⁠）⁠」⁠（⁠注5）として条件づけされています（ON N2.num > N1.num⁠）⁠。この集合群を一覧表示すると、表11のようになります（リスト14⁠）⁠。

SELECT (N1.num + 1) AS gap_start,
       '～',
       (MIN(N2.num) - 1) AS gap_end
  FROM Numbers N1 INNER JOIN Numbers N2
    ON N2.num > N1.num
 GROUP BY N1.num
HAVING (N1.num + 1) < MIN(N2.num);

このS1～S6のうち、最小上界が「N1.num+1」にならないS1、S3、S6の3つの集合に注目してください。当該の数値N1.numのすぐ後ろの数が最小上界と一致しないという事実はそこに断絶があるということを示します。これがHAVING句の「(N1.num+1) < MIN(N2.num);」という条件の意味です。

あとは、N1.numの1つ後ろの数が欠番の開始値、N2.numの1つ前の数が終了値になる、というしかけ。集合指向的な名答です。

おわりに

手続き型言語とSQLの考え方の相違点を浮き彫りにしつつも、両者の架橋点を探すというコンセプトで話を進めてきたつもりでしたが、いかがだったでしょうか。次回以降も、1つテーマを決めて、それに関連するSQLの興味深いテクニックを取り上げていく予定です。

参考資料

・J.セルコ『SQLパズル 第2版』

（翔泳社、2007、ISBN：978-4-7981-1413-2）

「 パズル4 入館証」と「パズル56 ホテルの部屋番号」では連番を更新する方法が、「⁠パズル9 席空いてますか？」では欠番のカタマリを取り出す見事な解法を学ぶことができます。また、非グループ化（GROUP BYの逆演算）に連番テーブルを利用する斬新なトリックを利用する「パズル48 非グループ化」も見逃せません。

・ミック『達人に学ぶ　SQL徹底指南書』

（翔泳社、2008、ISBN：978-4-7981-1516-0）

「1-4 HAVING句の力」と「1-10 帰ってきたHAVING句」では、自然数の集合とテーブルに含まれる数とに一対一対応がつくかどうかを調べることで欠番探索を行う方法を、また、本稿でも取り上げたDigits集合から連番を生成するクエリについては「1-9 SQLで数列を扱う」で詳しく図解しています。

・J.セルコ『Joe Celko’s Analytics And Olap in SQL』

（Morgan Kaufmann Pub、2006、ISBN：978-0-1236-9512-3）

連番を使ったメジアンの求め方は「8.1 OLAP Functionality8.1」で紹介されているアイデア。OLAPのツールという視点からSQLの使い方を解説したユニークでおもしろい本ですが、邦訳はなし。