WINDOW FUNCTION چیست - مقدمه

جهت معرفی window function ترجیح میدهم با اشاره ای بر محدودیت های آشنا و خسته کننده در دستور Group by شروع کنم. حتماً در زمان گروهبندی رکوردها و استفاده از یک تابع تجمعی مانند sum یا avg با دو محدودیت روبه رو شده اید. اول اینکه الزاماً بایستی همه فیلدهای موجود در دستور select کوئری، در دستور group by نیز وجود داشته باشند و دومین محدودیت نیز عدم دسترسی به جزئیات(فیلدهایی غیر از فیلد گروهبندی) بود. به نحوی که مثلاً بعد از محاسبه معدل دانشجویان، برای استخراج نام، فامیل و ... نیاز به طراحی یک subquery و یا مراحل اضافه تری داشتیم. اما با معرفی window function، علاوه بر رفع این محدودیت ها، طراحی کوئری هایی جهت محاسبه moving average یا runing total، حرکت بین رکودهای قبلی یا بعدی، انجام محاسبات آماری، رتبه بندی و ... با کمترین تعداد دستور و با سرعت بهینه قابل انجام است. 

در SQL SERVER همواره آشنایی با دستورات جدید و استفاده از آن ها علاوه بر رفع محدودیت های گذشته، منجر به افزایش سرعت و کارایی می شود. یکی از مجموعه دستورات پرکاربرد SQL SERVER که از نسخه 2005 معرفی و به تدریج در نسخه های بعدی تکمیل شد window function ها هستند. window function یک مجموعه توابع قدرتمندی است که راه حلی سریع، بهینه و با حداقل تعداد دستورات برای حل مسائل پیچیده ای ارائه می کند که قبلاً مجبور بودیم با استفاده از کرسر، subquery، دستورات پیچیده و بعضاً غیر بهبنه T-SQL آن ها را حل کنیم. در واقع یک window function تابعی است که بر روی یک مجموعه رکورد که تحت عنوان window شناخته می شود محاسباتی را انجام داده و نتیجه محاسبه را به ازای تک تک رکوردهای آن مجموعه نمایش می دهد. در نتیجه window function همواره بر روی مجموعه ای از رکوردها(Set) کار می کند و می دانیم که Sql Server در کار با مجموعه رکوردها از کارایی بالایی برخوردار است.

سایر DBMS ها مانند Oracle ,Sybase ,DB2 و PostgreSQL نیز از window function ها پشتیانی می کنند. در آموزش window function چیست ابتدا مقایسه ای خواهیم داشت بین group by و window function سپس تاریخچه و سیر تکاملی window function را در نسخه های مختلف sql server بررسی میکنیم. در پایان آموزش پس از معرفی انواع window function، اجزاء مختلف یک دستور در window function و نحوه استفاده از آن ها را معرفی خواهیم کرد.

---

WINDOW FUNCTION چیست - مقایسه با Group By

 یک محدودیت بزرگ در استفاده از group by این است که پس از گروهبندی، توابع تجمعی(aggregate function) بر روی گروه ها اعمال می شوند و نهایتاً نتیجه خروجی اجرای کوئری نیز به صورت تجمعی و یا گروهبندی شده می باشد به عبارتی دیگر، در این نوع کوئری ها پس از گروهبندی، نمی توانیم به جزئیات دسترسی داشته باشیم. برای بهتر مشخص شدن این موضوع از مثال زیر استفاده می کنیم:

--Employees ایجاد جدول   
CREATE TABLE [dbo].[Employees](
	[NatinalCode] [char](10),
	[Name] [nvarchar](50) ,
	[Family] [nvarchar](50) ,
	[JobGroup] [char](1),
	[Salary] [numeric](3,1) 
) 
GO
--درج مقادیر تستی در جدول
insert into [Employees] Values('1199800001',N'محسن',N'بندامیر','A',8),('1199800002',N'غلامرضا',N'محمدی','A',7.5)
,('1199800003',N'علیرضا',N'خورسندی','A' ,7.8 ),('1199800004',N'حسین',N'نوربخش', 'B',6 ),('1199800005',N'آرش',N'کمالی', 'B', 6.5)
,('1199800006',N'نرگس',N'سلیمانی', 'B', 6.8),('1199800007',N'شیوا',N'نادری','C' ,4.8 ),('1199800008',N'نجمه',N'قاسمی', 'C',4.6 ),
('1199800009',N'محمد',N'میرزاده', 'C', 4.3),('1199800010',N'ناصر',N'ندیمی','C',4 )

