در بسیاری از گزارش‌های تحلیلی و مالی، نیاز داریم مقدار یک ردیف را با مقدار ردیف قبلی همان مجموعه مقایسه کنیم؛ فروش این ماه در مقابل ماه گذشته، قیمت سهام امروز در مقابل روز معاملاتی قبلی، یا وضعیت یک سفارش در مقابل آخرین وضعیت ثبت‌شده پیش از آن. پیش از در دسترس قرار گرفتن توابعی مانند LAG در SQL Server، رسیدن به چنین مقایسه‌ای معمولاً نیازمند Self Join پیچیده یا Subquery همبسته بود که هم نوشتن آن‌ها دشوار بود و هم روی جداول بزرگ Performance ضعیف‌تری داشتند.

تابع LAG دقیقاً برای حل همین دسته از مسائل طراحی شده است. این تابع به موتور SQL Server اجازه می‌دهد بدون نیاز به Join اضافی، مستقیماً به مقدار یک یا چند ردیف پیش از ردیف جاری، در همان مجموعه نتیجه، دسترسی پیدا کند. در پروژه‌های سازمانی که تحلیل روند، مقایسه دوره‌ای و تشخیص تغییر وضعیت اهمیت دارند، LAG یکی از پرکاربردترین توابع Window محسوب می‌شود.

در این مقاله بررسی می‌کنیم LAG دقیقاً چگونه کار می‌کند، پارامترهای مختلف آن چه نقشی دارند، چگونه با PARTITION BY در سطح هر گروه عمل می‌کند، رفتار آن در برابر مقادیر NULL چگونه است، چه تفاوتی با LEAD و ROW_NUMBER دارد، در چه سناریوهای رایج سازمانی کاربرد دارد و چه نکاتی از منظر Performance باید در مدل‌های حجیم رعایت شود.

تعریف LAG و ساختار پایه آن

تابع LAG یکی از Window Functionهای SQL Server است که امکان دسترسی به مقدار یک ردیف پیشین را بر اساس ترتیب تعیین‌شده در عبارت OVER فراهم می‌کند. این تابع زمانی کاربرد دارد که بخواهیم مقدار فعلی را با مقدار قبلی در همان مجموعه یا همان گروه مقایسه کنیم، بدون اینکه برای پیدا کردن ردیف قبلی به Self Join یا Subquery جداگانه نیاز داشته باشیم.

ساختار کلی تابع به شکل زیر است:

LAG ( scalar_expression [, offset ] [, default ] )
    [ IGNORE NULLS | RESPECT NULLS ]
    OVER (
        [ PARTITION BY partition_expression ]
        ORDER BY order_expression
    )

پارامتر scalar_expression مشخص می‌کند مقدار کدام ستون یا Expression از ردیف قبلی بازگردانده شود.

پارامتر offset تعداد ردیف‌هایی را مشخص می‌کند که تابع باید در ترتیب تعریف‌شده به عقب حرکت کند. مقدار پیش‌فرض آن 1 است.

پارامتر default مقداری است که زمانی بازگردانده می‌شود که ردیف موردنظر خارج از محدوده Partition باشد. اگر این پارامتر مشخص نشود، مقدار NULL بازگردانده می‌شود.

عبارت PARTITION BY اختیاری است و مجموعه داده را به گروه‌های مستقل تقسیم می‌کند. در صورت استفاده، LAG فقط داخل همان Partition به عقب حرکت می‌کند و از یک گروه وارد گروه دیگر نمی‌شود.

عبارت ORDER BY مشخص می‌کند مفهوم «ردیف قبلی» دقیقاً بر اساس چه ترتیبی تعیین شود. بنابراین LAG بدون ORDER BY قابل استفاده نیست.

نکته مهم این است که LAG در SQL Server یک تابع Nondeterministic محسوب می‌شود. اگر ستون‌های موجود در ORDER BY نتوانند ترتیب ردیف‌ها را به‌صورت یکتا مشخص کنند، ترتیب بین ردیف‌های دارای مقادیر مساوی تضمین‌شده نیست و در نتیجه مقدار بازگردانده‌شده توسط LAG نیز می‌تواند برای آن ردیف‌ها قابل پیش‌بینی نباشد. به همین دلیل، در Queryهای حساس معمولاً یک ستون یکتا مانند کلید اصلی به‌عنوان Tie Breaker به ORDER BY اضافه می‌شود.

مثال پایه: مقایسه فروش ماهانه با ماه قبل

فرض کنید جدول فروش ماهانه یک سازمان به شکل زیر باشد.

MonthNumber MonthlySales
1 120,000,000
2 145,000,000
3 98,000,000
4 160,000,000

با استفاده از LAG می‌توان مقدار فروش ماه قبل را در کنار هر ردیف نمایش داد.

SELECT
    MonthNumber,
    MonthlySales,
    LAG ( MonthlySales ) OVER ( ORDER BY MonthNumber ) AS PreviousMonthSales
