WINDOW FUNCTION و توابع تحلیلی - قسمت دوم

در اولین قسمت از آموزش WINDOW FUNCTION و توابع تحلیلی به معرفی Window offset functions پرداختیم و کاربردهایی از این توابع را دیدیم. در این آموزش با window distribution functions آشنا می شویم. این توابع دارای مفاهیم آماری هستند و از آنجایی که هدف ما بیشتر یادگیری نحوه استفاده و کاربرد عملی از این توابع است، از پرداختن به تعاریف و مفاهیم آماری اجتناب کرده و تنها از دید کوئری نویسی و استفاده از این توابع در تهیه گزارشات، مثال هایی ارائه می کنیم. در ابتدا نحوه عملکرد هر تابع را توضیح می دهیم و در ادامه با ارائه یک مثال، خروجی تولید شده توسط تابع نمایش داده می شود و سپس فرمول محاسبه هر تابع عنوان خواهد شد.

در واقع در sql server 2012 دو جفت از توابع توزیعی معرفی شدند. دو تای اول PERCENTILE_CONT و PERCENTILE_DISC هستند که به عنوان inverse distribution functions شناخته می شوند و دو تای بعدی PERCENT_RANK و CUME_DIST است که جزء rank distribution functions محسوب می شوند.

---

 WINDOW FUNCTION و توابع تحلیلی - قسمت دوم

1. WINDOW FUNCTION و توابع تحلیلی - rank distribution functions

در ابتدا با توابع rank distribution functions یعنی PERCENT_RANK و CUME_DIST شروع می کنیم. به منظور طراحی اسکریپت با این توابع از جدول Employees با داده های فرضی به صورت زیر استفاده می کنیم:

--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 )

 

1-1. WINDOW FUNCTION و توابع تحلیلی - PERCENT_RANK

نمایش درصدی از مقادیر، که از مقدار جاری کوچکتر هستند. به عبارتی دیگر این تابع، جایگاه نسبی(relative standing) یک مقدار را در بین مقادیر خروجی نمایش می دهد. فرمت کلی این تابع به صورت زیر است:

PERCENT_RANK OVER ( [ partition_by_clause ] order_by_clause ) 

همانطور که مشخص است در این تابع، نمی توانیم فریم بندی(window frame) داشته باشیم. محاسبه جایگاه نسبی(relative standing) حقوق پرسنل در جدول Employees:

نکته: PERCEN_RANK به ازای بزرگترین مقدار، همواره برابر با یک و به ازای کوچکترین مقدار برابر با صفر است.

با توجه به نکته بالا می توانیم اینگونه بگوییم که همه کارمندان(%100)، حقوقی کمتر از 8.0 دریافت می کنند و هیچ کارمندی(%0) نیز حقوق دریافتی کمتر از 4.0 ندارد.

به منظور طراحی یک فرمول در محاسبه PERCEN_RANK باید از تابع ()RANK استفاده کنیم(جهت مطالعه در مورد تابع RANK این لینک را مشاهده کنید):

PERCENT_RANK() = (RANK() - 1) / (Total no of Rows - 1)

با توجه به فرمول بالا برای محاسبه جایگاه نسبی(relative standing) هر مقدار در خروجی، بایستی یک واحد از RANK متناظر با آن مقدار کسر کرده و سپس نتیجه را بر تعداد کل رکوردها منهای یک، تقسیم کنیم:

 

همانطور که در شکل بالا مشخص شده است، در ردیف دوم برای محاسبه جایگاه نسبی(relative standing) حقوق با مقدار 7.8 در بین مقادیر خروجی با استفاده از فرمول، نتیجه تقسیم 8 بر 9 یعنی عدد ۰/۸۸۸۸۸۸۸۸۸۸۸۸۸۸۸۹ نمایش داده شده است.

 1-2. WINDOW FUNCTION و توابع تحلیلی - CUME_DIST

نمایش درصدی از مقادیر، که کوچکتر یا مساوی با مقدار جاری هستند. به عبارتی دیگر این تابع، توزیع تجمعی(cumulative distribution) یک مقدار را در بین مقادیر خروجی نمایش می دهد. فرمت کلی این تابع به صورت زیر است:

CUME_DIST( [ partition_by_clause ] order_by_clause ) 

در این تابع نیز مانند percent_rank، نمی توانیم فریم بندی(window frame) داشته باشیم. محاسبه توزیع تجمعی(cumulative distribution) حقوق پرسنل در جدول Employees:

برای محاسبه توزیع تجمعی(cumulative distribution) هر مقدار موجود در فیلد Salary، تعداد حقوق های کمتر یا مساوی با مقدار جاری را بر تعداد کل رکوردهای جدول تقسیم می کنیم. به عنوان مثال برای محاسبه توزیع تجمعی، حقوق 7.8(ردیف دوم) از آنجایی که تعداد حقوق های کمتر یا مساوی با 7.8 برابر با 9 است و تعداد کل رکوردهای خروجی نیز برابر با 10 می باشد، در نتیجه مقدار توزیع تجمعی برای حقوق 7.8 برابر با 0.9 است.

نکته: همواره مقدار محاسبه شده توسط تابع CUME_DIST عددی بزرگتر از 0 و  کوچکتر از 1 است و مقادیر مشابه نیز دارای توزیع تجمعی یکسانی هستند.

2. WINDOW FUNCTION و توابع تحلیلی - inverse distribution functions

توابع rank distribution functions یعنی PERCENT_RANK و CUME_DIST، با دریافت یک عدد، درصد رتبه آن عدد را در بین مجموعه ای از اعداد با دو روش متفاوت محاسبه می کردند. در ادامه با inverse distribution functions آشنا می شویم که دقیقاً دارای عملکرد معکوس با این توابع هستند. به عبارتی با استفاده از توابع inverse distribution functions می توانیم مشخص کنیم که چه عددی دارای درصد رتبه خاصی است. به عنوان مثال در یک مجموعه، می توانیم عددی را پیدا کنیم که نیمی از اعداد از آن بزرگتر و نیمی دیگر از آن کوچکتر هستند. 

یکی از کاربردهای رایج در استفاده از inverse distribution functions پیدا کردن میانه(Median) در بین مجموعه ای از اعداد است. به عنوان مثال یکی از مراحل مورد نیاز در الگوریتم مرتب سازی سریع(Quick Sort) پیدا کردن pivot و یا میانه در هر مرحله محسوب می شود. به عبارتی میانه(median) عددی است که نیمی از اطلاعات آماری(داده ها) کوچکتر از آن و نیمی از اطلاعات آماری (داده ها) بزرگتر از آن هستند. در ادامه با دو تابع  inverse distribution functions یعنی PERCENTILE_CONT و PERCENTILE_DISC آشنا می شویم.

 2-1. WINDOW FUNCTION و توابع تحلیلی - PERCENTILE_DISC

فرمت این تابع کمی متفاوت با آنچه است که تا به حال در window function ها دیده ایم:

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP 
( ORDER BY order_by_expression [ ASC | DESC ] )   OVER ( [  ] )

     numeric_literal: درصد رتبه(percent rank) مورد نظر. به عنوان مثال برای یافتن میانه برابر با 0.5 است. 

     WITHIN GROUP: مجموعه ای(ستونی) که باید محاسبه تابع در آن انجام شود را مشخص می کند. 

مراحل مورد نیاز جهت تولید خروجی تابع Percentile-DISC به صورت زیر است:

1. مرتب سازی رکوردها بر اساس فیلد ORDER BY
2. جستجوی کوچکترین CUME_DIST که بزرگتر یا مساوی با numeric_literal است
3. نمایش مقدار با CUME_DIST یافته شده