جهت استخراج مجموع حقوق هر گروه کاری در جدول Employees از کوئری زیر استفاده می کنیم:

--استخراج مجموع حقوق هر گروه کاری
SELECT JobGroup, SUM(Salary) total
  FROM Employees
GROUP BY JobGroup

مراحل اجرای کوئری در شکل زیر قابل مشاهده است:

همانطور که در شکل بالا مشاهده می کنید و با توجه به آنچه در آموزش ترتیب اجرای دستورات کوئری در SQL SERVER اشاره شد، در ابتدا رکوردها از جدول Employees استخراج شده و سپس بر اساس فیلد JobGroup گروهبندی می شوند که در شکل بالا این گروه ها با رنگ های آبی و سبز و قرمز مشخص شده اند. نکته قابل توجه در شکل بالا این است که بعد از گروهبندی رکوردها و اعمال تابع sum بر روی فیلد salary در هر گروه، دیگر به جزئیات رکوردها مانند نام، کد ملی و ... به ازای هرکارمند دسترسی نداریم. اگر در دستور select فیلدهایی مانند نام و فامیل کاربر را اضافه کنیم با خطای زیر مواجه خواهیم شد که بر الزام وجود فیلدهای دستور select در group by اشاره دارد:

اما در صورت استفاده از window function جهت استخراج مجموع و حداکثر حقوق هر گروه کاری، می توانیم همزمان با گروهبندی بر اساس یک فیلد خاص و اعمال توابع MAX و SUM، به جزئیات هر رکورد نیز دسترسی داشته باشیم(قسمت های مختلف window function در ادامه توضیح داده می شود):

مراحل اجرای اسکریپت بالا در شکل زیر نمایش داده شده است:

پس مشاهده می کنیم که در صورت استفاده از window function به جزئیات موجود در هر رکورد دسترسی داریم و می توانیم آن ها را در خروجی داشته باشیم.

---

WINDOW FUNCTION چیست - تاریخچه

 قبل از پرداختن به تاریخچه window function از زمان معرفی آن تا تغییرات و نحوه تکمیل توابع در نسخه های مختلف sql server لازم است تا با انواع window function آشنایی مختصری پیدا کنیم. می توانیم window function ها را در سه دسته کلی توابع تجمعی، رتبه بندی و تحلیلی به شکل زیر قرار دهیم (در مورد هر یک از توابع زیر، در مجموعه کوئری نویسی آموزش هایی جداگانه ارائه می کنیم):

 window function در sql server 2005 معرفی شد و تنها شامل Aggregate window function و Ranking window function می شد تا نسخه sql server 2012 تغییر و یا بهبود خاصی در window function ایجاد نشد. می توان گفت که sql server 2012 به عنوان یک نقطه عطف در این زمینه محسوب می شود و توابع کاربردی زیادی به window function اضافه شد و اما در sql server 2016 با اضافه شدن یک عملگر جدید با نام Window Aggregate Operator و ایجاد قابلیت Batch Processing در اجرای این عملگر، بهبود کارایی چشمگیری در اجرای اکثر توابع window function ایجاد شد که در یک آموزش جداگانه این مورد را بررسی خواهیم کرد. در شکل زیر سیر تکاملی توابع مختلف window function در نسخه های sql server را مشاهده می کنید:

---

 WINDOW FUNCTION چیست - معرفی اجزاء 

 یکی از مهمترین قسمت های موجود در هر window function عبارت ()OVER است. همانطور که در ابتدای آموزش اشاره کردیم، یک window function تابعی است که بر روی مجموعه ای از رکوردها که در قالب window شناخته می شوند اعمال می شود. این مجموعه رکوردها بوسیله عبارت ()OVER تعیین می شوند. به عنوان مثال در کوئری زیر:

عبارت ()OVER در توابع MAX و SUM تعیین کننده مجموعه رکوردهایی(window) است که باید این دو تابع بر روی آن ها اعمال شود. توجه داشته باشید که در صورت ننوشتن هیچ عبارتی در مقابل ()OVER کلیه رکوردهای جدول جهت محاسبه تابع در نظر گرفته می شوند. همانطور که در خروجی کوئری بالا مشخص است، جهت محاسبه میانگین و مجموع حقوق کارمندان، این توابع بر روی فیلد salary از همه رکوردهای جدول اعمال شده اند. اما عبارت ()OVER دارای دو قابلیت می باشد:

     1. پارتیشن بندی رکوردها(مانند آنچه در Group By داشتیم) که در این حالت از عبارت PARTITION BY در ()OVER استفاده می کنیم.

     2. مرتب سازی رکوردها در هر پارتیشن ایجاد شده، که در این حالت از عبارت ORDER BY در ()OVER استفاده می کنیم.

در کوئری بالا که مثالی از کاربرد Aggregate window function است در عبارت ()OVER گفته ایم که ابتدا رکوردها را بر اساس JobGroup گروهبندی کن(PARTITION BY JobGroup) و سپس رکوردهای موجود در هر گروه را به ترتیب Family مرتب سازی کن(ORDER BY Family) و نهایتاً تابع SUM را به ازای فیلد salary در هر گروه یا پارتیشن محاسبه کن(SUM salaray). توجه داشته باشید که هر کدام از توابع می توانند دارای پارتیشن بندی و یا مرتب سازی متفاوتی باشند.

نکته: دستور ORDER BY در عبارت ()OVER، تنها جهت مرتب سازی رکوردهای متعلق به پارتیشن استفاده می شود و با دستور ORDER BY انتهای کوئری متفاوت است.

پس syntax یک window function به صورت زیر است:

 window_function (expression) OVER (
   [ PARTITION BY expr_list ]
   [ ORDER BY order_list ][ frame_clause ] ) 

در رابطه با عبارت [ frame clause] در دستور بالا تنها به این توضیح مختصر بسنده می کنیم، که پس از گروهبندی رکوردها، می توانیم یک نوع گروهبندی ثانویه نیز داخل هر گروه/پارتیشن داشته باشیم که به این گروه های کوچک داخل هر پارتیشن، فریم گفته می شود. جهت تعیین محدوه هر فریم در داخل پارتیشن نیز از RANGE و یا ROW استفاده می کنیم.

---

  WINDOW FUNCTION چیست - جمع بندی

اگر تا به حال برای دستیابی به اطلاعات تکمیلی رکوردها بعد از گروهبندی، طراحی اسکریپت هایی جهت استخراج moving average یا runing total، حرکت بین رکودهای قبلی یا بعدی، انجام محاسبات آماری، رتبه بندی و ... از کوئری های تو در تو و یا دستورات پیچیده T-SQ استفاده می کرده اید به شما توصیه می کنم که حتماً این دستورات را با window function جایگزین کنید. به این دلیل که اولاً توابع window function بر روی مجموعه ای از رکوردها اعمال می شوند و همواره به این نکته اشاره کرده ایم که sql server در کار با مجموعه رکوردها از کارایی بالایی برخوردار است و ثانیاً جهت افزایش سرعت و کارایی توابع window function امکاناتی مانند Aggregate Pushdown و یا Window Aggregate Operator با قابلیت Batch Processing ایجاد شده اند که از بازدهی قابل توجهی نسبت به روش های مشابه قبلی در اجرای کوئری ها برخوردار هستند.

در آموزش window function چیست سعی شد تا یک آشنایی مقدماتی با window function، مزیت استفاده از آن، مروری بر تغییرات آن در نسخه های مختلف sql server ایجاد کنیم. هر چند در طول آموزش نیز با یک نوع از window function به نام Aggregate window function آشنایی مختصری پیدا کردیم. با توجه به زیاد بودن تعداد توابع window function  و وسعت زمینه کاربرد آن ها، سعی می کنیم در آموزش هایی جداگانه به همراه مثال هایی عملی از کاربرد این توابع بپردازیم. دو مورد مهمی که در زمان استفاده از  window function باید مورد توجه قرار دهیم spooling و Batch Processing است که نیازمند ملاحظاتی در طراحی جدول و کوئری می باشد که به ابن دو مورد نیز در مبحث ارزیابی کارایی window function می پردازیم.

  

• قبلی