FROM MonthlySalesSummary;

نتیجه این Query به شکل زیر خواهد بود.

MonthNumber MonthlySales PreviousMonthSales
1 120,000,000 NULL
2 145,000,000 120,000,000
3 98,000,000 145,000,000
4 160,000,000 98,000,000

برای اولین ردیف، از آنجا که ردیف قبلی وجود ندارد، مقدار PreviousMonthSales برابر با NULL است. برای سایر ردیف‌ها، مقدار دقیقاً همان فروش ماه پیش از آن است.

تحلیل مسیر منطقی اجرا

داده مرتب‌شده بر اساس MonthNumber
        │
        ▼
پیمایش ردیف به ردیف بر اساس ترتیب ORDER BY
        │
        ▼
برای هر ردیف، مراجعه به یک ردیف قبل‌تر
        │
        ▼
اگر ردیف قبلی وجود دارد
        │         │
        │         ▼
        │   بازگرداندن مقدار آن ردیف
        │
        └── اگر ردیف قبلی وجود ندارد
                  │
                  ▼
            بازگرداندن NULL یا مقدار Default

محاسبه نرخ رشد دوره‌ای با استفاده از LAG

یکی از کاربردهای بسیار رایج LAG، محاسبه نرخ رشد یا تغییر نسبت به دوره قبل است، که در ترکیب با یک محاسبه ریاضی ساده به دست می‌آید. فراخوانی چندباره LAG با همان پارامترها در یک Query از نظر خوانایی مطلوب نیست، بنابراین بهتر است مقدار آن ابتدا در یک CTE محاسبه و سپس در محاسبات بعدی از همان مقدار استفاده شود؛ استفاده از CTE در اینجا عمدتاً برای بهبود خوانایی و ساختار Query است و به‌خودی‌خود تضمینی برای محاسبه یک‌باره LAG در سطح موتور محسوب نمی‌شود.

WITH MonthlyWithPrevious AS
(
    SELECT
        MonthNumber,
        MonthlySales,
        LAG ( MonthlySales ) OVER ( ORDER BY MonthNumber ) AS PreviousMonthSales
    FROM MonthlySalesSummary
)
SELECT
    MonthNumber,
    MonthlySales,
    PreviousMonthSales,
    CAST (
        ( MonthlySales - PreviousMonthSales ) * 100.0
        / NULLIF ( PreviousMonthSales, 0 )
        AS DECIMAL(10,2)
    ) AS GrowthPercentage
FROM MonthlyWithPrevious;

در این Query، پس از محاسبه مقدار ماه قبل با LAG در CTE، اختلاف آن با مقدار ماه جاری محاسبه شده و بر مقدار ماه قبل تقسیم می‌شود تا درصد رشد به دست آید. استفاده از NULLIF برای جلوگیری از خطای تقسیم بر صفر، در مواردی که مقدار ماه قبل صفر باشد، ضروری است.

پارامتر Offset: دسترسی به چند ردیف قبل‌تر

پارامتر offset مشخص می‌کند تابع LAG چند ردیف در ترتیب تعریف‌شده در ORDER BY به عقب حرکت کند. مقدار پیش‌فرض آن 1 است.

SELECT
    MonthNumber,
    MonthlySales,
    LAG ( MonthlySales, 2 ) OVER (
        ORDER BY MonthNumber
    ) AS TwoRowsAgoSales
FROM MonthlySalesSummary;

در این مثال، LAG(MonthlySales, 2) مقدار فروش دو ردیف قبل را بازمی‌گرداند، نه الزاماً مقدار دو ماه تقویمی قبل.

این تفاوت بسیار مهم است. فرض کنید داده‌های زیر وجود داشته باشد:

MonthNumber
-----------
1
2
4

برای ردیف مربوط به ماه 4، مقدار LAG(..., 2) مربوط به ماه 1 خواهد بود، زیرا ماه 1 دو ردیف قبل از ماه 4 قرار دارد.

بنابراین offset بر اساس تعداد ردیف‌های موجود در Partition عمل می‌کند و مفهوم تقویمی مانند «دو ماه قبل» را به‌صورت ذاتی درک نمی‌کند.

اگر هدف تحلیل دوره‌ای واقعی باشد، باید ابتدا مشخص شود که هر دوره دقیقاً یک ردیف دارد یا خیر و آیا تقویم داده بدون شکاف است یا خیر. در داده‌هایی که ممکن است ماه‌ها، روزها یا دوره‌های زمانی حذف شده باشند، استفاده مستقیم از LAG(..., 2) به‌عنوان معادل «دو دوره تقویمی قبل» می‌تواند نتیجه نادرستی ایجاد کند.

پارامتر Default تعیین مقدار جایگزین برای ردیف‌های بدون سابقه

به‌طور پیش‌فرض، وقتی ردیف قبلی وجود ندارد، LAG مقدار NULL بازمی‌گرداند. با استفاده از پارامتر سوم، می‌توان یک مقدار جایگزین معنادارتر تعیین کرد.

