آموزش توابع پنجره ای SQL Server LEAD و LAG تحلیل پیشرفته داده

آموزش جامع توابع پنجره‌ای SQL Server: LEAD و LAG برای تحلیل داده پیشرفته

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

آماده‌سازی داده‌های نمونه

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

DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (
    SalesID INT IDENTITY(1,1) PRIMARY KEY,
    SalesPerson VARCHAR(50) NOT NULL,
    SalesMonth DATE NOT NULL,
    SalesAmount MONEY NOT NULL
);
INSERT INTO dbo.Sales (SalesPerson, SalesMonth, SalesAmount) VALUES
('Alice', '2023-01-01', 1000.00),
('Alice', '2023-02-01', 1200.00),
('Alice', '2023-03-01', 1100.00),
('Bob', '2023-01-01', 900.00),
('Bob', '2023-02-01', 950.00),
('Bob', '2023-03-01', 1000.00),
('Charlie', '2023-01-01', 1500.00),
('Charlie', '2023-02-01', 1400.00),
('Charlie', '2023-03-01', 1600.00),
('Alice', '2023-04-01', 1300.00);

توابع LEAD و LAG در SQL Server چیست؟

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

تابع LEAD در SQL Server

تابع LEAD به شما امکان می‌دهد تا به تعداد ردیف‌های مشخصی در مجموعه نتایج به جلو نگاه کنید و مقداری را از آن ردیف “آینده” برگردانید. این تابع به خصوص برای مقایسه مقدار فعلی با مقدار بعدی یا محاسبه رشد در دوره‌های متوالی بسیار مفید است.

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

در این ساختار:

• scalar_expression: ستونی است که می‌خواهید مقدار آن را از ردیف بعدی برگردانید.
• offset: تعداد ردیف‌هایی است که می‌خواهید به جلو بروید. اگر این مقدار مشخص نشود، پیش‌فرض آن ۱ است (یعنی ردیف بلافاصله بعدی).
• default: مقداری است که در صورتی که هیچ ردیف بعدی در موقعیت مشخص شده یافت نشود، برگردانده می‌شود. اگر این مقدار مشخص نشود، پیش‌فرض آن NULL است.
• OVER ( [ partition_by_clause ] order_by_clause ): این بخش نحوه پارتیشن‌بندی و مرتب‌سازی داده‌ها را برای اعمال تابع پنجره‌ای تعیین می‌کند.
• partition_by_clause: (اختیاری) ردیف‌ها را به گروه‌های مجزا (پارتیشن) تقسیم می‌کند. تابع LEAD به طور مستقل در هر پارتیشن عمل می‌کند.
• order_by_clause: (اجباری) ترتیب ردیف‌ها را در هر پارتیشن یا کل مجموعه نتایج مشخص می‌کند. این بخش برای تعیین ردیف “بعدی” حیاتی است.

مثال ۱: استفاده از تابع LEAD بدون PARTITION BY

بیایید مثالی را بررسی کنیم که در آن از تابع LEAD بدون عبارت PARTITION BY استفاده می‌شود. هدف ما مشاهده میزان فروش برای ماه بعدی است.

SELECT
    SalesID,
    SalesPerson,
    SalesMonth,
    SalesAmount,
    LEAD(SalesAmount, 1, 0) OVER (ORDER BY SalesMonth) AS NextMonthSales
FROM dbo.Sales
ORDER BY SalesMonth;

در این مثال، تابع LEAD(SalesAmount, 1, 0) OVER (ORDER BY SalesMonth) میزان فروش (SalesAmount) را برای ردیف بعدی (با offset 1) بر اساس ترتیب ماه‌های فروش (ORDER BY SalesMonth) بازمی‌گرداند. اگر ردیف بعدی وجود نداشته باشد (مثلاً برای آخرین رکورد)، مقدار ۰ (default value) نمایش داده می‌شود. همانطور که مشاهده می‌کنید، این کوئری فروش کل را بدون در نظر گرفتن فروشنده در هر ماه مقایسه می‌کند.

مثال ۲: استفاده از تابع LEAD همراه با PARTITION BY

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

SELECT
    SalesID,
    SalesPerson,
    SalesMonth,
    SalesAmount,
    LEAD(SalesAmount, 1, 0) OVER (PARTITION BY SalesPerson ORDER BY SalesMonth) AS NextMonthSalesForPerson
FROM dbo.Sales
ORDER BY SalesPerson, SalesMonth;

در این کوئری، PARTITION BY SalesPerson باعث می‌شود که تابع LEAD به طور جداگانه برای هر SalesPerson (فروشنده) اعمال شود. این بدان معناست که NextMonthSalesForPerson برای هر فروشنده، فروش ماه بعدی همان فروشنده را نشان می‌دهد، نه فروش کل ماه بعدی در مجموعه داده. این رویکرد برای تحلیل عملکرد فردی بسیار دقیق‌تر است.

تابع LAG در SQL Server

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

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

در این ساختار:

