تشخیص داده پرت (Outliers) SQL Server تحلیل دقیق داده

روش‌های مؤثر تشخیص داده‌های پرت (Outliers) در SQL Server برای تحلیل داده‌ای دقیق

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

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

سه نوع اصلی داده پرت وجود دارد:
* **نقطه پرت (Point Outlier):** یک نقطه داده‌ای واحد که به طور قابل توجهی از بقیه مجموعه داده دور است. به عنوان مثال، در داده‌های تراکنش بانکی، یک تراکنش با حجم بسیار زیاد می‌تواند یک نقطه پرت باشد.
* **پرت متنی (Contextual Outlier):** یک نقطه داده‌ای که در یک متن خاص غیرعادی است، اما در متن‌های دیگر ممکن است عادی باشد. به عنوان مثال، دمای ۳۰ درجه سانتی‌گراد در تابستان عادی است، اما در زمستان می‌تواند یک پرت متنی باشد.
* **پرت جمعی (Collective Outlier):** زیرمجموعه‌ای از نقاط داده که به صورت جمعی از بقیه داده‌ها منحرف می‌شوند، حتی اگر هر نقطه داده‌ای به صورت مجزا پرت نباشد. به عنوان مثال، یک الگوی غیرعادی در ترافیک شبکه که نشان‌دهنده حمله سایبری است.

چندین روش برای شناسایی داده‌های پرت وجود دارد که در ادامه به دو مورد از پرکاربردترین آن‌ها در SQL Server می‌پردازیم.

**۱. روش انحراف معیار (Standard Deviation Method)**

این روش بر این فرض استوار است که داده‌ها به صورت نرمال توزیع شده‌اند. در این روش، نقاط داده‌ای که بیش از یک آستانه (معمولاً ۲ یا ۳ انحراف معیار) از میانگین فاصله دارند، به عنوان داده پرت شناسایی می‌شوند.

فرمول‌های مورد استفاده در این روش عبارتند از:
`Mean (μ) = Σx / N`
`Standard Deviation (σ) = √[Σ(x – μ)² / N]`
`Lower Bound = Mean – (Z * Standard Deviation)`
`Upper Bound = Mean + (Z * Standard Deviation)`

در این فرمول‌ها، `x` هر نقطه داده، `N` تعداد کل نقاط داده، `μ` میانگین، `σ` انحراف معیار، و `Z` یک مقدار عددی (مثلاً ۲ یا ۳ برای تعیین شدت پرت بودن) است.

برای پیاده‌سازی این روش در SQL Server، ابتدا میانگین و انحراف معیار را محاسبه کرده و سپس نقاط داده‌ای که خارج از محدوده آستانه هستند را شناسایی می‌کنیم. برای مثال، فرض کنید یک جدول به نام `SalesData` با ستون `SaleAmount` داریم:

WITH Stats AS (
    SELECT
        AVG(SaleAmount) AS AverageSale,
        STDEV(SaleAmount) AS StdDevSale
    FROM SalesData
),
OutlierCheck AS (
    SELECT
        s.SaleAmount,
        s.SaleDate,
        st.AverageSale,
        st.StdDevSale,
        (s.SaleAmount - st.AverageSale) / st.StdDevSale AS ZScore
    FROM SalesData s
    CROSS JOIN Stats st
)
SELECT
    SaleAmount,
    SaleDate,
    'Outlier' AS Status
FROM OutlierCheck
WHERE ABS(ZScore) > 3; -- Identifying outliers beyond 3 standard deviations

در این کد، ابتدا میانگین (`AverageSale`) و انحراف معیار (`StdDevSale`) را برای ستون `SaleAmount` محاسبه می‌کنیم. سپس برای هر فروش، `ZScore` (فاصله از میانگین بر حسب انحراف معیار) را حساب کرده و رکوردهایی که `ZScore` مطلق آن‌ها بزرگتر از ۳ است (یعنی بیش از ۳ انحراف معیار از میانگین فاصله دارند) را به عنوان داده پرت برمی‌گردانیم.

