Window Function ها در SQL چه هستند؟

در زندگی روزمره، گاهی با رویدادها و مفاهیمی روبه‌رو می‌شویم که پس از آشنایی با آنها، انگار نگاه ما به جهان تغییر می‌کند و زندگی به دو بخش «قبل» و «بعد» تقسیم می‌شود. این تجربه نه تنها در جنبه‌های شخصی زندگی، بلکه در دنیای فنی و حرفه‌ای نیز اتفاق می‌افتد. در این حوزه، این نقاط عطف معمولاً ابزارها، کتابخانه‌ها و مفاهیمی هستند که پس از شناخت آنها، تازه درمی‌یابیم که چه امکانات و قابلیت‌های عظیمی را تا به حال نادیده گرفته بودیم. با آشنایی با چنین مفاهیمی، این حس در ما ایجاد می‌شود که چرا زودتر به سراغ این ابزار یا دانش نرفته‌ایم. یکی از این نقاط عطف در دنیای SQL، Window Function ها هستند. این توابع به شما این امکان را می‌دهند که عملیات پیچیده‌ای را که قبلاً به نظر زمان‌بر و دشوار می‌آمد، با سادگی و کارایی بیشتری انجام دهید. وقتی به درک عمیقی از قابلیت‌های Window Function ها دست یابید، شاید به این فکر بیفتید که چرا قبلاً با این ابزار قدرتمند آشنا نشده‌اید و چگونه کارهای خود را بدون استفاده از این ابزار انجام داده‌اید.

استفاده از توابع پنجره ای در SQL : کاربردها و مزایا

توابع پنجره‌ای (Window Functions) در SQL زمانی بسیار مفید هستند که نیاز به انجام محاسبات پیچیده‌ای دارید که به داده‌های بیشتری نسبت به یک ردیف واحد نیاز دارند، اما بدون گروه‌بندی داده‌ها به شکلی که کل نتایج را تغییر دهد. به عبارت دیگر، این توابع به شما اجازه می‌دهند محاسباتی را روی یک مجموعه از ردیف‌ها انجام دهید، در حالی که همچنان دسترسی به هر ردیف به صورت جداگانه حفظ می‌شود.

موارد اصلی که از توابع پنجره‌ای استفاده می‌کنیم شامل:

1. رتبه بندی ردیف ها : وقتی نیاز دارید ردیف‌ها را بر اساس یک یا چند ستون خاص در هر پارتیشن جدول رتبه‌بندی کنید، مثلاً رتبه‌بندی فروش‌ها بر اساس فروشنده در یک منطقه خاص.
2. برچسب گذاری مقادیر : اگر بخواهید مقادیر عددی را در هر پارتیشن بر اساس دسته‌های مشخص (مانند صدتایی‌ها) دسته‌بندی کنید، توابع پنجره‌ای این کار را به سادگی انجام می‌دهند.
3. شناسایی رویدادها : این توابع به شما امکان می‌دهند اولین، دومین یا آخرین رویداد را در یک پارتیشن خاص شناسایی کنید. برای مثال، پیدا کردن اولین خرید هر مشتری در یک فروشگاه.
4. محاسبات آماری : برای محاسبه مقادیری مانند میانگین، میانه، یا حتی جمع کل که در بین ردیف‌ها توزیع می‌شوند، توابع پنجره‌ای می‌توانند بدون تغییر ساختار داده‌ها این محاسبات را انجام دهند.

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

Partition By در SQL

در SQL، عبارت Partition By به شما امکان می‌دهد داده‌ها را به پارتیشن‌های کوچکتر تقسیم کنید. هر پارتیشن به مجموعه‌ای از ردیف‌ها اطلاق می‌شود که بر اساس ستون یا ستون‌های خاصی گروه‌بندی شده‌اند. این پارتیشن‌ها به شما اجازه می‌دهند محاسبات مختلفی را در داخل هر پارتیشن انجام دهید، بدون اینکه بر روی داده‌های خارج از آن پارتیشن تأثیر بگذارید.

به‌عنوان مثال، فرض کنید یک جدول Sales دارید که شامل ستون‌های userId، sessionId و Amount است. اگر می‌خواهید مجموع فروش‌ها را برای هر کاربر در هر جلسه محاسبه کنید، می‌توانید از عبارت Partition By برای گروه‌بندی ردیف‌ها بر اساس userId و sessionId استفاده کنید. به این ترتیب، محاسبات مجموع تنها در محدوده پارتیشن‌های تعیین‌شده انجام می‌شود و تأثیری بر سایر ردیف‌ها ندارد.

فرض کنید جدول Sales به این صورت است:

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

SELECT

userId, sessionId, Amount, SUM(Amount) OVER (PARTITION BY userId, sessionId) AS TotalSales

FROM Sales;

خروجی این کوئری به صورت زیر خواهد بود :

در این مثال ، Partition By ردیف‌ها را بر اساس userId و sessionId گروه بندی می‌کند، و تابع SUM محاسبه مجموع را برای هر پارتیشن انجام می‌دهد.

ORDER BY در Window Functions

در SQL، عبارت ORDER BY به شما این امکان را می‌دهد که ترتیب ردیف‌های موجود در یک پارتیشن را مشخص کنید. وقتی از این عبارت در ترکیب با توابع پنجره (Window Functions) استفاده می‌کنید، می‌توانید محاسبات خود را بر اساس ترتیب خاصی از ردیف‌ها انجام دهید. این ترتیب‌دهی می‌تواند بر روی نتایج تأثیر زیادی داشته باشد، به ویژه در مواردی مانند محاسبات تجمعی، رتبه‌بندی یا مرتب‌سازی داده‌ها.