به منظور فراگیری آسان تر عملکرد و کاربرد این تابع، میانه(median) حقوق پرسنل در جدول Employees را برای هر گروه کاری محاسبه می کنیم:

 در کوئری بالا، هدف این است که در هر گروه کاری یک حقوق دریافتی را جستجو کنیم که نیمی از حقوق ها از آن کمتر و  نیمی دیگر از حقوق ها از آن بیشتر باشد. بنابر این باید مقدار numeric_literal را برابر با 0.5 و یا به عبارتی دیگر %50 قرار دهیم. همانطور که اشاره شد، مقدار حاصل از تابع Percentile-DISC از طریق مقایسه CUME_DIST متناظر با حقوق هر یک از کارمندان با numeric_literal حاصل می شود. پس در هر گروه کاری، حقوق(Salary) کارمندی که دارای کوچکترین مقدار CUME_DIST بزرگتر و یا مساوی با 0.5 باشد انتخاب می شود.

نکته: مقدار بازگشتی تابع Percentile-DISC همواره در بین مجموعه اعداد ستون مورد محاسبه قرار دارد.

2-2. WINDOW FUNCTION و توابع تحلیلی - PERCENTILE_CONT

فرمت این تابع نیز مانند Percentile-DISC و به صورت زیر است:

PERCENTILE_CONT ( numeric_literal ) WITHIN GROUP 
( ORDER BY order_by_expression [ ASC | DESC ] )   OVER ( [  ] )

اگر چه دو تابع دارای فرمت ظاهری یکسانی هستند اما در محاسبه تفاوت هایی دارند. در واقع PERCENTILE_CONT کمی پیچیده تر از Percentile-DISC است. عملکرد این تابع را با دو روش زیر توضیح می دهیم:

     a. با توجه به زوج و فرد بودن تعداد رکوردها (در هر پارتیشن):

      اگر تعداد رکوردها فرد باشد آن گاه عدد وسطی به عنوان میانه در نظر گرفته می شود. به طور مثال اگر اعداد a, b, c به ترتیب باشند آنگاه میانه، برابر است با  b
     اگر تعداد رکوردها زوج باشد میانگین دو عدد وسطی محاسبه و به عنوان میانه معرفی می شود. برای اعداد a, b, c, d میانه برابراست با: b+c/2

    b. استفاده از فرمول درون یابی خطی(linear interpolation):

RN = (1 + (P * ( N - 1 )) محاسبه کردن شماره ردیف مورد نظر 
P = Percentile Specified
N = Number of rows

CRN = CEILING(RN) سقف شماره ردیف
FRN = FLOOR(RN). کف شماره ردیف
If (CRN = FRN = RN) then the result is [value of expression from row at RN] مقدار معادل با شماره ردیف
Otherwise the result is  در صورتی که شماره ردیف، عدد صحیح نباشد
    (CRN - RN) * (value of expression for row at FRN) +
    (RN - FRN) * (value of expression for row at CRN)

کوئری زیر جهت محاسبه میانه(median) حقوق پرسنل در جدول Employees برای هر گروه کاری با استفاده از تابع PERCENTILE_CONT و روش درون یابی خطی(linear interpolation) طراحی شده است:

تابع PERCENTILE_CONT را برای کوئری بالا با توجه به زوج و فرد بودن تعداد رکوردها در هر پارتیشن محاسبه و با نتیجه حاصل شده مقایسه کنید.

نکته: مقدار بازگشتی تابع PERCENTILE_CONT ممکن است در بین مجموعه اعداد ستون مورد محاسبه قرار نداشته باشد.

به عنوان یک مثال مرتبط با نکته بالا، در گروه کاری C مقدار (0.5)PERCENTILE_CONT برابر با 4.45 شده است که در ستون Salary این مقدار وجود ندارد.

در شکل زیر، خروجی توابع PERCENTILE_CONT و PERCENTILE_DISC و AVG جهت مقایسه نتایج، در دو حالت پارتیشن بندی و بدون پارتیشن بندی نمایش داده شده است:

نکته: نوع مرتب سازی صعودی و یا نزولی فیلد مورد نظر، در خروجی توابع PERCENTILE_CONT و PERCENTILE_DISC تأثیر گذار است.

تأثیر نکته بالا در تغییر خروجی توابع PERCENTILE_CONT و PERCENTILE_DISC را با تغییر مرتب سازی پیش فرض از ASC به DESC در ORDER BY به عنوان تمرین بررسی کنید.

• قبلی