PostgreSQL 随机数据生成(tablefunc.normal_rand 指定mean stddev)

2021-09-28 14:48:36

背景

生成测试数据，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)