کشف بزرگترین شکاف های زمانی : LEAD و DATEDIFF

کشف بزرگترین شکاف‌های زمانی در بازه‌های تاریخ با SQL Server و T-SQL

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

برای مثال، فرض کنید داده‌هایی دارید که شامل تاریخ شروع و پایان یک رویداد خاص است. هدف یافتن بزرگترین فواصل زمانی (شکاف‌ها) بین پایان یک رویداد و شروع رویداد بعدی است.

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

CREATE TABLE DateRanges (
    Id INT,
    StartDate DATETIME,
    EndDate DATETIME
);

INSERT INTO DateRanges (Id, StartDate, EndDate) VALUES
(1, '2023-01-01 10:00:00', '2023-01-05 12:00:00'),
(1, '2023-01-06 14:00:00', '2023-01-10 16:00:00'),
(1, '2023-01-15 09:00:00', '2023-01-20 11:00:00'),
(1, '2023-01-21 13:00:00', '2023-01-25 15:00:00'),
(2, '2023-02-01 08:00:00', '2023-02-03 10:00:00'),
(2, '2023-02-05 11:00:00', '2023-02-07 13:00:00'),
(2, '2023-02-12 09:00:00', '2023-02-15 12:00:00'),
(3, '2023-03-01 07:00:00', '2023-03-02 09:00:00'),
(3, '2023-03-03 10:00:00', '2023-03-04 12:00:00'),
(3, '2023-03-08 14:00:00', '2023-03-09 16:00:00');

این جدول شامل سه ستون است: `Id` برای شناسایی گروه‌های مجزا از رویدادها، `StartDate` و `EndDate` برای نشان دادن بازه زمانی هر رویداد.

یافتن شکاف‌های زمانی با LEAD و DATEDIFF

برای شناسایی شکاف‌های زمانی، می‌توانیم از توابع ویندوز `LEAD` استفاده کنیم. تابع `LEAD` به ما اجازه می‌دهد به مقداری از ردیف بعدی در یک پارتیشن مشخص دسترسی پیدا کنیم. در این مورد، از آن برای یافتن `StartDate` رویداد بعدی در هر گروه `Id` استفاده می‌کنیم. سپس با استفاده از تابع `DATEDIFF`، تفاوت بین `EndDate` رویداد فعلی و `StartDate` رویداد بعدی را محاسبه می‌کنیم تا طول شکاف را بدست آوریم.

کوئری زیر تمام شکاف‌های زمانی (بر حسب روز) را برای هر گروه `Id` محاسبه می‌کند:

SELECT
    Id,
    StartDate,
    EndDate,
    LEAD(StartDate) OVER(PARTITION BY Id ORDER BY StartDate) AS NextStartDate,
    DATEDIFF(day, EndDate, LEAD(StartDate) OVER(PARTITION BY Id ORDER BY StartDate)) AS GapSize
FROM
    DateRanges
ORDER BY
    Id, StartDate;

در این کوئری:
* `LEAD(StartDate) OVER(PARTITION BY Id ORDER BY StartDate)`: تاریخ شروع رویداد بعدی را برای هر `Id`، بر اساس ترتیب `StartDate`، بازیابی می‌کند.
* `DATEDIFF(day, EndDate, NextStartDate)`: تعداد روزهای کامل بین `EndDate` فعلی و `NextStartDate` بعدی را محاسبه می‌کند. اگر `NextStartDate` وجود نداشته باشد (یعنی آخرین رکورد در گروه)، `GapSize` به صورت `NULL` خواهد بود.

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

یافتن N بزرگترین شکاف زمانی

برای یافتن N بزرگترین شکاف زمانی، می‌توانیم کوئری قبلی را گسترش دهیم. ابتدا شکاف‌ها را محاسبه کرده، سپس نتایج را بر اساس `GapSize` به صورت نزولی مرتب کرده و در نهایت از `TOP N` برای انتخاب بزرگترین شکاف‌ها استفاده می‌کنیم. باید ردیف‌هایی که `NextStartDate` یا `GapSize` آن‌ها `NULL` است و همچنین شکاف‌های منفی (همپوشانی) یا صفر را فیلتر کنیم.

کوئری زیر 3 بزرگترین شکاف زمانی را در داده‌ها پیدا می‌کند:

SELECT TOP (3)
    Id,
    StartDate,
    EndDate,
    NextStartDate,
    GapSize
FROM
    (
        SELECT
            Id,
            StartDate,
            EndDate,
            LEAD(StartDate) OVER(PARTITION BY Id ORDER BY StartDate) AS NextStartDate,
            DATEDIFF(day, EndDate, LEAD(StartDate) OVER(PARTITION BY Id ORDER BY StartDate)) AS GapSize
        FROM
            DateRanges
    ) AS SubQuery
WHERE
    NextStartDate IS NOT NULL AND GapSize > 0
ORDER BY
    GapSize DESC;

در این کوئری:
* از یک کوئری درونی (`SubQuery`) برای محاسبه `NextStartDate` و `GapSize` استفاده می‌شود.
* عبارت `WHERE NextStartDate IS NOT NULL AND GapSize > 0` اطمینان حاصل می‌کند که فقط شکاف‌های مثبت و واقعی در نظر گرفته شوند (یعنی همپوشانی‌ها و پایان‌های سری داده فیلتر شوند).
* `ORDER BY GapSize DESC` نتایج را بر اساس اندازه شکاف از بزرگترین به کوچکترین مرتب می‌کند.
* `SELECT TOP (3)` سه شکاف بزرگتر را از مجموعه نتایج نهایی انتخاب می‌کند. برای یافتن تعداد متفاوت شکاف‌ها، کافیست عدد داخل پرانتز را تغییر دهید.