Rows و Range در WINDOW FUNCTION - مقدمه

یکی از امکاناتی که WINDOW FUNCTION در طراحی کوئری برای ما فراهم می کند، دسترسی به جزئیات رکوردها پس از گروهبندی آن هاست به نحوی که به جای نوشتن دستور Group By در انتهای کوئری، کافی است عبارت Partition By را در قسمت ()OVER از یک WINDOW FUNCTION بنویسیم به منظور مطالعه بیشتر در این رابطه می توانید آموزش WINDOW FUNCTION و پایان محدودیت های GROUP BY را مطالعه کنید. اما از sql server 2012 امکان جدیدی به نام فریم بندی رکوردها به WINDOW FUNCTION اضافه شد. در واقع با استفاده از این امکان، می توانیم رکوردها را پس از قرار دادن در گروه های مختلف، مجدداً گروهبندی کنیم و به عبارتی دیگر یک گروهبندی ثانویه در داخل گروه ها داشته باشیم. 

در آموزش Rows و Range در WINDOW FUNCTION پس از آشنایی با مفهوم فریم بندی، مثال هایی عملی از کاربرد آن را با هم می بینیم.

---

Rows و Range در WINDOW FUNCTION - معرفی

فرمت کلی یک WINDOW FUNCTION به صورت زیر است:

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

به غیر از frame_clause که موضوع این آموزش است با مابقی قسمت ها از قبل(در آموزش معرفی window function) آشنا شده ایم. در واقع Rows و Range در WINDOW FUNCTION تعریف کننده فریم هستند. به عبارتی دیگر پس از گروهبندی رکوردها بر اساس فیلد موجود در عبارت Partition By می توانیم با استفاده از Rows و Range محدوده هایی را در داخل گروه ها تعریف کنیم و سپس تابع WINDOW FUNCTION را بصورت مستقل بر روی هر کدام از فریم ها یا محدوده های ایجاد شده اعمال کنیم.

نکته: Ranking WINDOW FUNCTIONها دارای Frame نیستند . به عبارتی دیگر نمی توان برای آن ها از Range و یا Rows استفاده کرد.

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

     PRECEDING: مشخص کردن تعداد رکوردهای قبل از رکورد جاری
     FOLLOWING: مشخص کردن تعداد رکوردهای بعد از رکورد جاری
     UNBOUNDED : شامل همه رکوردهای قبل و یا بعد از رکورد جاری

نکته: در هر WINDOW FUNCTION که از فریم بندی پشتیبانی می کند فریم پیش فرض RANGE UNBOUNDED PRECEDING و یا RANGE Between UNBOUNDED PRECEDING And Current Row است.

با توجه به نکته بالا در صورت عدم وجود Rows و Range در توابع WINDOW FUNCTION غیر از توابع رتبه بندی(از frame پشتیبانی نمی کنند) فریم پیش فرض برای هر گروه، شامل رکورد جاری و رکوردهای قبل از آن می باشد. به عبارتی تابع WINDOW FUNCTION بر روی مجموعه رکوردها از ابتدا تا رکورد جاری اعمال می شود که معادل با RANGE Between UNBOUNDED PRECEDING And Current Row است.

جهت ارائه مثال هایی در زمینه آموزش و کاربرد Rows و Range در WINDOW FUNCTION از جدول و داده های تستی زیر استفاده می کنیم:

--ایجاد جدول
CREATE TABLE OrderItem (
      OrderID smallint identity, 
      Supplier nvarchar(50),
      ProductType nvarchar(20), 
      OrderAmt money, 
      OrderDate char(10));

