PostgreSQL 随機資料生成(tablefunc.normal_rand 指定mean stddev)

标簽

PostgreSQL , tablefunc , normal_rand , mean , stddev

https://github.com/digoal/blog/blob/master/201807/20180714_01.md#%E8%83%8C%E6%99%AF 背景

生成測試資料，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)
           

https://github.com/digoal/blog/blob/master/201807/20180714_01.md#%E4%BE%8B%E5%AD%90 例子

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)  
           

https://github.com/digoal/blog/blob/master/201807/20180714_01.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL 生成任意基數數獨 - 4》 《PostgreSQL 生成任意基數數獨 - 3》 《PostgreSQL 生成任意基數數獨 - 2》 《PostgreSQL 生成任意基數數獨 - 1》 《PostgreSQL 如何快速建構 海量 逼真 測試資料》 《生成泊松、高斯、指數、随機分布資料 - PostgreSQL 9.5 new feature - pgbench improve, gaussian (standard normal) & exponential distribution》 https://www.postgresql.org/docs/devel/static/tablefunc.html HTAP 測試