PostgreSQLスキルアップノート（自己啓発のための個人サイト）
算術関数・ランダム値生成（psql用）
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 算術関数・ランダム値生成（psql用）
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.2.3 (CentOS6.2)
2013/03/17
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
テストデータなどの生成用途向け。
psqlコマンドラインから扱えるごく簡易的なもの。
【マニュアル】
→●[マニュアル]
【マニュアル参考】
「SQL差し替え」→●[マニュアル]
【その他】
−
■１■ 意図した桁数や整数範囲をランダムに生成(基本形）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．意図した桁数以内
──────────────────────────────
１桁以内
【□】 SELECT floor(random()*10);
２桁以内
【□】 SELECT floor(random()*100);
３桁以内
【□】 SELECT floor(random()*1000);
floor
-------
623
【注意】round関数など四捨五入系では適正なランダムにならないので使用しない。
必ず切捨て系の方法を使うこと。
詳細はこのページの最後に記載。
２．指定した範囲内（以上、以下）
──────────────────────────────
val1以上、val2以下のランダムな整数
select floor(val1 + random() * (val2 + 1 - val1));
例）2013以上、2020以下
select floor(2013 + random() * (2020 + 1 - 2013));
■２■ ランダム値生成用パーツ（psql用）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
psqlの「SQL差し替え」を使用して簡易的にモジュールにしておく。
整数用ランダム値のみ「nm1」と「nm2」を任意の範囲にカスタマイズする。
以外は固定。
nm1とnm2は後から変更も可能。
【□】 ランダム生成パーツの定義
┌────────────────────────────┐
-- 整数用ランダム値(nm1以上nm2以下のランダム値を生成)
\set nm1 1000
\set nm2 9999
\set nm 'floor(:nm1+random()*(:nm2+1-:nm1))'
-- アルファベット大文字１文字用ランダム値
\set au 'chr(floor(65+random()*(90+1-65))::int)'
-- アルファベット小文字１文字用ランダム値
\set al 'chr(floor(97+random()*(122+1-97))::int)'
└────────────────────────────┘
以上の定義をした上で、
・数値は select :nm;
・大文字は select :au;
・小文字は select :al;
などとしてランダム値を取り出す。
・文字を連結する場合は、
select :au||:al||:au;
あるいは、
\set au5 :au||:au||:au||:au||:au||:au
などと定義しておく。
・大量生成する場合にはgenerate_seriesと組み合わせる。
・任意の文字数等には非対応。
■３■ ランダム値生成用パーツの使用例
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 先に前項の「ランダム生成パーツの定義」を実施しておく。
例）ランダムな数値を１件出力。
──────────────────────────────
【□】 SELECT :nm;
floor
-------
7055
値はデフォルト定義の４桁(1000〜9999)で出力される。
例）指定範囲を100000から999999に変更。
──────────────────────────────
【□】 \set nm1 100000
【□】 \set nm2 999999
【□】 SELECT :nm;
floor
--------
276603
例）アルファベット大文字３文字と数字
──────────────────────────────
【□】 select :au||:au||:au||:nm;
?column?
-----------
BPE245760
例）連続生成
──────────────────────────────
【□】 \set nm1 1
【□】 \set nm2 9999
【□】 SELECT :al||:al||:au||:nm from generate_series(1,10000);
?column?
----------
xzA9451
smR944
vbD9870
pzM1700
eaY4358
lpA4387
dtE1295
wdC7846
cdS8103
:
:
例）連続生成・連番付き
──────────────────────────────
【□】 \set nm1 1
【□】 \set nm2 9999
【□】 SELECT generate_series(1,10000),:al||:al||:au||:nm;
generate_series | ?column?
-----------------+----------
1 | kgL6829
2 | vyV2251
3 | erM6409
4 | guX2669
5 | hlT3876
6 | imV1159
7 | pzQ3872
8 | tsD1280
9 | ypV7587
10 | nqZ6700
11 | hlI5340
12 | gfU5252
:
:
■■ 【参考】ランダム値を生成したはずがランラムでない・・
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
random関数の結果を安易に扱ってしまうと本当のランダムではなくなってしまう場合がある。
以下の方法で方法１と方法２は範囲の最初と最後の値の出現頻度が半減する。
いずれも四捨五入していることに起因（当然といえば当然の動作）
方法１）キャスト × SELECT (random()*9)::int;
方法２）round関数 × SELECT round((random()*9)::numeric,0);
方法３）floor関数 ○ SELECT floor(random()*10);
方法４）trunc関数 ○ SELECT trunc((random()*10)::numeric,0);
例 0から9の値をランダム生成して本当にランダムか確認
方法１（方法２も同様）・・×
──────────────────────────────
select c1,count(*) from(
select generate_series(1,10000) as c0,(random() * 9)::int as c1
) a
group by c1 order by c1;
c1 | count
----+-------
0 | 543 ★他の値の半分しか出現しない×
1 | 1096
2 | 1174
3 | 1119
4 | 1128
5 | 1104
6 | 1150
7 | 1066
8 | 1101
9 | 519 ★他の値の半分しか出現しない×
方法３（方法４も同様）・・○
──────────────────────────────
select c1,count(*) from(
SELECT generate_series(1,10000) as c0,floor(random()*10) as c1
) a
group by c1 order by c1;
c1 | count
----+-------
0 | 1037
1 | 993
2 | 1023
3 | 980
4 | 945
5 | 1011
6 | 988
7 | 1026
8 | 996
9 | 1001
以上