--درج رکوردهای تستی در جدول
INSERT INTO OrderItem VALUES
    (N'رضایی''s',N'سخت افزار',2121.09,'1396/01/12'),(N'کریمی',N'الکتریکی',12347.87,'1396/01/18'),
      (N'نادری',N'سخت افزار',999.99,'1396/01/22'),(N'محمدی',N'سخت افزار',6532.09,'1396/01/31'),
      (N'کشوری',N'ظروف آشپزخانه',3421.10,'1396/02/03'),(N'کشوری',N'ظروف آشپزخانه',1290.90,'1396/02/07'),
      (N'سلطانی',N'لبنی',12987.01,'1396/02/10'),(N'ربیعی''s',N'لوازم التحریر',43235.67,'1396/02/21'),
      (N'احسنی',N'ورزشی',89320.19,'1396/03/10'),(N'ترابی',N'ورزشی',53821.19,'1396/03/12'),
      (N'حسابی',N'لوازم التحریر',3245.59,'1396/03/14'),(N'مهری',N'بهداشتی',3331.59,'1396/03/19'),
      (N'تصدیق''s',N'پوشاک',2321.01,'1396/03/21'),(N'بندامیر',N'الکتریکی',3456.01,'1396/04/03'),
      (N'بندامیر',N'الکتریکی',1253.87,'1396/04/21'),(N'کشوری',N'ظروف آشپزخانه',3332.89,'1396/04/20'),
      (N'کیقبادی',N'منسوجات',345.11,'1396/04/22'),(N'هاشمی',N'سخت افزار',2347.09,'1396/05/03'),
      (N'ربیعی',N'لوازم التحریر',1243.78,'1396/05/08'),(N'احسنی',N'ورزشی',89876.90,'1396/05/10'),
      (N'پوری',N'لبنی',1231.22,'1396/05/10'),(N'تصدیقs''s',N'پوشاک',3246.98,'1396/05/12'),
      (N'عطار',N'بهداشتی',2325.98,'1396/05/14'),(N'مهری',N'بهداشتی',8786.99,'1396/05/21'),
      (N'نادری',N'سخت افزار',12341.09,'1396/05/22');

 به عنوان مثال برای محاسبه Running Total(جمع مقدار عددی یک ستون با مجموع مقادیر قبل از آن) کوئری زیر را طراحی می کنیم:

همانطور که در مثال بالا مشخص است، در حالتی که Rows و Range در WINDOW FUNCTION مشخص نشده باشد، به صورت پیش فرض از فریم RANGE UNBOUNDED PRECEDING استفاده می شود. به این معنا که در زمان اعمال تابع SUM به ازای هر رکورد، مجموع رکوردهای قبلی به علاوه رکورد جاری محاسبه می شود. فریم های دیگری که می توانیم برای Rows و Range داشته باشیم عبارتند از:

UNBOUNDED PRECEDING: شروع فریم از اولین رکورد موجود در گروه/پارتیشن

unsigned value specification> PRECEDING>: شروع فریم از تعداد رکوردهای قبل از رکورد جاری. غیر قابل استفاده برای RANGE

<BETWEEN <window frame bound > AND <window frame bound: مشخص کردن شروع و پایان یک فریم در گروه/پارتیشن

UNBOUNDED FOLLOWING: مشخص کردن آخرین رکورد موجود در گروه/پارتیشن به عنوان نقطه پایانی

unsigned value specification> FOLLOWING>: پایان فریم در تعداد رکورد مشخص شده بعد از رکورد جاری. غیر قابل استفاده برای RANGE

unsigned integer literal: یک مقدار Integer مثبت است که تعداد رکوردهای قبل و یا بعد از رکورد جاری را مشخص می کند و تنها برای ROWS قابل استفاده است.

خلاصه ای از محدوده های(Delimiters) قابل استفاده در ایجاد فریم داخل پارتیشن/گروه:

نکته: اگر unsigned value specification> FOLLOWING> به عنوان نقطه شروع مشخص شود، باید حتماً نقطه پایانی هم با unsigned value specification> FOLLOWING> مشخص شود.

چند مثال از فریم بندی:

ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING: فریم با شروع از دومین رکورد بعد از رکورد جاری و خاتمه با دهمین رکورد بعد از رکورد جاری. غیر قابل استفاده برای RANGE

RANGE/ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING: یک فریم شامل رکورد جاری تا ابتدای گروه/پارتیشن

RANGE/ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: فریم شامل رکورد جاری تا آخرین رکورد گروه/پارتیشن

نکته: در صورتی که در تعیین فریم، از preceding به تنهایی همراه با ROWS/RANGE استفاده کنیم. در واقع از preceding(با هر تعداد رکورد) به عنوان نقطه شروع Frame استفاده می شود و رکورد جاری(CURRENT ROW) تعیین کننده نقطه پایانی فریم است:

     “ROWS 5 PRECEDING” برابر است با “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”

 کوئری زیر مجموع فروش هر رکورد با دو رکورد قبل از آن(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) را نمایش می دهد. به عبارت دیگر هر فریم حداکثر حاوی سه رکورد است:

توجه داشته باشید اگر در کوئری بالا جهت فریم بندی به جای ROWS از RANGE استفاده می کردیم با خطای زیر مواجه می شدیم:

          "RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters"

 به عنوان یک مثال عملی که در محاسبه آن از Aggregate Window Function به همراه فریم بندی استفاده می شود می توان میانگین متحرک(moving average) را نام برد. moving average کاربرد زیادی در تحلیل تکنیکال دارد و به عنوان مثال در نرم افزار Meta Trader جزو اندیکاتورهای پر استفاده محسوب می شود. وقتي میخواھیم moving average را محاسبه كنیم بايد بازه زماني(تعداد رکورد موجود در هر فریم) را مشخص كنیم که در اینصورت میانگین متحرک برابر است با مجموع مقادیر فیلد مورد نظر در تعداد رکورد مشخص شده، تقسیم بر تعداد آن ها(simple moving average)

