تحلیل رگرسیون خطی در SQL Server

تحلیل رگرسیون خطی در SQL Server: راهنمای جامع و کاربردی

در این مقاله، چگونگی انجام تحلیل رگرسیون خطی ساده را مستقیماً در SQL Server بررسی می‌کنیم. رگرسیون یک ابزار آماری قدرتمند است که به ما امکان می‌دهد رابطه بین یک متغیر وابسته و یک یا چند متغیر مستقل را مدل‌سازی کنیم. این تحلیل به ویژه برای پیش‌بینی و درک تأثیر متغیرهای مختلف بر یکدیگر مفید است.

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

در مدل رگرسیون خطی ساده، ما یک متغیر وابسته (Y) و یک متغیر مستقل (X) داریم. فرمول کلی یک مدل رگرسیون خطی ساده به شرح زیر است:

`Y = a + bX + e`

در این فرمول:
* Y نمایانگر متغیر وابسته (آنچه می‌خواهیم پیش‌بینی کنیم).
* X نمایانگر متغیر مستقل (آنچه برای پیش‌بینی استفاده می‌شود).
* a نمایانگر نقطه عرض از مبدأ یا Y-intercept است (مقدار Y زمانی که X برابر با صفر است).
* b نمایانگر شیب خط رگرسیون (slope) است (میزان تغییر در Y به ازای یک واحد تغییر در X).
* e نمایانگر خطای تصادفی (random error) است که تفاوت بین مقدار مشاهده شده و مقدار پیش‌بینی شده را نشان می‌دهد.

هدف ما محاسبه بهترین مقادیر برای ‘a’ و ‘b’ است که خطای ‘e’ را به حداقل برساند. این کار معمولاً با استفاده از روش حداقل مربعات (Least Squares) انجام می‌شود.

برای محاسبه ضرایب ‘b’ (شیب) و ‘a’ (نقطه عرض از مبدأ) در رگرسیون خطی ساده، از فرمول‌های زیر استفاده می‌کنیم:

فرمول شیب (b):

`b = (nΣXY – ΣXΣY) / (nΣX^2 – (ΣX)^2)`

یا معادل آن به شکل دیگر:

`b = Σ[(X – X_mean)(Y – Y_mean)] / Σ[(X – X_mean)^2]`

فرمول نقطه عرض از مبدأ (a):

`a = Y_mean – b * X_mean`

که در آن:
* n تعداد نقاط داده است.
* ΣXY مجموع حاصل ضرب X و Y است.
* ΣX و ΣY به ترتیب مجموع مقادیر X و Y هستند.
* ΣX^2 مجموع مربعات X است.
* X_mean و Y_mean میانگین مقادیر X و Y هستند.

برای پیاده‌سازی این محاسبات در SQL Server، ابتدا یک جدول نمونه با داده‌های مربوط به متغیرهای X و Y ایجاد می‌کنیم:

CREATE TABLE dbo.RegressionData (
    X_Value DECIMAL(18, 4),
    Y_Value DECIMAL(18, 4)
);
GO

INSERT INTO dbo.RegressionData (X_Value, Y_Value) VALUES
(1, 2),
(2, 4),
(3, 5),
(4, 4),
(5, 5),
(6, 7),
(7, 8),
(8, 9),
(9, 10),
(10, 11);
GO

سپس، شروع به محاسبه اجزای لازم برای ضرایب ‘a’ و ‘b’ می‌کنیم. این شامل میانگین‌ها، مجموع‌ها و مجموع مربعات می‌شود.

مرحله ۱: محاسبه میانگین X و Y

SELECT
    AVG(X_Value) AS X_Mean,
    AVG(Y_Value) AS Y_Mean
FROM dbo.RegressionData;

مرحله ۲: محاسبه مجموع X، مجموع Y، مجموع X^2، و مجموع XY

SELECT
    SUM(X_Value) AS Sum_X,
    SUM(Y_Value) AS Sum_Y,
    SUM(POWER(X_Value, 2)) AS Sum_X_Squared,
    SUM(X_Value * Y_Value) AS Sum_XY,
    COUNT(*) AS N
FROM dbo.RegressionData;

با استفاده از این مقادیر، می‌توانیم ‘b’ و ‘a’ را محاسبه کنیم.

محاسبه ‘b’:

WITH RegressionStats AS (
    SELECT
        CAST(COUNT(*) AS DECIMAL(18,4)) AS N,
        SUM(X_Value) AS Sum_X,
        SUM(Y_Value) AS Sum_Y,
        SUM(POWER(X_Value, 2)) AS Sum_X_Squared,
        SUM(X_Value * Y_Value) AS Sum_XY
    FROM dbo.RegressionData
)
SELECT
    (N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2)) AS B_Coefficient
FROM RegressionStats;

محاسبه ‘a’:

