PostgreSQL 随机数据生成(tablefunc.normal_rand 指定mean stddev)
背景
生成测试数据,PostgreSQL有各种各样的方法,建本文参考文档。
本文提供一种生成指定任意个数、平均值、标准方差的数值。使用tablefunc插件的normal_rand即可。
F.38.1.1. normal_rand
normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
normal_rand produces a set of normally distributed random values (Gaussian distribution).
numvals is the number of values to be returned from the function. mean is the mean of the normal distribution of values and stddev is the standard deviation of the normal distribution of values.
For example, this call requests 1000 values with a mean of 5 and a standard deviation of 3:
test=# SELECT * FROM normal_rand(1000, 5, 3);
normal_rand
----------------------
1.56556322244898
9.10040991424657
5.36957140345079
-0.369151492880995
0.283600703686639
.
.
.
4.82992125404908
9.71308014517282
2.49639286969028
(1000 rows)
例子
postgres=# create extension tablefunc;
select normal_rand(10,10,0.1);
例子
postgres=# select normal_rand(20,10,0);
normal_rand
-------------
10
10
10
10
10
10
10
10
10
10
10
10
10
10
10
10
10
10
10
10
(20 rows)
postgres=# select normal_rand(20,10,1);
normal_rand
------------------
9.72520225550088
10.0119482459389
10.5916890547498
10.1463735999097
8.92547759747822
11.6355890779347
10.9720784944947
10.1733686382872
10.5646035351755
10.1595366384555
10.2430859539833
10.1603680100806
8.76754663101294
8.07820227599032
8.37749903746811
13.7146194036438
10.7485346808754
10.0266158727721
10.5129017656189
11.0932448676642
(20 rows)
参考
《PostgreSQL 如何快速构建 海量 逼真 测试数据》
https://www.postgresql.org/docs/devel/static/tablefunc.html