آشنایی با Window Function ها در SQL Server بخش پنجم

اندازه‌ی قلم متن

تخمین مدت زمان مطالعه‌ی مطلب: دو دقیقه

در این بخش فانکشن د‌یگری از توابع تحلیلی به نام CUME_DIST را بررسی می‌نماییم.

CUME_DIST:

بوسیله تابع CUME_DIST می‌توان ارزیابی نمود، در یک گروه، چه درصد از مقادیر،مساوی یا کوچکتر از مقدار سطر جاری می‌باشند، به این تابع cumulative distribution نیز گفته می‌شود.

Syntax تابع CUME_DIST به صورت زیر است:

CUME_DIST( )
    OVER ( [ partition_by_clause ] order_by_clause )

شرح Syntax:

1- Partition By Clause : بوسیله پارامتر فوق می‌توانید، نتیجه پرس جو (Query)،خود را دسته بندی نمایید.

2- order by clause : همانطور که از نامش مشخص است، جهت مرتب نمودن خروجی Query می‌باشد.

معمولا شرح عملکرد توابع تحلیلی، کمی مشکل است. بنابراین برای درک، عملکرد تابع CUME_DIST چند مثال را بررسی می‌کنیم.

در ابتدا بوسیله Script زیر یک جدول ایجاد و 10 رکورد در آن درج می‌کنیم:

Create Table TestCUME_DIST
(SalesOrderID int not null,
OrderQty smallint not null,
ProductID int not null
 );
 GO
Insert Into TestCUME_DIST
       Values (43663,1,760),(43667,3,710),(43667,1,773),
  (43667,1,775),(43667,1,778),(43669,1,747),
  (43670,1,709),(43670,2,710),(43670,2,773),(43670,1,776)

مثال اول: Script زیر را اجرا می‌کنیم، سپس خروجی آن را بررسی می‌نماییم:

SELECT SalesOrderID, OrderQty,
       CUME_DIST() OVER(ORDER BY SalesOrderID) AS [CUME_DIST]
FROM TestCUME_DIST ORDER BY [CUME_DIST] DESC

پس از اجرا خروجی بصورت زیر خواهد بود:

در ادامه اجازه دهید،مقادیری که در فیلد CUME_DIST بدست آمده است را بصورت تصویری بررسی کنیم.

مقادیر سطر اول تا چهارم:

*** برای بدست آوردن CUME_DIST سطر پنجم نیز خواهیم داشت:

Rows=(c1+c2)/c3 بنابراین خواهیم داشت: 0/6=10/(5+1)=Rows

مثال دوم : ابتدا Script زیر را اجرا نمایید:

SELECT SalesOrderID, OrderQty, ProductID,
       CUME_DIST() OVER(PARTITION BY SalesOrderID ORDER BY ProductID ) AS [CUME_DIST]
FROM TestCUME_DIST
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC, [CUME_DIST] DESC

خروجی :

همانگونه که ملاحظه می‌کنید، در این مثال، خروجی، براساس SalesOrderID به چهار گروه تقسیم می‌شود و عملیات مرتب سازی روی فیلد ProductID انجام می گیرد، بنابراین CUME_DIST، روی هر گروه بر روی فیلد ProductID محاسبه می‌شود.

گروه اول : نحوه محاسبه Cume_DIST سطر اول:

سوال:چه تعداد از مقادیر ProductID آن برابر 776 میباشد؟

جواب: فقط مقدار سطر اول، بنابراین خواهیم داشت C1=1

سوال: چه تعداد از مقادیر کوچکتر از ProductID=776 می‌باشد؟

جواب: مقدار سه سطر، در واقع مقادیر سطر دوم،سوم و چهارم کوچکتر از مقدار سطر اول می‌باشند، c2=3

سوال: تعداد کل سطرهای گروه اول چه مقدار می‌باشد؟

جواب: 4سطر

بنابراین برای بدست آوردن CUME_DIST سطر اول خواهیم داشت:

1=4/(1+3)=Rows

محاسبه سطر دوم از گروه اول بدون شرح:

0/75=4/(1+2)=Rows

امیدوارم مفید واقع شده باشد.

آشنایی با Window Function ها در SQL Server بخش پنجم

مطالب مشابه