WITH RegressionStats AS (
    SELECT
        CAST(COUNT(*) AS DECIMAL(18,4)) AS N,
        AVG(X_Value) AS X_Mean,
        AVG(Y_Value) AS Y_Mean,
        SUM(X_Value) AS Sum_X,
        SUM(Y_Value) AS Sum_Y,
        SUM(POWER(X_Value, 2)) AS Sum_X_Squared,
        SUM(X_Value * Y_Value) AS Sum_XY
    FROM dbo.RegressionData
),
Coefficients AS (
    SELECT
        (N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2)) AS B_Coefficient,
        X_Mean,
        Y_Mean
    FROM RegressionStats
)
SELECT
    Y_Mean - B_Coefficient * X_Mean AS A_Coefficient
FROM Coefficients;

پس از محاسبه ضرایب ‘a’ و ‘b’، می‌توانیم از معادله رگرسیون برای پیش‌بینی مقادیر Y بر اساس مقادیر جدید X استفاده کنیم. این گام اساسی در مدل‌سازی پیش‌بینی کننده است. فرمول پیش‌بینی به این صورت است:

`Y_predicted = a + bX`

برای پیش‌بینی مقادیر Y_Value برای هر X_Value در داده‌هایمان، می‌توانیم از کوئری زیر استفاده کنیم. این کوئری ضرایب را محاسبه کرده و سپس برای هر نقطه داده، Y پیش‌بینی شده را بازگرداند:

WITH RegressionCoefficients AS (
    SELECT
        CAST(COUNT(*) AS DECIMAL(18,4)) AS N,
        AVG(X_Value) AS X_Mean,
        AVG(Y_Value) AS Y_Mean,
        SUM(X_Value) AS Sum_X,
        SUM(Y_Value) AS Sum_Y,
        SUM(POWER(X_Value, 2)) AS Sum_X_Squared,
        SUM(X_Value * Y_Value) AS Sum_XY
    FROM dbo.RegressionData
),
CalculatedCoefficients AS (
    SELECT
        (N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2)) AS B_Coefficient,
        (Y_Mean - ((N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2))) * X_Mean) AS A_Coefficient
    FROM RegressionCoefficients
)
SELECT
    rd.X_Value,
    rd.Y_Value AS Actual_Y,
    (cc.A_Coefficient + cc.B_Coefficient * rd.X_Value) AS Predicted_Y
FROM dbo.RegressionData rd
CROSS JOIN CalculatedCoefficients cc;

ضریب تعیین (R-squared یا R²) معیاری است برای ارزیابی میزان برازش مدل رگرسیون. این ضریب نشان می‌دهد که چه درصدی از واریانس متغیر وابسته (Y) توسط متغیر مستقل (X) توضیح داده می‌شود. مقدار R-squared بین ۰ و ۱ است؛ هرچه به ۱ نزدیک‌تر باشد، مدل بهتر داده‌ها را توضیح می‌دهد و قدرت پیش‌بینی بالاتری دارد.

فرمول R-squared به شرح زیر است:

`R^2 = 1 – (RSS / TSS)`

که در آن:
* RSS (Residual Sum of Squares) مجموع مربعات باقیمانده‌ها (اختلاف بین مقادیر واقعی Y و مقادیر پیش‌بینی شده Y) است. این مقدار نشان‌دهنده واریانس غیرقابل توضیح توسط مدل است.
* TSS (Total Sum of Squares) مجموع مربعات کل (اختلاف بین مقادیر واقعی Y و میانگین Y واقعی) است. این مقدار نشان‌دهنده واریانس کل در متغیر وابسته است.

برای محاسبه RSS و TSS، ابتدا باید مقادیر پیش‌بینی شده را داشته باشیم.

WITH RegressionCoefficients AS (
    SELECT
        CAST(COUNT(*) AS DECIMAL(18,4)) AS N,
        AVG(X_Value) AS X_Mean,
        AVG(Y_Value) AS Y_Mean,
        SUM(X_Value) AS Sum_X,
        SUM(Y_Value) AS Sum_Y,
        SUM(POWER(X_Value, 2)) AS Sum_X_Squared,
        SUM(X_Value * Y_Value) AS Sum_XY
    FROM dbo.RegressionData
),
CalculatedCoefficients AS (
    SELECT
        (N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2)) AS B_Coefficient,
        (Y_Mean - ((N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2))) * X_Mean) AS A_Coefficient,
        Y_Mean AS Global_Y_Mean
    FROM RegressionCoefficients
),
PredictedValues AS (
    SELECT
        rd.Y_Value AS Actual_Y,
        (cc.A_Coefficient + cc.B_Coefficient * rd.X_Value) AS Predicted_Y,
        cc.Global_Y_Mean
    FROM dbo.RegressionData rd
    CROSS JOIN CalculatedCoefficients cc
)
SELECT
    SUM(POWER(Actual_Y - Predicted_Y, 2)) AS RSS,
    SUM(POWER(Actual_Y - Global_Y_Mean, 2)) AS TSS
