www.dntips.ir
مطالب ۳۶۹۴
نویسندگان ۲۷۶
گروه‌های مطالب ۱۰۲۴
نقشه‌های راه ۱۱۹
دوره‌ها ۱۴
اشتراک‌ها ۱۷۹۱۴
عنوان:

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


نویسنده: فرهاد فرهمندخواه
تاریخ: ۱۳۹۲/۰۱/۲۹ ۱۸:۵۰
آدرس: www.dntips.ir
در این بخش فانکشن د‌یگری از توابع تحلیلی به نام 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  

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

مطالب مشابه