SELECT
    MonthNumber,
    MonthlySales,
    LAG ( MonthlySales, 1, 0 ) OVER ( ORDER BY MonthNumber ) AS PreviousMonthSalesOrZero
FROM MonthlySalesSummary;

در این نسخه، برای اولین ردیف که ردیف قبلی ندارد، به‌جای NULL، مقدار صفر بازگردانده می‌شود. انتخاب بین NULL و یک مقدار پیش‌فرض باید بر اساس معنای واقعی گزارش انجام شود؛ اگر عدم وجود داده قبلی باید به‌صورت «داده‌ای برای مقایسه نیست» تفسیر شود، NULL مناسب‌تر است. اگر باید به‌صورت یک مقدار عددی معنادار در محاسبات بعدی شرکت کند، تعیین یک مقدار پیش‌فرض منطقی‌تر است.

رفتار LAG در برابر مقادیر NULL: RESPECT NULLS و IGNORE NULLS

در LAG باید بین دو وضعیت متفاوت تفاوت قائل شد: وجود نداشتن ردیف قبلی و وجود داشتن ردیف قبلی که مقدار Expression موردنظر در آن NULL است.

به‌صورت پیش‌فرض، LAG از رفتار RESPECT NULLS استفاده می‌کند. در این حالت، اگر ردیف قبلی وجود داشته باشد اما مقدار موردنظر در آن NULL باشد، همان NULL به‌عنوان نتیجه LAG بازگردانده می‌شود.

از SQL Server 2022، امکان استفاده از IGNORE NULLS و RESPECT NULLS برای LAG و LEAD فراهم شده است. IGNORE NULLS هنگام جست‌وجوی مقدار قبلی، مقادیر NULL را نادیده می‌گیرد و به سمت مقدار غیر NULL قبلی حرکت می‌کند. Microsoft همچنین اصلاحیه‌ای مرتبط با IGNORE NULLS در LAG و LEAD را در SQL Server 2022 CU4 منتشر کرده است.

SELECT
    OrderDate,
    OrderID,
    SalesAmount,
    LAG ( SalesAmount ) IGNORE NULLS OVER (
        ORDER BY OrderDate, OrderID
    ) AS PreviousNonNullSales
FROM DailySales;

در این Query، اگر مقدار SalesAmount در ردیف قبلی NULL باشد، LAG با IGNORE NULLS به عقب حرکت می‌کند تا نزدیک‌ترین مقدار غیر NULL را پیدا کند.

در مقابل:

LAG ( SalesAmount ) RESPECT NULLS OVER (
    ORDER BY OrderDate, OrderID
)

یا استفاده از LAG بدون تعیین گزینه NULLS، مقدار NULL موجود در ردیف قبلی را حفظ می‌کند.

بنابراین انتخاب بین این دو رفتار باید بر اساس معنای داده انجام شود. اگر NULL بخشی از معنای واقعی داده است و باید در تحلیل حفظ شود، RESPECT NULLS مناسب است. اگر هدف یافتن آخرین مقدار معتبر ثبت‌شده است و NULL باید نادیده گرفته شود، IGNORE NULLS انتخاب مناسب‌تری است.

استفاده از PARTITION BY برای محاسبه LAG در سطح هر گروه

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

فرض کنید در یک سیستم فروش چند‌شعبه‌ای، می‌خواهیم فروش هر ماه هر شعبه را با فروش ماه قبل همان شعبه مقایسه کنیم، نه با ماه قبل شعبه دیگر.

SELECT
    BranchID,
    MonthNumber,
    MonthlySales,
    LAG ( MonthlySales ) OVER (
        PARTITION BY BranchID
        ORDER BY MonthNumber
    ) AS PreviousMonthSalesSameBranch
FROM BranchMonthlySales;

در این Query، داده‌ها ابتدا بر اساس BranchID به گروه‌های مستقل تقسیم می‌شوند و سپس در هر گروه، LAG مقدار ماه قبل را در همان گروه محاسبه می‌کند. برای اولین ماه هر شعبه، مقدار PreviousMonthSalesSameBranch برابر NULL خواهد بود، زیرا LAG هرگز به گروه دیگری سرریز نمی‌کند.

تحلیل مسیر منطقی اجرا با PARTITION BY

داده خام جدول BranchMonthlySales
        │
        ▼
PARTITION BY BranchID
        │
        ▼
تفکیک به گروه‌های مستقل بر اساس شعبه
        │
        ▼
ORDER BY MonthNumber در هر گروه
        │
        ▼
LAG در هر گروه به‌طور مستقل محاسبه می‌شود
        │
        ▼
اولین ماه هر شعبه، NULL می‌گیرد