کوئری زیر میانگین متحرک فروش سه ماهه را محاسبه می کند: 

مراحل اجرای کوئری بالا به این صورت است که در ابتدا رکوردهای موجود در جدول OrderItem بر اساس ماهی که فروش در آن ها انجام شده است گروهبندی می شوند(دستور Group By) سپس تابع SUM به ازی فیلد OrderAmt در هر کدام از گرو ه ها محاسبه می شود. دقت کنید در اینجا تابع SUM به عنوان پارامتر تابع AVG، یک تابع تجمعی(Aggregate Function) است. در ادامه رکوردها بر اساس ماه فروش مرتب می شوند(دستور Order By داخل عبارت OVER) و تابع AVG به ازای هر ماه(رکورد)، میانگین مجموع فروش آن ماه(رکورد) را به همراه مجموع فروش دو ماه(رکورد) قبلی محاسبه می کند(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

همانطور که اشاره شد، ایجاد فریم می تواند در داخل پارتیشن ها انجام شد. به عبارتی دیگر می توانیم بعد از گروهبندی رکوردها، در داخل هر گروه نیز فریم تعریف کنیم. شکل زیر یک مثال از فریم بندی در داخل پارتیشن/گروه را  نمایش می دهد:

(OVER(Partition By ID ORDER BY Value ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

---

Rows و Range در WINDOW FUNCTION - مقایسه

در طول آموزش با برخی از تفاوت هایی که بین Range و Rows در تعریف فریم وجود دارد آشنا شدیم به عنوان مثال اینکه در Range نمی توانیم از مقادیر عددی برای تعیین محدوده(Delimiter) استفاده کنیم و تنها مجاز به استفاده از Unbounded هستیم. محدوده قابل استفاده در Range  و Rows در شکل زیر آورده شده است:

اما یک تفاوت عمده بین Range و Rows زمانی رخ می دهد که در مجموعه رکوردها، مقدار تکراری وجود داشته باشد. می دانیم که اگر به عنوان مثال نقطه شروع فریم UNBOUNDED PRECEDING باشد. این فریم شامل رکورد جاری و مجموعه رکوردهای قبل از آن خواهد بود. اما اگر فریم UNBOUNDED PRECEDING را با Range تعریف کرده باشیم، آنگاه در صورت وجود مقادیر تکراری بعد از رکورد جاری، آن مقادیر نیز جزو فریم محسوب می شوند. همینطور اگر نقطه پایان فریم UNBOUNDED FOLLOWING باشد. این فریم شامل رکورد جاری و مجموعه رکوردهای بعد از آن خواهد بود. اما اگر فریم UNBOUNDED FOLLOWING را با Range تعریف کرده باشیم، آنگاه در صورت وجود مقادیر تکراری قبل از رکورد جاری، آن مقادیر نیز جزو فریم محسوب می شوند. این مورد در شکل زیر نمایش داده شده است:

به منظور درک بهتر تفاوت Range و Row در تعریف فریم، مثال بالا را به صورت عملی در sql server پیاده سازی می کنیم. جدول tbl_Employees را با رکوردهای تستی و مقادیر شبیه به شکل بالا ایجاد می کنیم:

--Employees ایجاد جدول   
CREATE TABLE [dbo].[tbl_Employees](
	[NatinalCode] [char](10),
	[Name] [nvarchar](50) ,
	[Family] [nvarchar](50) ,
	[JobGroup] [char](1),
	[Salary] [numeric](3,1) 
) 
GO
delete from [tbl_Employees]
--درج مقادیر تستی در جدول
insert into [tbl_Employees] Values('1199800001',N'محسن',N'بندامیر','A',8),('1199800002',N'حسن',N'مرادی','A',7)
,('1199800003',N'علی',N'کشوری','A' ,5 ),('1199800004',N'رضا',N'رحیمی', 'B',5 ),('1199800005',N'مژگان',N'جمشیدی', 'B',5)
,('1199800006',N'نسرین',N'هاشمی', 'B', 5),('1199800007',N'حسین',N'محمدی','C' ,5 ),('1199800008',N'رضا',N'کیانی', 'C',4 ),
('1199800009',N'فاطمه',N'رضایی', 'C', 3)

برای شبیه سازی فریم های موجود در تصویر، از Aggregate Window Function استفاده می کنیم:

تحلیل و بررسی خروجی بالا، به عنوان تمرین بر عهده شما می باشد. 

• قبلی
• بعدی