今回、SQLのパフォーマンステストのためにたくさんテストデータを作成したため、その知見を少しでも残します。
基本ロジック
基本的なロジックは、親のテーブルにランダムで作成、子のテーブルやコードなどの参照したいものはVLOOKUPで参照していくとなります。
前提
この記事は、以下の方針で進めていきます。
- 中盤から終盤以降で、DDLまたはテーブル定義書（hogehoge.xlsx）、コード定義が手に入る前提
- エクセル関数をメインに、他のテーブルと連動して動的にデータを作成する
- 揮発性のエクセル関数（rand関数など）を使用するため、同一のデータの再生成は不可能
特に最後の条件は確認お願いします。
もちろん、csvを作成するため、ある程度の復元は可能です。作成したエクセルにコピペしなおせばOKです。ですが以下の2点の注意点に気を付けなければいけません。
- csvからコピペするため、値が入力される
- 値が固定となるため、今後のデータ変更時に、ズレが生じる恐れアリ
- 文字列の0詰めが消えてしまう恐れアリ
- コードとの結合がうまくいかなくなる可能性がｶﾞｶﾞｶﾞｶﾞ
目次
では前置きが長くなりました。
以下の内容でお送りいたします。
- テーブル情報取得
- ヘッダー作成（物理・論理名、制約、桁数、データ型）
- セルの書式設定
- id列作成
- データランダム作成
- VLOOKUP基本▶応用
- サクラエディタへのコピペ、csv作成
テーブル情報取得
DDLまたはテーブル定義書から情報を抜き出します。
ここに関してはそれぞれのフォーマットがあるため、「こちらに抜き出したものを用意してあります」と料理番組風に進めさせていただきます。
しいて言うなら、DDLからの場合、正規表現を使うと抜き出しが楽になる気がします。
Ctrl+Zで戻しながら、作業を行えばテーブル一覧が取得できます。
カラム物理名を取得
置換前：^( | +)([^ ]+).+
置換後：$2
カラムの桁数を取得
置換前：^( | +)([^ ]+).+(.+).+
置換後：$3
データ型を取得(Postgres参照)
文字列
置換前：.+(character varying).+
置換後：String
整数
置換前：.+(integer).+
置換後：$1
小数
置換前：.+(decimal).+
置換後：$1
この手順が終了すると、テーブルの型定義が縦にならんで取得できていると思います。
ヘッダー作成（物理・論理名、制約、桁数、データ型etc...）
取得したテーブル情報をExcel上に反映していきます。
基本的に、ここは個人の自由とは思っております。上記のように、「物理名」「論理名」「外部制約の参照先」「データ型」「桁数」「作成時の数式」として保持してあると便利かと思っています。
セルの書式設定
Ctrl+1でセルの書式の設定を行います。ユーザー定義などで以下の通り設定すると桁数などを調整できます。
0詰め（5桁）
00000
ダブルコーテーション付与
”＠”
id列作成
検索に用いるid列を作成、昇順にソートします。
他のテーブルから参照する際、どの列を意識しているのかを結合して作成します。
桁数5桁と桁数指定なし
=TEXT(参照先1,"00000")&TEXT(参照先2,)
データランダム作成
https://www.excelist.net/function/RANDBETWEEN.html
RANDBETWEEN関数を用いて、ランダム作成を行います。
またコードなどを自動生成を行う場合、コードを別シートに記載してidとして列数を用いてINDEX関数で参照しています。
数字のランダム作成
=RANDBETWEEN(最小値,最大値)
数字のランダム作成（下2桁00）
=RANDBETWEEN()*100
文字列のランダム作成
=CHAR(RANDBETWEEN(CODE("あ"),CODE("ん"))))
コードの生成
=INDEX(【参照先シート名】【取得列】:【取得列】,
MATCH( RANDBETWEEN(1,COUNT(【参照先シート名】【取得列】:【取得列】)-【コードのヘッダー数】) ,【参照先シート名】【取得列】:【取得列】,0))
VLOOKUP基本▶応用
https://raku2life.com/excel-comb-indexmatch/
https://tech.nikkeibp.co.jp/it/pc/article/technique/20081209/1010410/
追加、削除されても参照先がずれないよう以下の構造を組み合わせて参照します。
=INDEX(【参照先シート名】【取得列】:【取得列】,
MATCH(【検索キー】,【参照先シート名】【取得列】:【取得列】,0))
取得列の取得(論理名:１行を参考)
=LEFT( ADDRESS(1,MATCH("【論理名】",【参照先シート名】1:1,0),2),
FIND("$",ADDRESS(1,MATCH("【論理名】",【参照先シート名】1:1,0),2) )-1 )
サクラエディタへのコピペ、csv作成
作成したデータからサクラエディタへコピペします。
タブをカンマに置換すればCSVにも対応できます。
感想
以上、テストデータの作成となります。
基本的にランダムで作ってしまうので、条件がある場合IF関数でRANDBETWEEN関数の範囲を指定することでほしいデータを作成することができます。
…VLOOKUPでやるって言ったのにINDEXでしかやってない