این رفتار دقیقاً همان چیزی است که در تحلیل چندشعبه‌ای موردنیاز است؛ بدون PARTITION BY، ممکن است فروش ماه اول یک شعبه با فروش ماه آخر شعبه دیگر مقایسه شود، که از نظر تحلیلی کاملاً بی‌معنا است.

تفاوت LAG با LEAD

در کنار LAG، تابع مکمل آن یعنی LEAD نیز در SQL Server وجود دارد. در حالی که LAG به ردیف‌های پیش از ردیف جاری دسترسی می‌دهد، LEAD دقیقاً عکس این کار را انجام می‌دهد و به ردیف‌های بعد از ردیف جاری دسترسی می‌دهد.

SELECT
    MonthNumber,
    MonthlySales,
    LAG ( MonthlySales ) OVER ( ORDER BY MonthNumber ) AS PreviousMonth,
    LEAD ( MonthlySales ) OVER ( ORDER BY MonthNumber ) AS NextMonth
FROM MonthlySalesSummary;
MonthNumber MonthlySales PreviousMonth NextMonth
1 120,000,000 NULL 145,000,000
2 145,000,000 120,000,000 98,000,000
3 98,000,000 145,000,000 160,000,000
4 160,000,000 98,000,000 NULL

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

تفاوت LAG با ROW_NUMBER

اگرچه LAG و ROW_NUMBER هر دو در دسته Window Functions قرار می‌گیرند و هر دو بر پایه یک بند OVER عمل می‌کنند، این دو تابع مسئله کاملاً متفاوتی را حل می‌کنند و نباید با هم اشتباه گرفته شوند.

ROW_NUMBER به هر ردیف یک شماره ترتیبی اختصاص می‌دهد، اما در صورت وجود مقادیر مساوی در ORDER BY، ترتیب تخصیص این شماره‌ها بدون یک Tie Breaker مناسب قابل اتکا نیست. این تابع هیچ اطلاعاتی از مقدار سایر ردیف‌ها در اختیار نمی‌گذارد. LAG برعکس عمل می‌کند: هدف آن شماره‌گذاری نیست، بلکه بازگرداندن مقدار واقعی یک ستون از یک ردیف مشخص پیش از ردیف جاری است.

SELECT
    MonthNumber,
    MonthlySales,
    ROW_NUMBER () OVER ( ORDER BY MonthNumber ) AS RowNum,
    LAG ( MonthlySales ) OVER ( ORDER BY MonthNumber ) AS PreviousMonthSales
FROM MonthlySalesSummary;

پیش از در دسترس قرار گرفتن LAG، یکی از روش‌های رایج برای شبیه‌سازی همین رفتار، ترکیب ROW_NUMBER با یک Self Join بود؛ ابتدا به هر ردیف یک شماره ترتیبی اختصاص داده می‌شد و سپس جدول با خودش بر اساس شماره ردیف منهای یک Join می‌شد تا مقدار ردیف قبلی به دست آید.

با ROW_NUMBER و Self Join:
WITH NumberedRows AS
(
    SELECT
        MonthNumber,
        MonthlySales,
        ROW_NUMBER () OVER ( ORDER BY MonthNumber ) AS RowNum
    FROM MonthlySalesSummary
)
SELECT
    curr.MonthNumber,
    curr.MonthlySales,
    prev.MonthlySales AS PreviousMonthSales
FROM NumberedRows curr
LEFT JOIN NumberedRows prev
    ON prev.RowNum = curr.RowNum - 1;

این روش از نظر نتیجه معادل LAG است، اما نیازمند یک CTE اضافی و یک Join جداگانه است که هم خوانایی کمتری دارد و هم معمولاً پیچیدگی اجرایی بیشتری نسبت به استفاده مستقیم از LAG به همراه دارد. به همین دلیل، LAG جایگزین ساده‌تر و شفاف‌تری برای این الگوی قدیمی محسوب می‌شود.

کاربرد سازمانی: تشخیص تغییر وضعیت در توالی رویدادها

یکی از کاربردهای مهم LAG در سامانه‌های سازمانی، شناسایی تغییر وضعیت یک موجودیت نسبت به وضعیت قبلی آن است.

فرض کنید جدول OrderStatusHistory هر تغییر وضعیت سفارش را ثبت می‌کند:

WITH StatusHistory AS
(
SELECT
OrderID,
StatusDate,
OrderStatus,
LAG(OrderStatus) OVER
(
PARTITION BY OrderID
ORDER BY StatusDate, StatusHistoryID
) AS PreviousStatus,
LAG(StatusHistoryID) OVER
(
PARTITION BY OrderID
ORDER BY StatusDate, StatusHistoryID
) AS PreviousHistoryID
FROM OrderStatusHistory
)
SELECT
OrderID,
StatusDate,
OrderStatus,
PreviousStatus
FROM StatusHistory
WHERE
PreviousHistoryID IS NOT NULL
AND PreviousStatus IS DISTINCT FROM OrderStatus;

