تولید اعداد تصادفی یکنواخت نرمال و لگ نرمال در SQL Server راهنمای جامع

تولید اعداد تصادفی یکنواخت، نرمال و لگ‌نرمال در SQL Server: راهنمای جامع 

تولید اعداد تصادفی یک نیاز رایج در شبیه‌سازی‌ها، تحلیل‌های آماری و تولید داده‌های آزمایشی است. SQL Server دارای تابع داخلی RAND() است، اما این تابع محدودیت‌هایی دارد؛ برای هر بچ از دستورات، تنها یک بار مقداردهی اولیه می‌شود و اعداد تولید شده ممکن است برای اهداف پیشرفته آماری کافی نباشند. این مقاله به شما نشان می‌دهد چگونه می‌توانید اعداد تصادفی با توزیع‌های یکنواخت، نرمال (گوسی) و لگ‌نرمال را در SQL Server تولید کنید.

برای غلبه بر مشکل تکرار RAND() در یک بچ، می‌توانیم از CHECKSUM(NEWID()) به عنوان seed برای RAND() استفاده کنیم. این روش تضمین می‌کند که هر بار که RAND() فراخوانی می‌شود، یک مقدار تصادفی جدید بر اساس یک seed منحصر به فرد تولید شود. برای سهولت کار، یک تابع Scalar-Valued Function با نام udf_Random ایجاد می‌کنیم که این مکانیزم را کپسوله می‌کند.

CREATE FUNCTION dbo.udf_Random()
RETURNS FLOAT
AS
BEGIN
    RETURN RAND(CHECKSUM(NEWID()));
END;

تولید اعداد تصادفی یکنواخت

یک توزیع یکنواخت شامل اعدادی است که به طور مساوی در یک بازه مشخص بین حداقل (a) و حداکثر (b) توزیع شده‌اند. برای تولید این نوع اعداد، می‌توانیم از فرمول زیر استفاده کنیم:

(b – a) * RAND() + a

این فرمول یک عدد تصادفی بین a و b را تولید می‌کند. تابع GENERATE_UNIFORM_RANDOM با استفاده از این منطق ساخته می‌شود:

CREATE FUNCTION dbo.GENERATE_UNIFORM_RANDOM (@Min float, @Max float)
RETURNS float
AS
BEGIN
    RETURN (@Max - @Min) * dbo.udf_Random() + @Min;
END;

تولید اعداد تصادفی نرمال (گوسی)

توزیع نرمال، که به توزیع گوسی نیز معروف است، یکی از مهم‌ترین توزیع‌های احتمالی در آمار است. این توزیع به شکل زنگوله است و توسط میانگین (mu) و انحراف معیار (sigma) تعریف می‌شود. برای تولید اعداد تصادفی با توزیع نرمال از توزیع یکنواخت، می‌توانیم از تبدیل Box-Muller استفاده کنیم.

روش Box-Muller دو عدد تصادفی مستقل نرمال را از دو عدد تصادفی یکنواخت مستقل بین 0 و 1 تولید می‌کند. فرض کنید u1 و u2 دو عدد تصادفی یکنواخت در بازه (0,1] باشند. دو متغیر نرمال z1 و z2 به صورت زیر محاسبه می‌شوند:

z1 = sqrt(-2 * log(u1)) * cos(2 * pi * u2)

z2 = sqrt(-2 * log(u1)) * sin(2 * pi * u2)

پس از به دست آوردن z، می‌توانیم آن را به میانگین (mu) و انحراف معیار (sigma) دلخواه با استفاده از فرمول زیر مقیاس‌بندی کنیم:

Normal_Random = sigma * z + mu

تابع GENERATE_NORMAL_RANDOM این منطق را پیاده‌سازی می‌کند:

CREATE FUNCTION dbo.GENERATE_NORMAL_RANDOM (@Mu float, @Sigma float)
RETURNS float
AS
BEGIN
    DECLARE @u1 float, @u2 float, @z float;
    SELECT @u1 = dbo.udf_Random(), @u2 = dbo.udf_Random();

    -- Ensure u1 is not zero to avoid log(0)
    IF @u1 = 0 SET @u1 = 0.000000000000001; 

    SET @z = SQRT(-2 * LOG(@u1)) * COS(2 * PI() * @u2);
    RETURN @Sigma * @z + @Mu;
END;

تولید اعداد تصادفی لگ‌نرمال

توزیع لگ‌نرمال برای مدل‌سازی پدیده‌هایی استفاده می‌شود که مقادیر آن‌ها مثبت و دارای چولگی به سمت راست هستند، مانند قیمت سهام، درآمد یا اندازه ذرات. اگر یک متغیر تصادفی دارای توزیع لگ‌نرمال باشد، لگاریتم طبیعی آن (ln) دارای توزیع نرمال خواهد بود.

برای تولید یک عدد لگ‌نرمال از یک توزیع نرمال، از فرمول زیر استفاده می‌کنیم:

Lognormal_Random = EXP(Normal_Random_Number)

که در آن Normal_Random_Number یک عدد تصادفی نرمال با میانگین mu_n و انحراف معیار sigma_n است که به صورت زیر از میانگین (mu_ln) و انحراف معیار (sigma_ln) توزیع لگ‌نرمال مورد نظر به دست می‌آیند:

mu_n = ln(mu_ln^2 / sqrt(sigma_ln^2 + mu_ln^2))

sigma_n = sqrt(ln(1 + sigma_ln^2 / mu_ln^2))

تابع GENERATE_LOGNORMAL_RANDOM با استفاده از این فرمول‌ها پیاده‌سازی شده است:

CREATE FUNCTION dbo.GENERATE_LOGNORMAL_RANDOM (@Mu_LN float, @Sigma_LN float)
RETURNS float
AS
BEGIN
    DECLARE @Mu_N float, @Sigma_N float;
    
    SET @Mu_N = LOG(@Mu_LN * @Mu_LN / SQRT(@Sigma_LN * @Sigma_LN + @Mu_LN * @Mu_LN));
    SET @Sigma_N = SQRT(LOG(1 + (@Sigma_LN * @Sigma_LN) / (@Mu_LN * @Mu_LN)));

    RETURN EXP(dbo.GENERATE_NORMAL_RANDOM(@Mu_N, @Sigma_N));
END;

مثال‌های کاربردی

در ادامه چند مثال برای نحوه استفاده از توابع ایجاد شده ارائه می‌شود. این مثال‌ها نشان می‌دهند که چگونه می‌توانید این توابع را برای تولید مجموعه‌های داده تصادفی برای سناریوهای مختلف به کار ببرید.

برای تولید 10 عدد تصادفی با توزیع یکنواخت بین 10 و 100:

SELECT TOP 10 dbo.GENERATE_UNIFORM_RANDOM(10, 100) AS UniformRandomNumber
FROM sys.all_columns;

برای تولید 10 عدد تصادفی با توزیع نرمال با میانگین 50 و انحراف معیار 10:

SELECT TOP 10 dbo.GENERATE_NORMAL_RANDOM(50, 10) AS NormalRandomNumber
FROM sys.all_columns;

برای تولید 10 عدد تصادفی با توزیع لگ‌نرمال با میانگین 100 و انحراف معیار 20 (برای توزیع لگ‌نرمال):

SELECT TOP 10 dbo.GENERATE_LOGNORMAL_RANDOM(100, 20) AS LognormalRandomNumber
FROM sys.all_columns;