LAG

توابع پنجره SQL LAG() – توضیح جامع با مثال

LAG یک تابع پنجره در SQL است که برای دسترسی به مقادیر ردیف های قبلی در همان مجموعه نتایج بدون استفاده از self-join استفاده می شود. این تابع برای مقایسه مقادیر ردیف فعلی با مقادیر ردیف های قبلی، به خصوص در مورد داده های زمان بندی شده یا ستون های خاص، بسیار مفید است.

نحو:

LAG(expression, offset, default)
OVER (PARTITION BY partition_expression
      ORDER BY order_expression)

اجزاء:

• expression: ستونی که می خواهید مقدار آن را از ردیف قبلی دریافت کنید.
• offset: (اختیاری) یک عدد مثبت که نشان دهنده تعداد ردیف های قبلی است که می خواهید به آن دسترسی داشته باشید. اگر مشخص نشده باشد، پیش فرض ۱ است.
• default: (اختیاری) مقداری که اگر offset خارج از محدوده باشد، برگردانده می شود. اگر مشخص نشده باشد، NULL برگردانده می شود.
• PARTITION BY: (اختیاری) عبارتی که مجموعه نتایج را به پارتیشن هایی بر اساس یک ستون یا مجموعه ای از ستون ها تقسیم می کند. LAG برای هر پارتیشن به طور جداگانه اعمال می شود.
• ORDER BY: (اختیاری) عبارتی که ترتیب ردیف ها را در هر پارتیشن تعیین می کند. LAG بر اساس ترتیب مشخص شده عمل می کند.

مثال:

فرض کنید جدولی به نام sales داریم که شامل ستون های sale_date, employee_id و sale_amount است. می خواهیم برای هر کارمند، تفاوت فروش سال جاری با سال قبل را محاسبه کنیم.

SELECT
  sale_date,
  employee_id,
  sale_amount,
  LAG(sale_amount, ۱) OVER (PARTITION BY employee_id ORDER BY sale_date) AS previous_year_sale_amount,
  sale_amount - LAG(sale_amount, ۱) OVER (PARTITION BY employee_id ORDER BY sale_date) AS sale_difference
FROM sales;

در این مثال:

• LAG(sale_amount, 1) OVER (PARTITION BY employee_id ORDER BY sale_date) مقدار sale_amount را از ردیف قبلی برای هر کارمند برمی گرداند.
• sale_amount - LAG(sale_amount, 1) OVER (PARTITION BY employee_id ORDER BY sale_date) تفاوت بین sale_amount فعلی و sale_amount سال قبل را محاسبه می کند.

نکات:

• LAG فقط می تواند به ردیف های قبلی در همان مجموعه نتایج دسترسی داشته باشد.
• LAG برای محاسباتی که به مقادیر از ردیف های قبلی نیاز دارند، مانند محاسبه تغییرات در طول زمان، مفید است.
• LAG می تواند با سایر توابع پنجره مانند ROW_NUMBER(), RANK(), و SUM() ترکیب شود.

منابع:

• https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16
• https://www.geeksforgeeks.org/videos/window-functions-in-sql/

مثال های اضافی:

• محاسبه درصد تغییر فروش سالانه:

SELECT
  sale_date,
  employee_id,
  sale_amount,
  (sale_amount - LAG(sale_amount, ۱) OVER (PARTITION BY employee_id ORDER BY sale_date)) / LAG(sale_amount, ۱) OVER (PARTITION BY employee_id ORDER BY sale_date) * ۱۰۰ AS sale_change_percent
FROM sales;

• یافتن ردیف هایی که فروش آنها نسبت به سال قبل بیش از ۱۰% افزایش یافته است:

SELECT
  sale_date,
  employee_id,
  sale_amount,
  LAG(sale_amount, ۱) OVER (PARTITION BY employee_id ORDER BY sale_date) AS previous_year_sale_amount,
  sale_amount - LAG(sale_amount, ۱) OVER (PARTITION BY employee_id ORDER BY sale_date) AS sale_difference
FROM sales
WHERE sale_difference / LAG(sale_amount, ۱) OVER (PARTITION BY employee_id ORDER BY sale_date) * ۱۰۰ > ۱۰;