در این Query، StatusHistoryID به‌عنوان Tie Breaker اضافه شده است تا اگر یک سفارش چند رویداد با StatusDate یکسان داشته باشد، ترتیب آن‌ها مشخص باقی بماند.

عبارت IS DISTINCT FROM برای مقایسه‌ای طراحی شده است که باید در برابر NULL نیز نتیجه قطعی TRUE یا FALSE داشته باشد. این قابلیت از SQL Server 2022 در دسترس است.

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

  • اولین وضعیت سفارش
  • تغییر از یک وضعیت به وضعیت دیگر
  • تغییر از NULL به یک مقدار
  • تغییر از یک مقدار به NULL
  • باقی ماندن مقدار NULL بدون تغییر

اگر بخواهیم اولین رکورد هر سفارش را نیز به‌عنوان یک رویداد مستقل برگردانیم، باید آن را جداگانه در منطق Query در نظر بگیریم، زیرا نبودن مقدار قبلی با وجود داشتن مقدار قبلی برابر با NULL یکسان نیست.

کاربرد سازمانی: محاسبه فاصله زمانی بین رویدادهای متوالی

سناریوی دیگری که LAG در آن نقش کلیدی دارد، محاسبه فاصله زمانی بین دو رویداد متوالی برای یک موجودیت مشخص است، مانند فاصله بین دو خرید متوالی یک مشتری.

WITH CustomerPurchases AS
(
    SELECT
        CustomerID,
        OrderID,
        OrderDate,
        LAG ( OrderDate ) OVER (
            PARTITION BY CustomerID
            ORDER BY OrderDate, OrderID
        ) AS PreviousOrderDate
    FROM Orders
)
SELECT
    CustomerID,
    OrderDate,
    PreviousOrderDate,
    DATEDIFF ( DAY, PreviousOrderDate, OrderDate ) AS DaysSincePreviousOrder
FROM CustomerPurchases;

در این Query، برای هر سفارش مشتری، تاریخ سفارش قبلی همان مشتری با LAG پیدا می‌شود و سپس با DATEDIFF فاصله روزهای بین این دو سفارش محاسبه می‌شود. توجه شود که در ORDER BY علاوه بر OrderDate، ستون OrderID نیز به‌عنوان Tie Breaker اضافه شده است، زیرا اگر یک مشتری چند سفارش را دقیقاً در یک تاریخ ثبت کرده باشد، بدون این ستون دوم ترتیب پردازش آن‌ها از نظر موتور تضمین‌شده نیست و نتیجه LAG می‌تواند در اجراهای مختلف Query متفاوت باشد. نتیجه این محاسبه در تحلیل‌های مربوط به رفتار خرید مشتری، مانند شناسایی الگوی خرید منظم یا تشخیص مشتریانی که فاصله خریدشان به‌طور غیرمعمول افزایش یافته و در معرض ریزش هستند، کاربرد گسترده‌ای دارد.

ترکیب LAG با CASE برای منطق شرطی پیشرفته

LAG اغلب در ترکیب با CASE برای ساخت منطق شرطی پیچیده‌تر بر اساس روند تغییرات استفاده می‌شود.

WITH SalesWithPrevious AS
(
    SELECT
        MonthNumber,
        MonthlySales,
        LAG ( MonthlySales ) OVER ( ORDER BY MonthNumber ) AS PreviousMonthSales
    FROM MonthlySalesSummary
)
SELECT
    MonthNumber,
    MonthlySales,
    PreviousMonthSales,
    CASE
        WHEN PreviousMonthSales IS NULL THEN N'بدون داده مقایسه'
        WHEN MonthlySales > PreviousMonthSales THEN N'رشد'
        WHEN MonthlySales < PreviousMonthSales THEN N'کاهش'
        ELSE N'بدون تغییر'
    END AS TrendIndicator
FROM SalesWithPrevious;

این Query یک برچسب متنی مدیریتی بر اساس روند تغییر فروش نسبت به ماه قبل تولید می‌کند. چنین الگویی در داشبوردهای مدیریتی که نیاز به نمایش سریع روند صعودی یا نزولی هر رکورد دارند، بدون نیاز به محاسبات پیچیده در لایه گزارش‌گیری، کاربرد عملی زیادی دارد.

تفاوت LAG با روش‌های سنتی Self Join

پیش از استفاده گسترده از Window Functionها، یکی از روش‌های رایج برای دسترسی به ردیف قبلی، استفاده از Self Join بود. برای مثال، اگر MonthNumber یکتا و بدون شکاف باشد، می‌توان نوشت:

SELECT
    curr.MonthNumber,
    curr.MonthlySales,
    prev.MonthlySales AS PreviousMonthSales
FROM MonthlySalesSummary AS curr
LEFT JOIN MonthlySalesSummary AS prev
    ON prev.MonthNumber = curr.MonthNumber - 1;

در مقابل، LAG همین منطق را به شکل مستقیم‌تری بیان می‌کند:

