LEAD

تابع LEAD در SQL: توضیح جامع با مثال

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

سینتکس:

LEAD(column_name, offset, default_value)
OVER (PARTITION BY partition_column_name ORDER BY order_column_name)

اجزاء:

• column_name: نام ستونی که می خواهید از آن مقدار را بدست آورید.
• offset: (اختیاری) تعداد ردیف هایی که می خواهید از ردیف فعلی عبور کنید. پیش فرض ۱ است.
• default_value: (اختیاری) مقداری که باید در صورت عدم وجود ردیف بعدی برگردانده شود. پیش فرض NULL است.
• PARTITION BY partition_column_name: (اختیاری) ستونی که برای پارتیشن بندی مجموعه نتایج استفاده می شود. محاسبه برای هر پارتیشن جداگانه انجام می شود.
• ORDER BY order_column_name: ستونی که برای مرتب سازی مجموعه نتایج قبل از اعمال تابع استفاده می شود.

مثال:

فرض کنید جدولی به نام sales با ستون های order_id, customer_id, sales_date و sales_amount داریم. می خواهیم تفاوت قیمت فروش بین هر سفارش و سفارش بعدی را محاسبه کنیم.

SELECT
  order_id,
  customer_id,
  sales_date,
  sales_amount,
  LEAD(sales_amount, ۱) OVER (PARTITION BY customer_id ORDER BY sales_date) AS next_sales_amount,
  LEAD(sales_amount, ۱) OVER (PARTITION BY customer_id ORDER BY sales_date) - sales_amount AS sales_difference
FROM sales;

در این مثال:

• PARTITION BY customer_id: محاسبه را برای هر مشتری جداگانه انجام می دهد.
• ORDER BY sales_date: سفارشات را بر اساس تاریخ فروش مرتب می کند.
• LEAD(sales_amount, 1) OVER (...): قیمت فروش سفارش بعدی را برای هر سفارش بدست می آورد.
• sales_difference: تفاوت بین قیمت فروش فعلی و قیمت فروش بعدی را محاسبه می کند.

نکات:

• تابع LEAD فقط می تواند به ردیف های بعدی در همان پارتیشن و در همان جهت مرتب سازی دسترسی داشته باشد.
• اگر ردیف بعدی وجود نداشته باشد، default_value برگردانده می شود.
• می توانید از چندین تابع LEAD در یک عبارت SELECT استفاده کنید.

مزایای استفاده از تابع LEAD:

• خوانایی و کارآمدی کد را افزایش می دهد.
• نیاز به اتصالات خودکار را کاهش می دهد.
• محاسبات پیچیده را بدون نیاز به پرس و جوهای فرعی امکان پذیر می کند.

موارد استفاده از تابع LEAD:

• مقایسه مقادیر در ردیف فعلی با مقادیر در ردیف های بعدی
• محاسبه تغییرات در طول زمان
• یافتن الگوها در داده ها
• rank کردن رکوردها

منابع:

• https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver16
• https://www.codecademy.com/resources/docs/sql/window-functions
• https://www.sqltutorial.org/sql-functions/