Shoeisha Technology Media

CodeZine(コードジン)

記事種別から探す

SQLで数列を扱う

SQLにおける順序を持ったデータの取り扱い方

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

SQLでは、基本的にデータに順序がないものとして扱います。それゆえ、順序を意識したデータの扱い方は、手続き型言語とファイルシステムのそれとはかなり異なるものになります。本稿ではその扱い方を、数列を例に解説し、解法の背後にある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個含むので、ダブルカウントにはなりません。

00~99までの数に、各数字は20個現れる
00 01 02 03 04 05 06 07 08 09
10 11 12 13 14 15 16 17 18 19
20 21 22 23 24 25 26 27 28 29
30 31 32 33 34 35 36 37 38 39
40 41 42 43 44 45 46 47 48 49
50 51 52 53 54 55 56 57 58 59
60 61 62 63 64 65 66 67 68 69
70 71 72 73 74 75 76 77 78 79
80 81 82 83 84 85 86 87 88 89
90 91 92 93 94 95 96 97 98 99

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

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

Digits
digit(数字)
0
1
2
3
4
5
6
7
8
9

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

連番を求める その1:0~99
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句に条件を追加しましょう。

連番を求める その2:1~542を求める
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文でいつでも連番を取得できるようになります。

シーケンス・ビューを作る(0~999までをカバー)
CREATE VIEW Sequence (seq)
AS SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100)
     FROM Digits D1, Digits D2, Digits D3;
シーケンス・ビューから1~100まで取得
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 100
ORDER BY seq;

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

欠番を全部求める

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

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

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

SeqTbl
seq(連番)
1
2
4
5
6
7
8
11
12

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

EXCEPTバージョン
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
EXCEPT
SELECT seq FROM SeqTbl;
NOT INバージョン
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列にインデックスがあれば、極値関数を高速化できます。


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

著者プロフィール

  • ミック(ミック)

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

バックナンバー

連載:達人に学ぶSQL

もっと読む

おすすめ記事

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