SELECT
    MonthNumber,
    MonthlySales,
    LAG ( MonthlySales ) OVER (
        ORDER BY MonthNumber
    ) AS PreviousMonthSales
FROM MonthlySalesSummary;

با این حال، این دو Query همیشه از نظر معنایی معادل نیستند.

Self Join بالا فرض می‌کند MonthNumber یکتا و پیوسته است. اگر ماهی در داده وجود نداشته باشد، یا چند ردیف برای یک ماه وجود داشته باشد، شرط MonthNumber - 1 دیگر الزاماً مفهوم «ردیف قبلی» را بیان نمی‌کند.

LAG در مقابل، بر اساس ترتیب ردیف‌ها در Window کار می‌کند و می‌تواند بدون ساختن Join جداگانه مقدار ردیف قبلی را برگرداند.

از نظر Performance نیز نباید نتیجه‌گیری کرد که LAG همیشه سریع‌تر از Self Join است. انتخاب روش مناسب به حجم داده، Cardinality، Indexها، شکل Query، آمار و Execution Plan بستگی دارد. مزیت اصلی LAG در این سناریو، بیان مستقیم منطق «دسترسی به ردیف قبلی» و کاهش پیچیدگی Query است. Performance نهایی باید با Execution Plan واقعی و داده واقعی سیستم ارزیابی شود.

تأثیر LAG بر Execution Plan و Performance

اجرای LAG نیازمند دسترسی به داده‌ها در ترتیب مشخص‌شده در ORDER BY است. اگر ترتیب موردنیاز از طریق Index مناسب در اختیار موتور نباشد، احتمال ایجاد عملیات Sort در Execution Plan افزایش پیدا می‌کند.

نقش Index در Performance تابع LAG

LAG برای تعیین ردیف قبلی به ترتیب مشخص‌شده در ORDER BY نیاز دارد. اگر داده از قبل در ترتیب موردنیاز در دسترس نباشد، Query Optimizer ممکن است در Execution Plan یک عملیات Sort ایجاد کند.

برای مثال، در Query زیر:

SELECT
    BranchID,
    MonthNumber,
    MonthlySales,
    LAG ( MonthlySales ) OVER (
        PARTITION BY BranchID
        ORDER BY MonthNumber
    ) AS PreviousMonthSales
FROM BranchMonthlySales;

یک Index با کلیدهای:

CREATE NONCLUSTERED INDEX IX_BranchMonthlySales_Branch_Month
ON BranchMonthlySales
(
    BranchID,
    MonthNumber
)
INCLUDE
(
    MonthlySales
);

می‌تواند در شرایط مناسب به موتور کمک کند داده را با ترتیب موردنیاز Window Function در اختیار داشته باشد.

با این حال، وجود این Index تضمین نمی‌کند که همیشه Sort حذف شود یا Query حتماً سریع‌تر اجرا شود. Query Optimizer بر اساس Cardinality، Statistics، Selectivity، هزینه دسترسی به Index و سایر عوامل Execution Plan نهایی را انتخاب می‌کند.

بنابراین در Queryهای حجیم، هدف نباید صرفاً «ساختن Index برای LAG» باشد. باید Execution Plan واقعی، Logical Reads، CPU Time و Elapsed Time قبل و بعد از تغییر بررسی شود.

مقایسه هزینه LAG با سایر توابع Window

در بسیاری از Queryهای Window، مرتب‌سازی بخش قابل‌توجهی از هزینه اجرا را تشکیل می‌دهد. بنابراین شباهت PARTITION BY و ORDER BY بین چند Window Function می‌تواند به استفاده مجدد از ترتیب داده‌ها در Execution Plan کمک کند، هرچند این موضوع به Plan نهایی وابسته است.

دو فراخوانی LAG با همان PARTITION BY و ORDER BY:
SELECT
    MonthNumber,
    LAG ( MonthlySales, 1 ) OVER ( ORDER BY MonthNumber ) AS OneMonthAgo,
    LAG ( MonthlySales, 2 ) OVER ( ORDER BY MonthNumber ) AS TwoRowsAgo
FROM MonthlySalesSummary;

از آنجا که هر دو فراخوانی LAG از همان PARTITION BY و ORDER BY استفاده می‌کنند، Optimizer می‌تواند در بسیاری از Execution Planها از یک ترتیب‌دهی مشترک برای محاسبه هر دو مقدار استفاده کند، به‌جای آنکه دو بار جداگانه داده را مرتب کند. با این حال، شکل نهایی Plan به Query، آمار جدول و شرایط مدل بستگی دارد و نباید به‌عنوان یک تضمین قطعی در نظر گرفته شود.

اشتباهات رایج در استفاده از LAG

فراموش کردن PARTITION BY در داده‌های چندگروهی