فرض کنید می‌خواهید در هر پارتیشن، ردیف‌ها را بر اساس زمانی که رویدادها رخ داده‌اند مرتب کنید، تا بتوانید محاسباتی مانند شماره ردیف (Row Number)، رتبه‌بندی (Ranking) یا محاسبات تجمعی را انجام دهید. با استفاده از ORDER BY می‌توانید این ترتیب را تعیین کنید و SQL از این ترتیب‌دهی برای انجام محاسبات خود استفاده می‌کند.

فرض کنید جدولی به نام Events دارید که شامل ستون‌های userId، sessionId و datetimeCreated است. می‌خواهید شماره ردیف هر رویداد را بر اساس زمانی که رویدادها ایجاد شده‌اند، به دست آورید.

جدول فرضی Events:

برای اینکه ردیف‌ها را بر اساس datetimeCreated به صورت نزولی مرتب کنید و به هر ردیف یک شماره ترتیب اختصاص دهید، می‌توانید از کوئری زیر استفاده کنید :

SELECT

userId, sessionId, datetimeCreated, ROW_NUMBER() OVER (PARTITION BY userId, sessionId ORDER BY datetimeCreated DESC) AS RowNumber

FROM Events;

استفاده از توابع LEAD و LAG در SQL

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

تابع LAG :

تابع LAG به شما اجازه می‌دهد تا به داده‌های ردیف قبلی در یک پارتیشن خاص دسترسی پیدا کنید. این تابع به خصوص برای مقایسه تغییرات بین ردیف‌های متوالی مفید است.
فرض کنید جدولی دارید که شامل داده‌های فروش روزانه است و می‌خواهید تفاوت بین فروش امروز و فروش روز قبل را محاسبه کنید.

SELECT

SaleDate, SalesAmount, LAG(SalesAmount, 1) OVER (ORDER BY SaleDate) AS PreviousDaySales,
SalesAmount – LAG(SalesAmount, 1) OVER (ORDER BY SaleDate) AS SalesDifference

FROM DailySales;

در این مثال، تابع LAG(SalesAmount, 1) فروش روز قبل را برمی‌گرداند و اختلاف فروش بین روزهای متوالی محاسبه می‌شود.

تابع LEAD :

تابع LEAD به شما امکان می‌دهد که به داده‌های ردیف بعدی نسبت به ردیف فعلی دسترسی داشته باشید. این تابع برای مقایسه مقادیر فعلی با مقادیر آتی مفید است.

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

SELECT CustomerID, OrderDate,

LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate

FROM Orders;

در این مثال ، تابع LEAD(OrderDate, 1) تاریخ سفارش بعدی کاربر را برمی‌گرداند.

Rolling Window در SQL

در SQL، تابع Rolling Window یکی از قدرتمندترین ابزارها برای تجزیه‌وتحلیل داده‌ها در بازه‌های زمانی پیوسته یا سایر معیارهای متوالی است. این تابع به شما این امکان را می‌دهد که محاسباتی مانند جمع، میانگین، و شمارش را بر روی یک مجموعه از ردیف‌ها در یک محدوده متحرک (Rolling Window) انجام دهید. این عملیات بدون استفاده از PARTITION BY می‌تواند بر روی کل مجموعه داده‌ها اعمال شود و مقادیر مرتبط با ردیف‌های قبل یا بعد را با هم ترکیب کند.

یک مثال کلاسیک از Rolling Window، محاسبه تعداد خریدهای انجام‌شده در ۵ رویداد آخر (به استثنای رویداد فعلی) است. فرض کنید که می‌خواهید از توابع پنجره برای شمارش تعداد رویدادهای خرید یک کاربر خاص در پنج خرید قبلی استفاده کنید. در این حالت، می‌توانید از تابع ROW_NUMBER() در ترکیب با توابع دیگر برای تعریف یک پنجره متحرک استفاده کنید. فرض کنید یک جدول به نام UserPurchases دارید که شامل ستون‌های UserId, PurchaseDate, و PurchaseAmount است. هدف شما این است که تعداد خریدهای انجام‌شده در ۵ رویداد قبلی (به استثنای رویداد فعلی) را پیدا کنید.

SELECT

UserId, PurchaseDate, PurchaseAmount,

COUNT(*) OVER (ORDER BY PurchaseDate ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS PurchasesInLast5

FROM UserPurchases;

در این کوئری :

• ORDER BY PurchaseDate: ردیف‌ها را بر اساس تاریخ خرید مرتب می‌کند.
• ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING: یک پنجره متحرک را تعیین می‌کند که شامل ۵ ردیف قبل از ردیف فعلی است، به استثنای ردیف فعلی.

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

سخن پایانی

هر بار که با یک کوئری پیچیده مواجه می‌شوید که شامل گروه‌بندی، مرتب‌سازی، یا محاسبه معیارها بر اساس مقادیر سایر ردیف‌ها است، به توابع پنجره (Window Functions) به عنوان یک راه‌حل ساده و مؤثر فکر کنید. این توابع، ابزاری قدرتمند برای تجزیه و تحلیل داده‌ها هستند که می‌توانند به شما در حل مشکلات پیچیده کمک کنند و در عین حال کارایی بالاتری نسبت به روش‌های سنتی ارائه دهند. امیدواریم این مقاله به شما کمک کرده باشد تا با توابع پنجره بیشتر آشنا شوید و به‌درستی از آن‌ها در موقعیت‌های مختلف بهره ببرید.