• scalar_expression: ستونی است که می‌خواهید مقدار آن را از ردیف قبلی برگردانید.
• offset: تعداد ردیف‌هایی است که می‌خواهید به عقب بروید. اگر این مقدار مشخص نشود، پیش‌فرض آن ۱ است (یعنی ردیف بلافاصله قبلی).
• default: مقداری است که در صورتی که هیچ ردیف قبلی در موقعیت مشخص شده یافت نشود، برگردانده می‌شود. اگر این مقدار مشخص نشود، پیش‌فرض آن NULL است.
• OVER ( [ partition_by_clause ] order_by_clause ): این بخش نحوه پارتیشن‌بندی و مرتب‌سازی داده‌ها را برای اعمال تابع پنجره‌ای تعیین می‌کند.
• partition_by_clause: (اختیاری) ردیف‌ها را به گروه‌های مجزا (پارتیشن) تقسیم می‌کند. تابع LAG به طور مستقل در هر پارتیشن عمل می‌کند.
• order_by_clause: (اجباری) ترتیب ردیف‌ها را در هر پارتیشن یا کل مجموعه نتایج مشخص می‌کند. این بخش برای تعیین ردیف “قبلی” حیاتی است.

مثال ۳: استفاده از تابع LAG بدون PARTITION BY

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

SELECT
    SalesID,
    SalesPerson,
    SalesMonth,
    SalesAmount,
    LAG(SalesAmount, 1, 0) OVER (ORDER BY SalesMonth) AS PreviousMonthSales
FROM dbo.Sales
ORDER BY SalesMonth;

در این مثال، تابع LAG(SalesAmount, 1, 0) OVER (ORDER BY SalesMonth) میزان فروش (SalesAmount) را برای ردیف قبلی (با offset 1) بر اساس ترتیب ماه‌های فروش (ORDER BY SalesMonth) بازمی‌گرداند. اگر ردیف قبلی وجود نداشته باشد (مثلاً برای اولین رکورد)، مقدار ۰ (default value) نمایش داده می‌شود. این کوئری فروش کل را بدون در نظر گرفتن فروشنده در هر ماه مقایسه می‌کند.

مثال ۴: استفاده از تابع LAG همراه با PARTITION BY

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

SELECT
    SalesID,
    SalesPerson,
    SalesMonth,
    SalesAmount,
    LAG(SalesAmount, 1, 0) OVER (PARTITION BY SalesPerson ORDER BY SalesMonth) AS PreviousMonthSalesForPerson
FROM dbo.Sales
ORDER BY SalesPerson, SalesMonth;

در این کوئری، PARTITION BY SalesPerson اطمینان می‌دهد که تابع LAG به طور جداگانه برای هر SalesPerson (فروشنده) اعمال می‌شود. این بدان معناست که PreviousMonthSalesForPerson برای هر فروشنده، فروش ماه قبلی همان فروشنده را نشان می‌دهد، و نه فروش ماه قبلی در کل مجموعه داده. این رویکرد برای تحلیل روندهای عملکرد فردی بسیار دقیق است.

محاسبه تغییرات ماه به ماه با LEAD/LAG

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

SELECT
    SalesPerson,
    SalesMonth,
    SalesAmount,
    LAG(SalesAmount, 1, SalesAmount) OVER (PARTITION BY SalesPerson ORDER BY SalesMonth) AS PreviousMonthSales,
    SalesAmount - LAG(SalesAmount, 1, SalesAmount) OVER (PARTITION BY SalesPerson ORDER BY SalesMonth) AS MonthOverMonthChange,
    (SalesAmount - LAG(SalesAmount, 1, SalesAmount) OVER (PARTITION BY SalesPerson ORDER BY SalesMonth)) * 100.0 / LAG(SalesAmount, 1, SalesAmount) OVER (PARTITION BY SalesPerson ORDER BY SalesMonth) AS MonthOverMonthPercentageChange
FROM dbo.Sales
ORDER BY SalesPerson, SalesMonth;

در این کوئری پیشرفته برای تحلیل داده‌ها، ما چند ستون جدید اضافه کرده‌ایم:

• PreviousMonthSales: با استفاده از LAG، فروش ماه قبل برای هر فروشنده را محاسبه می‌کند.
• MonthOverMonthChange: تفاوت بین فروش ماه جاری و فروش ماه قبل را نشان می‌دهد. این ستون رشد یا کاهش مطلق را نمایش می‌دهد.
• MonthOverMonthPercentageChange: تغییرات را به صورت درصدی محاسبه می‌کند، که دیدگاه بهتری از میزان رشد یا افت نسبی ارائه می‌دهد. در اینجا برای اولین ماه هر فروشنده، مقدار SalesAmount به عنوان default value برای LAG در نظر گرفته شده تا از تقسیم بر صفر جلوگیری شود و تغییرات صفر درصد نمایش داده شود.

این نوع تحلیل با LEAD و LAG، برای متخصصین سئو و تحلیل‌گران داده، برای درک روندهای کلیدی عملکرد و شناسایی نقاط قوت و ضعف بسیار حیاتی است.