یکی از رایج‌ترین اشتباهات، استفاده از LAG بدون PARTITION BY روی داده‌ای که در واقع شامل چند گروه مستقل است، مانند تاریخچه چند مشتری یا چند شعبه در یک جدول واحد. بدون PARTITION BY، اولین ردیف یک گروه ممکن است به‌اشتباه با آخرین ردیف گروه قبلی مقایسه شود، که از نظر تحلیلی کاملاً نادرست است.

فراموش کردن Tie Breaker در ORDER BY

اگر ستون ORDER BY مقادیر تکراری داشته باشد، ترتیب دقیق پردازش رکوردهای هم‌ارزش از نظر موتور تضمین‌شده نیست، مگر آنکه یک ستون اضافه به‌عنوان Tie Breaker در ORDER BY لحاظ شود. این موضوع می‌تواند باعث شود نتیجه LAG برای رکوردهای با تاریخ یا شماره یکسان، در اجراهای مختلف Query متفاوت باشد.

LAG ( MonthlySales ) OVER (
    PARTITION BY BranchID
    ORDER BY OrderDate, OrderID
) AS PreviousValue

اگر OrderID در هر Partition یکتا باشد، افزودن آن به‌عنوان Tie Breaker باعث می‌شود ترکیب ستون‌های ORDER BY یک ترتیب یکتا و قابل پیش‌بینی ایجاد کند.

اشتباه گرفتن Offset با واحد زمانی تقویمی

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

نادیده گرفتن تفاوت NULL طبیعی و NULL ناشی از نبود ردیف قبلی

هنگام تفسیر نتیجه LAG، باید توجه داشت که مقدار NULL می‌تواند به دو دلیل متفاوت رخ دهد: یا ردیف قبلی اصلاً وجود ندارد، یا ردیف قبلی وجود دارد اما مقدار ستون موردنظر در آن ردیف خودش NULL بوده است، رفتاری که با گزینه پیش‌فرض RESPECT NULLS حفظ می‌شود. در گزارش‌هایی که این تمایز اهمیت دارد یا هدف یافتن آخرین مقدار معتبر است، باید بین حفظ این رفتار پیش‌فرض یا استفاده از IGNORE NULLS تصمیم آگاهانه گرفت.

استفاده از LAG در جایی که یک Aggregation ساده کافی است

گاهی توسعه‌دهندگان از LAG برای محاسباتی استفاده می‌کنند که در واقع با یک تابع تجمیعی ساده مانند MIN یا MAX همراه با GROUP BY قابل انجام است. LAG باید برای سناریوهایی رزرو شود که واقعاً نیاز به دسترسی ردیف‌به‌ردیف به مقدار قبلی وجود دارد، نه برای هر نوع مقایسه‌ای که می‌تواند با یک Aggregation ساده‌تر حل شود.

Best Practiceهای استفاده از LAG در پروژه‌های سازمانی

انتخاب ستون‌های PARTITION BY باید دقیقاً منطبق با مرز منطقی گروه‌بندی موردنیاز کسب‌وکار باشد، تا مقایسه هر ردیف تنها با ردیف‌های واقعاً مرتبط همان گروه انجام شود.

طراحی Index متناسب با ترکیب PARTITION BY و ORDER BY باید پیش از استقرار نهایی Query در محیط عملیاتی بررسی شود، به‌خصوص در Queryهایی که روی جداول با میلیون‌ها رکورد اجرا می‌شوند.

همیشه باید یک Tie Breaker مناسب در ORDER BY لحاظ شود تا رفتار Query در برابر مقادیر تکراری قابل پیش‌بینی و پایدار باقی بماند، به‌خصوص با توجه به ماهیت Nondeterministic این تابع.

پیش از اتکا به پارامتر Offset به‌عنوان معادل یک بازه زمانی تقویمی، باید از پیوستگی و کامل بودن توالی داده در مجموعه موردنظر اطمینان حاصل شود.

هنگام تفسیر مقدار NULL خروجی LAG، باید تفاوت بین نبود ردیف قبلی و NULL بودن واقعی مقدار در ردیف قبلی، در صورت اهمیت برای گزارش، به‌صراحت مدیریت شود؛ در صورت نیاز به یافتن آخرین مقدار معتبر صرف‌نظر از شکاف داده، استفاده از IGNORE NULLS باید بررسی گردد.

در Queryهای پیچیده با چند فراخوانی LAG یا LEAD، محاسبه نتیجه در یک CTE و استفاده مجدد از آن در بخش‌های بعدی Query، خوانایی کد را افزایش می‌دهد.

پیش از انتخاب بین LAG و روش‌های قدیمی‌تر مانند ترکیب ROW_NUMBER با Self Join، باید هر دو راهکار روی داده واقعی سازمان با Execution Plan واقعی مقایسه شوند، نه صرفاً بر اساس این فرض که LAG همیشه سریع‌تر است.

جمع‌بندی

