如何在 SQL Server 中生成正态分布的数字 [英] How to generate numbers with a normal distribution in SQL Server

问题描述

我正在尝试植入一些数据，是否可以在 SQL Server 中生成遵循正态分布曲线的数字?

I'm trying to seed some data, is there anyway to generate numbers in SQL Server that follow a normal distribution curve?

比如:我会指定平均值、标准差和计数，然后我会得到一个数字列表?

Like: I will specify the mean, the standard deviation and the count and I get a list of numbers back?

推荐答案

引用自 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135026 你可以试试

quoting from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135026 you might try with

CREATE FUNCTION [dbo].[udf_NORMSDIST]
(
            @x FLOAT
)
RETURNS FLOAT
AS
/****************************************************************************************
NAME: udf_NORMSDIST
WRITTEN BY: rajdaksha
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135026
DATE: 2009/10/29 
PURPOSE: Mimics Excel's Function NORMSDIST

Usage: SELECT dbo.udf_NORMSDIST(.5)


REVISION HISTORY

Date Developer Details
2010/08/11 LC Posted Function


*****************************************************************************************/

BEGIN 
DECLARE @result FLOAT
DECLARE @L FLOAT
DECLARE @K FLOAT
DECLARE @dCND FLOAT
DECLARE @pi FLOAT
DECLARE @a1 FLOAT
DECLARE @a2 FLOAT
DECLARE @a3 FLOAT
DECLARE @a4 FLOAT
DECLARE @a5 FLOAT

--SELECT @L = 0.0
SELECT @K = 0.0
SELECT @dCND = 0.0

SELECT @a1 = 0.31938153
SELECT @a2 = -0.356563782
SELECT @a3 = 1.781477937
SELECT @a4 = -1.821255978
SELECT @a5 = 1.330274429
SELECT @pi = 3.1415926535897932384626433832795

SELECT @L = Abs(@x)

IF @L >= 30
BEGIN
IF sign(@x) = 1
SELECT @result = 1
ELSE
SELECT @result = 0
END
ELSE
BEGIN
-- perform calculation
SELECT @K = 1.0 / (1.0 + 0.2316419 * @L)
SELECT @dCND = 1.0 - 1.0 / Sqrt(2 * @pi) * Exp(-@L * @L / 2.0) *
(@a1 * @K + @a2 * @K * @K + @a3 * POWER(@K, 3.0) + @a4 * POWER(@K, 4.0) + @a5 * POWER (@K, 5.0))
IF (@x < 0)
SELECT @result = 1.0 - @dCND
ELSE
SELECT @result = @dCND

END

RETURN @result
END

您显然可以偏移结果以更改平均值并将其包装在函数中以根据需要进行迭代以返回所需数量的样本点.如果您需要这方面的帮助，请告诉我.

You can obviously offset the results to change the mean and wrap this in a function to iterate as needed to return the desired number of sample points. Let me know if you need help for this.

这篇关于如何在 SQL Server 中生成正态分布的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！