حل پیشرفته مشکل Gaps و Islands در TSQL با توابع پنجره‌ای SQL Server

حل پیشرفته مشکل Gaps و Islands در T-SQL با توابع پنجره‌ای: راهنمای عملی SQL Server

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

فرض کنید در حال ردیابی زمان ورود و خروج کارمندان هستید و می‌خواهید مدت زمان هر جلسه کاری پیوسته را شناسایی کنید. اگر یک کارمند چندین بار ورود/خروج داشته باشد و بین آنها وقفه‌ای طولانی وجود داشته باشد، آنگاه هر دوره ورود/خروج پیوسته یک “جزیره” و زمان بین آنها یک “شکاف” محسوب می‌شود.

برای شروع، یک جدول نمونه ایجاد کرده و داده‌ها را وارد می‌کنیم:

CREATE TABLE EmployeeLogins (
    EmployeeID INT,
    EventTime DATETIME
);

INSERT INTO EmployeeLogins (EmployeeID, EventTime) VALUES
(1, '2023-01-01 08:00:00'),
(1, '2023-01-01 08:05:00'),
(1, '2023-01-01 08:10:00'),
(1, '2023-01-01 09:00:00'),
(1, '2023-01-01 09:05:00'),
(2, '2023-01-01 08:00:00'),
(2, '2023-01-01 08:05:00'),
(2, '2023-01-01 08:10:00');

در این مثال، برای `EmployeeID = 1`، سه ورود اول یک “جزیره” را تشکیل می‌دهند (8:00، 8:05، 8:10). سپس یک “شکاف” از 8:10 تا 9:00 وجود دارد و دو ورود بعدی (9:00، 9:05) یک “جزیره” دیگر را تشکیل می‌دهند. هدف ما شناسایی شروع و پایان هر یک از این جزایر است.

**استفاده از توابع پنجره‌ای (Window Functions) برای حل مشکل:**

راه حل اصلی در این روش، اختصاص یک شناسه گروهی منحصر به فرد به هر جزیره است. ما از ترکیب توابع `LAG()` و `SUM()` به همراه `ROW_NUMBER()` در عبارات جدول مشترک (CTE) برای دستیابی به این هدف استفاده می‌کنیم.

1. **شناسایی رویداد قبلی (`LoginDetails`):**
ابتدا، از تابع پنجره‌ای `LAG()` برای دریافت زمان رویداد قبلی برای هر کارمند استفاده می‌کنیم. این کار به ما کمک می‌کند تا تفاوت زمانی بین رویدادهای متوالی را محاسبه کنیم.

WITH LoginDetails AS (
    SELECT
        EmployeeID,
        EventTime,
        LAG(EventTime, 1, '1900-01-01') OVER (PARTITION BY EmployeeID ORDER BY EventTime) AS PreviousEventTime
    FROM
        EmployeeLogins
)
SELECT * FROM LoginDetails;
    

در کوئری بالا، `LAG(EventTime, 1, ‘1900-01-01’)` زمان `EventTime` ردیف قبلی را برمی‌گرداند. `PARTITION BY EmployeeID` تضمین می‌کند که این مقایسه فقط در محدوده همان کارمند انجام شود، و `ORDER BY EventTime` ترتیب زمانی را برای مقایسه صحیح مشخص می‌کند. ‘1900-01-01’ به عنوان مقدار پیش‌فرض برای اولین رویداد هر کارمند استفاده می‌شود.

2. **تعیین شروع یک جزیره جدید (`IslandStarts`):**
در گام بعدی، بر اساس تفاوت زمانی بین رویداد فعلی و رویداد قبلی، مشخص می‌کنیم که آیا یک جزیره جدید شروع شده است یا خیر. اگر تفاوت زمانی بیشتر از یک حد مشخص (مثلاً 10 دقیقه) باشد، یا اگر اولین رویداد برای یک کارمند باشد، آن را به عنوان شروع یک جزیره جدید علامت‌گذاری می‌کنیم.

WITH LoginDetails AS (
    SELECT
        EmployeeID,
        EventTime,
        LAG(EventTime, 1, '1900-01-01') OVER (PARTITION BY EmployeeID ORDER BY EventTime) AS PreviousEventTime
    FROM
        EmployeeLogins
),
IslandStarts AS (
    SELECT
        EmployeeID,
        EventTime,
        CASE
            WHEN DATEDIFF(minute, PreviousEventTime, EventTime) > 10 OR PreviousEventTime = '1900-01-01' THEN 1
            ELSE 0
        END AS IsNewIslandStart
    FROM
        LoginDetails
)
SELECT * FROM IslandStarts;
    