LAG یکی از توابع کلیدی خانواده Window Functions در SQL Server است که امکان دسترسی مستقیم به مقدار ردیف‌های پیشین را، بدون نیاز به Self Join پیچیده، فراهم می‌کند. این تابع در سناریوهای رایجی مانند محاسبه نرخ رشد دوره‌ای، تشخیص تغییر وضعیت در توالی رویدادها و محاسبه فاصله زمانی بین رویدادهای متوالی، نقش کلیدی دارد و از نظر مفهومی با ROW_NUMBER که صرفاً شماره‌گذاری می‌کند، کاملاً متفاوت است.

استفاده صحیح از LAG نیازمند توجه دقیق به انتخاب ستون‌های PARTITION BY و ORDER BY، درک درست از اینکه Offset معادل تعداد ردیف است نه دوره تقویمی، تصمیم آگاهانه بین RESPECT NULLS و IGNORE NULLS، رعایت Tie Breaker و طراحی Index متناسب برای کاهش احتمال Sort غیرضروری روی جداول بزرگ است. در مقایسه با روش‌های سنتی مبتنی بر Self Join یا ترکیب ROW_NUMBER با Join، LAG معمولاً از نظر بیان مستقیم منطق دسترسی به ردیف قبلی و خوانایی Query انتخاب مناسب‌تری است. با این حال، نمی‌توان برتری Performance آن را به‌صورت عمومی تضمین کرد و انتخاب نهایی باید با بررسی Execution Plan و معیارهایی مانند Logical Reads، CPU Time و Elapsed Time روی داده واقعی انجام شود.

سوالات متداول FAQ

تفاوت LAG و LEAD در چیست؟
LAG به مقدار ردیف‌های پیش از ردیف جاری دسترسی می‌دهد، در حالی که LEAD به مقدار ردیف‌های بعد از ردیف جاری دسترسی می‌دهد. انتخاب بین آن‌ها بر اساس جهت مقایسه موردنیاز گزارش انجام می‌شود.

تفاوت LAG و ROW_NUMBER چیست؟
ROW_NUMBER به هر ردیف یک شماره ترتیبی اختصاص می‌دهد و هیچ اطلاعاتی از مقدار سایر ردیف‌ها ارائه نمی‌کند. LAG برعکس عمل می‌کند و مقدار واقعی یک ستون را از یک ردیف مشخص پیش از ردیف جاری بازمی‌گرداند. پیش از در دسترس قرار گرفتن LAG، ترکیب ROW_NUMBER با یک Self Join یکی از روش‌های رایج برای شبیه‌سازی همین رفتار بود.

تفاوت RESPECT NULLS و IGNORE NULLS در LAG چیست؟
RESPECT NULLS که رفتار پیش‌فرض است، اگر مقدار ردیف قبلی NULL باشد، همان NULL را بازمی‌گرداند. IGNORE NULLS به عقب ادامه می‌دهد تا نزدیک‌ترین مقدار غیر NULL را پیدا کند، که برای یافتن آخرین مقدار معتبر در داده‌های دارای شکاف مفید است.

چرا Query مبتنی بر LAG گاهی کند اجرا می‌شود؟
گاهی اجرای LAG به دلیل نیاز به مرتب‌سازی داده‌ها بر اساس ستون‌های ORDER BY و PARTITION BY هزینه‌بر می‌شود. اگر داده‌ها از قبل در ترتیب مناسب در دسترس نباشند، Query Optimizer ممکن است از عملیات Sort استفاده کند که روی جداول بزرگ می‌تواند زمان اجرا را افزایش دهد. همچنین عواملی مانند حجم داده، Cardinality، Statistics، Memory Grant، طراحی Index و Execution Plan نهایی نیز بر Performance تأثیرگذار هستند. به همین دلیل، علت کندی Queryهای مبتنی بر LAG را باید با بررسی Execution Plan و شاخص‌هایی مانند Logical Reads، CPU Time و Elapsed Time ارزیابی کرد.

مشاوره تخصصی SQL Server و بهینه‌سازی Query با لاندا

اگر در طراحی Queryهای تحلیلی، استفاده از توابع Window مانند LAG و LEAD، بهینه‌سازی Execution Plan یا طراحی Index مناسب برای جداول حجیم با چالش مواجه هستید، تیم فنی توسعه فناوری اطلاعات لاندا می‌تواند در بررسی و بهینه‌سازی راهکار SQL Server شما کمک کند.

از تحلیل Query و Execution Plan تا طراحی Index، بهینه‌سازی Performance و پیاده‌سازی راهکارهای مقیاس‌پذیر SQL Server، لاندا در کنار سازمان‌هاست تا گزارش‌ها و سامانه‌های داده‌ای با دقت، پایداری و سرعت بیشتری اجرا شوند.

برای بررسی نیازهای پروژه و دریافت مشاوره تخصصی SQL Server و بهینه‌سازی Query، با تیم لاندا، تماس یا ✆ باشید.

روزبه امیرعصامی

SQL Server

DBASQL Serverاسکیوال سرورباز طراحی دیتابیسبهینه سازی کوئریپایگاه داده

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

No comment

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *