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

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

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

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

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

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万行生成したい」と言われたら、この方法を使う猛者はいないでしょう。

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⁠）⁠。

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

連番ビューSequenceを作る

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

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]⁠。