SQLアタマアカデミー

第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う (4)連番の生成

この記事を読むのに必要な時間:およそ 1.5 分

連番の生成~どうしてもループが嫌と言うならしかたない~

これまで,あらかじめ複数行を保持するテーブルを対象に連番を割り当てていました。今度は動的に連番を生成する方法を考えます。

ループを使わずに1行のデータをn行に増やす

表7のような1行だけデータを持つテーブルがあります。データの中身は重要ではないので気にしないでください。

表7 OneRow

col1col2col3
testdatacol

みなさんに考えてほしいのは,この1行のデータを3行に増やしてもらうことです。しかもその際,図6のように連番を付与します。

図6 連番列「seq」が追加されている

seq col1 col2 col3
--- ---- ---- ----
  1 test data col
  2 test data col
  3 test data col

ここで条件が1つあります。それは,3行に限らずn行に簡単に一般化できる方法であることです。もしこの問題を手続き型言語で解くなら,3回ループして,カウンタの変数を連番列に使えばよいでしょう。問題とも呼べないぐらい簡単な話です。しかしSQLにおいては,基本的に「ループ」という手続きを使いません。代わりにSQLは,集合同士の演算によって集合を次々と組替え,求める集合にたどり着きます。

効率的な演算は何か

おそらく誰もが最初に思いつく方法は,UNIONで3つの行を「足し算」することでしょうリスト11)。重複行は発生しないので,UNION ALLが利用できます。確かにこれでも求める結果は得られます。しかしお世辞にも「拡張性の高い」コードとは呼べません。「1000万行生成したい」と言われたら,この方法を使う猛者はいないでしょう。

リスト11 UNION で行を足す:拡張性に欠ける

SELECT 1, col1, col2, col3
  FROM OneRow
 UNION ALL
SELECT 2, col1, col2, col3
  FROM OneRow
 UNION ALL
SELECT 3, col1, col2, col3
  FROM OneRow;

ではどうするか? 数を増やしたいなら,足し算よりもっと効率的な演算があるではありませんか。そう,掛け算です。これを利用しない手はありません。SQLにおける掛け算に相当するのは結合です。したがってこのケースならば,表8のような補助テーブルを用意してクロス結合すれば,「1×3=3」という演算のできあがりですリスト12)。

表8 補助テーブル

seq
1
2
3

リスト12 結合で行を掛ける:拡張性に富む


SELECT S.seq, O.col1, O.col2, O.col3
  FROM Seq S CROSS JOIN OneRow O;

連番ビューSequenceを作る

あとはSeqテーブルの行数を増減させることができれば,連番つきで何行でも行数を増やすことが可能になります。そのためには,Seqテーブルをビューにしておくことが最も簡単でしょう。まずは十分な大きさを持つ連番テーブルSequenceを作る必要がありますが,これは伝統的にのように各桁の数字を組み合わせることで可能なことが知られています表9,リスト13)。

表9 Digits

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

リスト13 0~999 までの連番を保持するシーケンス・ビューを作る

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

--シーケンス・ビューから1~3まで取得
CREATE VIEW Seq (seq)
AS SELECT seq
     FROM Sequence
    WHERE seq BETWEEN 1 AND 3;

Sequenceビューを作るクエリは,各桁の数字0~9についてクロス結合ですべての組み合わせを求めています。D1が1の位,D2が10の位,D3が100の位を表します。あとは同様にDn集合を追加することで,どんなに大きな連番テーブルでも思うままに作れます。そうして作られたSequenceビューから,BETWEEN述語によって適当な範囲を切り出しているわけです。SQLらしい,集合演算を駆使した方法です。

なお,パフォーマンス上の注意を促しておくと,クロス結合はSQLの演算の中で最も高コストなため,できる限りビューを使わないようSequenceをテーブルとして保持しておくのが現実的です。そうすればseq列の主キーのインデックスが利用できるため,Seqビューを作るクエリが高速化されます。

また,連番生成の方法は,実装依存のものも含めれば上記以外にも何通りかあります。興味深いものが多いので,みなさんも考えて/探してみてください注4)。

注4)
回答は、筆者のWebページ内にある「SQLアタマアカデミー」サポートページに掲載しています。

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入