تابع `DATEDIFF(minute, PreviousEventTime, EventTime)` تفاوت بر حسب دقیقه بین دو زمان را محاسبه می‌کند. اگر این تفاوت بیشتر از 10 دقیقه باشد، یا اگر `PreviousEventTime` مقدار پیش‌فرض ‘1900-01-01’ باشد (که نشان‌دهنده اولین رویداد است)، ستون `IsNewIslandStart` مقدار 1 می‌گیرد؛ در غیر این صورت 0.

3. **گروه‌بندی جزایر (`IslandGroups`):**
اکنون که محل شروع هر جزیره جدید را مشخص کرده‌ایم، می‌توانیم از یک تابع پنجره‌ای `SUM()` تجمعی برای اختصاص یک شناسه گروهی منحصر به فرد به هر جزیره استفاده کنیم. این `SUM()` در محدوده هر `EmployeeID` و بر اساس ترتیب `EventTime` محاسبه می‌شود.

WITH LoginDetails AS (
    SELECT
        EmployeeID,
        EventTime,
        LAG(EventTime, 1, '1900-01-01') OVER (PARTITION BY EmployeeID ORDER BY EventTime) AS PreviousEventTime
    FROM
        EmployeeLogins
),
IslandStarts AS (
    SELECT
        EmployeeID,
        EventTime,
        CASE
            WHEN DATEDIFF(minute, PreviousEventTime, EventTime) > 10 OR PreviousEventTime = '1900-01-01' THEN 1
            ELSE 0
        END AS IsNewIslandStart
    FROM
        LoginDetails
),
IslandGroups AS (
    SELECT
        EmployeeID,
        EventTime,
        SUM(IsNewIslandStart) OVER (PARTITION BY EmployeeID ORDER BY EventTime) AS IslandGroup
    FROM
        IslandStarts
)
SELECT * FROM IslandGroups;
    

تابع `SUM(IsNewIslandStart) OVER (PARTITION BY EmployeeID ORDER BY EventTime)` به صورت تجمعی مقادیر 1 را برای هر کارمند جمع می‌کند. هر بار که `IsNewIslandStart` برابر 1 شود، مقدار `IslandGroup` افزایش می‌یابد و یک گروه جدید تشکیل می‌دهد. این باعث می‌شود که تمام رویدادهای داخل یک جزیره، `IslandGroup` یکسانی داشته باشند.

4. **استخراج جزایر نهایی:**
در نهایت، می‌توانیم با گروه‌بندی بر اساس `EmployeeID` و `IslandGroup`، حداقل و حداکثر `EventTime` را برای یافتن شروع و پایان هر جزیره کاری استخراج کنیم.

WITH LoginDetails AS (
    SELECT
        EmployeeID,
        EventTime,
        LAG(EventTime, 1, '1900-01-01') OVER (PARTITION BY EmployeeID ORDER BY EventTime) AS PreviousEventTime
    FROM
        EmployeeLogins
),
IslandStarts AS (
    SELECT
        EmployeeID,
        EventTime,
        CASE
            WHEN DATEDIFF(minute, PreviousEventTime, EventTime) > 10 OR PreviousEventTime = '1900-01-01' THEN 1
            ELSE 0
        END AS IsNewIslandStart
    FROM
        LoginDetails
),
IslandGroups AS (
    SELECT
        EmployeeID,
        EventTime,
        SUM(IsNewIslandStart) OVER (PARTITION BY EmployeeID ORDER BY EventTime) AS IslandGroup
    FROM
        IslandStarts
)
SELECT
    EmployeeID,
    MIN(EventTime) AS IslandStart,
    MAX(EventTime) AS IslandEnd,
    COUNT(*) AS NumberOfEvents
FROM
    IslandGroups
GROUP BY
    EmployeeID, IslandGroup
ORDER BY
    EmployeeID, IslandStart;
    

این کوئری نهایی، هر جزیره (دوره کاری پیوسته) را با زمان شروع (`IslandStart`)، زمان پایان (`IslandEnd`) و تعداد رویدادهای موجود در آن جزیره (`NumberOfEvents`) نمایش می‌دهد. این یک راه حل قدرتمند و بهینه برای مدیریت و تحلیل مسائل Gaps و Islands در SQL Server با استفاده از قابلیت‌های پیشرفته توابع پنجره‌ای T-SQL است.