**۲. روش دامنه بین چارکی (Interquartile Range – IQR) یا روش Tukey**

این روش برای شناسایی داده‌های پرت، به ویژه در مجموعه‌های داده‌ای که توزیع نرمال ندارند، بسیار مؤثر است. این روش بر اساس چارک‌ها (Quartiles) کار می‌کند و نسبت به روش انحراف معیار کمتر تحت تأثیر داده‌های پرت قرار می‌گیرد.

فرمول‌های مورد استفاده در این روش عبارتند از:
`Q1 = 25th Percentile`
`Q3 = 75th Percentile`
`IQR = Q3 – Q1`
`Lower Bound = Q1 – (1.5 * IQR)`
`Upper Bound = Q3 + (1.5 * IQR)`

در این فرمول‌ها، `Q1` چارک اول (۲۵ درصد داده‌ها پایین‌تر از آن قرار دارند)، `Q3` چارک سوم (۷۵ درصد داده‌ها پایین‌تر از آن قرار دارند)، و `IQR` دامنه بین چارکی است که نشان‌دهنده گستره ۵۰ درصد میانی داده‌ها است. هر نقطه داده‌ای که کمتر از کران پایین (`Lower Bound`) یا بیشتر از کران بالا (`Upper Bound`) باشد، به عنوان داده پرت شناسایی می‌شود.

برای پیاده‌سازی این روش در SQL Server، می‌توانیم از توابع پنجره‌ای مانند `PERCENTILE_CONT` برای محاسبه چارک‌ها استفاده کنیم.

WITH Quartiles AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY SaleAmount) OVER () AS Q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleAmount) OVER () AS Q3
    FROM SalesData
),
IQR_Calc AS (
    SELECT DISTINCT
        Q1,
        Q3,
        Q3 - Q1 AS IQR
    FROM Quartiles
),
OutlierBounds AS (
    SELECT
        Q1,
        Q3,
        IQR,
        Q1 - (1.5 * IQR) AS LowerBound,
        Q3 + (1.5 * IQR) AS UpperBound
    FROM IQR_Calc
)
SELECT
    sd.SaleAmount,
    sd.SaleDate,
    'Outlier' AS Status
FROM SalesData sd
CROSS JOIN OutlierBounds ob
WHERE sd.SaleAmount  ob.UpperBound;

در این کد، ابتدا `Q1` و `Q3` را برای `SaleAmount` محاسبه می‌کنیم. سپس `IQR` را به دست آورده و از آن برای تعیین کران‌های پایین و بالای داده‌های پرت استفاده می‌کنیم. در نهایت، رکوردهایی که `SaleAmount` آن‌ها خارج از این محدوده است را به عنوان داده‌های پرت نمایش می‌دهیم.

**۳. نمودار جعبه‌ای (Box Plot – روش بصری)**

نمودار جعبه‌ای یک روش بصری قدرتمند برای نمایش توزیع داده‌ها و شناسایی بصری داده‌های پرت است. این نمودار شامل یک جعبه است که چارک اول تا سوم (IQR) را نشان می‌دهد و خطی در وسط آن، میانه (چارک دوم) را مشخص می‌کند. دو “شاخک” یا “سبیل” از جعبه به سمت حداقل و حداکثر مقادیر غیرپرت گسترش می‌یابند. هر نقطه داده‌ای که فراتر از این شاخک‌ها باشد، به صورت جداگانه نمایش داده می‌شود و به عنوان داده پرت در نظر گرفته می‌شود. این نقاط معمولاً همان‌هایی هستند که با روش IQR شناسایی می‌شوند. اگرچه SQL Server به خودی خود ابزار رسم نمودار ندارد، اما نتایج تحلیل IQR را می‌توان به ابزارهای BI مانند Power BI یا Tableau منتقل کرد تا نمودار جعبه‌ای رسم شود و داده‌های پرت به صورت بصری مشخص گردند.