FROM PredictedValues;

اکنون، برای محاسبه R-squared:

WITH RegressionCoefficients AS (
    SELECT
        CAST(COUNT(*) AS DECIMAL(18,4)) AS N,
        AVG(X_Value) AS X_Mean,
        AVG(Y_Value) AS Y_Mean,
        SUM(X_Value) AS Sum_X,
        SUM(Y_Value) AS Sum_Y,
        SUM(POWER(X_Value, 2)) AS Sum_X_Squared,
        SUM(X_Value * Y_Value) AS Sum_XY
    FROM dbo.RegressionData
),
CalculatedCoefficients AS (
    SELECT
        (N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2)) AS B_Coefficient,
        (Y_Mean - ((N * Sum_XY - Sum_X * Sum_Y) / (N * Sum_X_Squared - POWER(Sum_X, 2))) * X_Mean) AS A_Coefficient,
        Y_Mean AS Global_Y_Mean
    FROM RegressionCoefficients
),
PredictedValues AS (
    SELECT
        rd.Y_Value AS Actual_Y,
        (cc.A_Coefficient + cc.B_Coefficient * rd.X_Value) AS Predicted_Y,
        cc.Global_Y_Mean
    FROM dbo.RegressionData rd
    CROSS JOIN CalculatedCoefficients cc
),
SumsOfSquares AS (
    SELECT
        SUM(POWER(Actual_Y - Predicted_Y, 2)) AS RSS,
        SUM(POWER(Actual_Y - Global_Y_Mean, 2)) AS TSS
    FROM PredictedValues
)
SELECT
    1 - (RSS / TSS) AS R_Squared
FROM SumsOfSquares;

برای ساده‌سازی فرآیند و افزایش کارایی، می‌توانیم تمام محاسبات رگرسیون خطی شامل ضرایب ‘a’ و ‘b’، مقادیر پیش‌بینی شده، و ضریب تعیین (R-squared) را در یک کوئری SQL واحد ادغام کنیم. این رویکرد به ما امکان می‌دهد نتایج کامل تحلیل رگرسیون را با یک اجرای واحد به دست آوریم.

WITH SummaryStats AS (
    SELECT
        COUNT(*) AS N,
        AVG(X_Value) AS X_Mean,
        AVG(Y_Value) AS Y_Mean,
        SUM(X_Value) AS Sum_X,
        SUM(Y_Value) AS Sum_Y,
        SUM(POWER(X_Value, 2)) AS Sum_X_Squared,
        SUM(X_Value * Y_Value) AS Sum_XY
    FROM dbo.RegressionData
),
Coefficients AS (
    SELECT
        X_Mean,
        Y_Mean,
        -- Calculate B_Coefficient (Slope)
        CAST((N * Sum_XY - Sum_X * Sum_Y) AS DECIMAL(18,4)) / CAST((N * Sum_X_Squared - POWER(Sum_X, 2)) AS DECIMAL(18,4)) AS B_Coefficient
    FROM SummaryStats
),
FinalCoefficients AS (
    SELECT
        B_Coefficient,
        -- Calculate A_Coefficient (Y-intercept)
        CAST(Y_Mean - B_Coefficient * X_Mean AS DECIMAL(18,4)) AS A_Coefficient
    FROM Coefficients
),
PredictedValues AS (
    SELECT
        rd.X_Value,
        rd.Y_Value AS Actual_Y,
        (fc.A_Coefficient + fc.B_Coefficient * rd.X_Value) AS Predicted_Y,
        fc.A_Coefficient,
        fc.B_Coefficient,
        (SELECT Y_Mean FROM SummaryStats) AS Global_Y_Mean -- Get overall Y mean for TSS
    FROM dbo.RegressionData rd
    CROSS JOIN FinalCoefficients fc
),
RegressionMetrics AS (
    SELECT
        (SELECT A_Coefficient FROM FinalCoefficients) AS A_Coefficient,
        (SELECT B_Coefficient FROM FinalCoefficients) AS B_Coefficient,
        SUM(POWER(Actual_Y - Predicted_Y, 2)) AS RSS, -- Residual Sum of Squares
        SUM(POWER(Actual_Y - Global_Y_Mean, 2)) AS TSS -- Total Sum of Squares
    FROM PredictedValues
)
SELECT
    A_Coefficient,
    B_Coefficient,
    RSS,
    TSS,
    CASE
        WHEN TSS = 0 THEN NULL -- Avoid division by zero if all Y values are identical
        ELSE 1 - (RSS / TSS)
    END AS R_Squared
FROM RegressionMetrics;