SQLを使用してランダムデータを生成する｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2019.08.22

SQLを使用してランダムデータを生成する

Ted Holt 著

SQL照会のパフォーマンスを適切にテストするために、ランダムデータを持つ大きなデータベース表を生成する必要があるとします。セキュリティ上の理由から、表の本番バージョンをコピーすることはできません。大量の（実に大量の）ランダムデータを生成する方法が必要です。そして、このシナリオが単なる仮定の話ではないとします。

SQL照会を本番で使用する前に、本番のようなデータセットに対してテストを行ってみる必要があります。25行（レコード）のテストデータセットに対して照会を実行していたとしても、それが本番に導入され、数千行さらには数百万行の本番データセットに立ち向かうことになったときには、不愉快な不意打ちをいくつも食らう結果に終わるかもしれません。それほど多くのデータを入力することはできないのは明らかですが、どのようにしてそのようなテストデータセットを生成したらよいのでしょうか。使用しているのはSQLです。1つの例を示しましょう。

1つの表に対して照会をテストするとします（これは現実的ではありませんが、例を小さくできるため、テクニックを説明しやすくなります）。すでに表のコピーがあり、その表へデータをロードするだけです。ただし、これは説明用の例であり、以下は、説明用の表を作成するステートメントです。

create table mylib.Cust24
  ( ID       dec(5),
    Name     char(20),
    Type     dec(1),
    Balance  dec(5,2),
    DueDate  date,
  primary key (ID));

これらの列（フィールド）にデータを入れる必要があります。ID（主キー）には、連続番号を使用できます。その場合、再帰的共通表式がその連続番号を生成します。

with list (IDNbr) as 
  (values(1) 
    union all
   select IDNbr + 1
     from list
    where IDNbr < 25)
select IDNbr from list

これで、連続するID番号を持つ25行が生成されます。もちろん、さらに多くの行を生成することもできます。

ここから、他の列にランダムデータを入れてゆきます。Type列は、1～3の整数である必要があります。

dec(rand()*3+1, 1,0)

rand()関数は、0～1の範囲の乱数を生成します。その数値に3を乗算することによって、0～3の範囲の乱数に変わります。その数値に1を加算することで、1～4の範囲の数値になります（その数値が4になる可能性はほとんどありません。乱数ジェネレーターから0または1が返されたことはなく、それらの間の数値のみです）。dec()関数は小数点以下を切り捨て、整数だけにします。

Balance列は、5桁のパック10進数です。正の数にする必要があるなら、Type列の式と同じような式を使用することができます。

dec(rand()*999, 5, 2)

負の数もいくつか必要なら、そうなるように式を調整します。

dec(rand()*1000, 5, 2)-500

ランダムな日付の生成は難しくありません。rand()関数を使用して日数を生成し、現在の日付などの参照日付と合算します。

current date + int(rand()*100) days

この式は、現在の日付に最大で100日を加算します。

ややこしい部分を最後に取っておきました。ランダムなName値を生成しなければなりません。rand()関数が返すのは数値のみです。それらの数値を文字列に変換するには、どのようにしたらよいのでしょうか。多少複雑ですが、難しくはありません。以下はその1つのやり方です。

with list (IDNbr, RandName) as 
  (values(1,         dec(rand()*100000,5,0)) 
    union all
   select IDNbr + 1, dec(rand()*100000,5,0)
     from list
    where IDNbr < 25)
select IDNbr,
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName       ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz', 
              int(mod(RandName*0.1   ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName*0.01  ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName*0.001 ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName*0.0001,53)+1), 1)

部分ごとに見てゆきます。

with list (IDNbr, RandName) as 
  (values(1,         dec(rand()*100000,5,0)) 
    union all
   select IDNbr + 1, dec(rand()*100000,5,0)
     from list
    where IDNbr < 25)

RandNameは、5桁のパック10進数の乱数です。RandNameは、文字の配列の添え字を生成するのに使用できます。

substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
       int(mod(RandName,53)+1), 1)

mod()関数は、RandNameを53（リテラルの文字の数）で除算して、0～52の数値を返します。1を加算することで、有効な文字列の添え字の値になります。Substr()は、リテラルから1つの文字を返します。これで1つのランダムな文字（アルファベットまたはスペース）が得られます。

RandNameを他の10の累乗で除算することで、より多くの文字を生成することができます。

substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz', 
           int(mod(RandName*0.1 ,53)+1), 1)
substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
           int(mod(RandName*0.01,53)+1), 1)

照会全体を以下に示します。

with list (IDNbr, RandName) as 
  (values(1,         dec(rand()*100000,5,0)) 
    union all
   select IDNbr + 1, dec(rand()*100000,5,0)
     from list
    where IDNbr < 25)
select IDNbr, 
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName       ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz', 
              int(mod(RandName*0.1   ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName*0.01  ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName*0.001 ,53)+1), 1) concat
   substr('ABCDEFGHIJKLMNOPQRSTUVWYXZ abcdefghijklmnopqrstuvwxyz',
              int(mod(RandName*0.0001,53)+1), 1) as Name,
   dec(rand()*3+1, 1,0) as Type,
   dec(rand()*1000, 5, 2) as Balance,
   current date + int(rand()*100) days as DueDate
  from list;

うまく行きましたか。判断はお任せします。

select * from cust24;

参照用に、IBM Knowledge Centerの2つの記事のリンクを示します（「関連記事」欄の最初の2つのリンク）。それらはDb2 for i用ではありませんが、参考になるところがあるかもしれません。そこで使用されている照会は、ご使用のシステムでは機能しないかもしれないことに注意してください。1つの例では、translate()関数の2つ目のパラメーターにmod()関数を指定することによって文字データを生成していますが、Db2 for iでは（現時点では）そうすることはできません。

ランダムデータの大きな表を使用することの1つの欠点は、すべてのケースがテストされたと確信できないかもしれないことです。けれども、コードを完全に実行するのに必要となるすべてのデータを含む小さなデータセットで、そうしたテストはすでに完了しています。ランダムデータに対するテストの目的は、パフォーマンスであって、精度ではありません。