如何使用mysql用户定义函数生成高斯分布 [英] how to generate a gaussian distribution using mysql user-defined function
问题描述
我想创建一个MySQL用户定义的函数,形式如下:
sample_gaussian(mean,stdev),返回一个随机化的
值,采用均值和标准$ b的高斯分布$ b用户输入参数的偏差。 MySQL已经有一个
函数rand(),它返回一个随机数,所以我只需要
知道一些伪代码来限制/转换那个值
,这样它就可以进入正确的分布。
有什么建议吗?
BTW-这是我的第一个stackoverflow问题,所以请原谅
我,如果这个问题要求太多的用户
在回答我自己的问题时,这里是一个MySQL用户定义的函数,返回一个从具有给定均值和标准差的高斯分布。
如果存在,降低函数gauss;
DELIMITER //
CREATE FUNCTION gauss(mean float,stdev float)RETURNS float
BEGIN
set @ x = rand(),@ y = rand();
set @gaus =((sqrt(-2 * log(@x))* cos(2 * pi()* @ y))* stdev)+ mean;
return @gaus;
END
//
DELIMITER;
要验证这实际上是返回一个高斯分布,您可以生成一系列这些,然后绘制直方图:
创建临时表temp(id int,rando float);
插入temp(rando)select gauss(2,1); #重复此操作500次
插入temp(rando)从any_table_with_500中选择gauss(2,1)+ _entries limit 500;
从temp组中选择round(temp,1),count(*)by round(temp,1)#创建一个直方图
如果您在Excel或绘图工具中绘制该柱状图,您将看到钟形的正常曲线。
I like to use MySQL to do quantitative analysis and statistics. I would like to make a MySQL user-defined function of the form: sample_gaussian(mean, stdev) that returns a single randomized value sampled from a gaussian distribution having mean and standard deviation of the user-entered arguments. MySQL already has a function rand() that returns a random number, so I just need to know some pseudocode for constraining/transforming that value so that it falls into the right distribution. Any suggestions?
BTW- This is my first stackoverflow question, so please forgive me if this question is asking too much of users on this site.
In answer to my own question, here is a MySQL user-defined function that returns a single random value sampled from a Gaussian distribution with a given mean and standard deviation.
DROP FUNCTION IF EXISTS gauss;
DELIMITER //
CREATE FUNCTION gauss(mean float, stdev float) RETURNS float
BEGIN
set @x=rand(), @y=rand();
set @gaus = ((sqrt(-2*log(@x))*cos(2*pi()*@y))*stdev)+mean;
return @gaus;
END
//
DELIMITER ;
To verify that this is in fact returning a Gaussian distribution, you can generate a series of these, then plot a histogram:
create temporary table temp (id int, rando float);
insert into temp (rando) select gauss(2,1); # repeat this operation 500 times
insert into temp (rando) select gauss(2,1) from any_table_with_500+_entries limit 500;
select round(temp,1), count(*) from temp group by round(temp,1) # creates a histogram
If you plot that histogram in excel or graphing tool of choice, you'll see the bell shaped normal curve.
这篇关于如何使用mysql用户定义函数生成高斯分布的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!