مثالهای عملی مستقل و قابل اجرا
مثال 1: محاسبه جایگاه نسبی پایه
CUME_DIST جایگاه هر امتیاز را در توزیع مرتبشده برمیگرداند.
WITH Scores AS (SELECT * FROM (VALUES (1,N'الف',40),(2,N'ب',60),(3,N'پ',60),(4,N'ت',80),(5,N'ث',100))V(Id,Name,Score))
SELECT Id,Name,Score,CUME_DIST() OVER(ORDER BY Score) AS RelativePosition FROM Scores ORDER BY Score,Id;
| نام | امتیاز | CUME_DIST |
|---|
| پ | 60 | 0.600000 |
نکته کاربردی: خروجی float است؛ برای نمایش درصد میتوان در 100 ضرب کرد ولی دقت محاسباتی را زود گرد نکنید.
مثال 2: تأثیر مقادیر مساوی
دو امتیاز 60 همرتبهاند و تابع برای هر دو خروجی یکسان تولید میکند.
WITH Scores AS (SELECT * FROM (VALUES (1,N'الف',40),(2,N'ب',60),(3,N'پ',60),(4,N'ت',80),(5,N'ث',100))V(Id,Name,Score))
SELECT Name,Score,RANK() OVER(ORDER BY Score) AS Rnk,CUME_DIST() OVER(ORDER BY Score) AS P FROM Scores ORDER BY Score,Id;
| امتیاز | رتبه | نسبت |
|---|
| 60 | 2 | 0.600000 |
نکته کاربردی: شناخت رفتار tie برای ساخت Badge، گروهبندی مشتری و گزارش منابع انسانی ضروری است.
مثال 3: رتبهبندی مستقل در هر گروه
پارتیشن باعث میشود توزیع هر دپارتمان با جمعیت خودش سنجیده شود.
WITH D AS (SELECT * FROM (VALUES (1,N'فروش',70),(2,N'فروش',90),(3,N'فنی',60),(4,N'فنی',80))V(Id,Dept,Score))
SELECT Dept,Id,Score,CUME_DIST() OVER(PARTITION BY Dept ORDER BY Score) AS P FROM D ORDER BY Dept,Score;
| واحد | امتیاز | جایگاه |
|---|
| فروش | 90 | 1 |
| فنی | 60 | 0.5 |
نکته کاربردی: مقایسه بین پارتیشنهای بسیار کوچک ممکن است گمراهکننده باشد؛ اندازه گروه را نیز گزارش کنید.
مثال 4: تبدیل خروجی به درصد خوانا
CAST کنترلشده، درصد را برای داشبورد خوانا میکند و مقدار خام برای تحلیل حفظ میشود.
WITH Scores AS (SELECT * FROM (VALUES (1,N'الف',40),(2,N'ب',60),(3,N'پ',60),(4,N'ت',80),(5,N'ث',100))V(Id,Name,Score))
SELECT Name,Score,CAST(100.0*CUME_DIST() OVER(ORDER BY Score) AS decimal(6,2)) AS PercentValue FROM Scores ORDER BY Score,Id;
نکته کاربردی: برای محاسبات بعدی از مقدار float خام و برای لایه نمایش از decimal گرد شده استفاده کنید.
مثال 5: فیلتر افراد بالای آستانه
چون تابع پنجرهای در WHERE همان سطح مجاز نیست، CTE ابتدا جایگاه را محاسبه میکند.
WITH Scores AS (SELECT * FROM (VALUES (1,N'الف',40),(2,N'ب',60),(3,N'پ',60),(4,N'ت',80),(5,N'ث',100))V(Id,Name,Score))
,W AS(SELECT *,CUME_DIST() OVER(ORDER BY Score) AS P FROM Scores) SELECT Id,Name,Score,P FROM W WHERE P>=0.75 ORDER BY P;
نکته کاربردی: آستانه 0.75 باید با تعریف کسبوکار هماهنگ شود؛ دو تابع در مرزها ممکن است اعضای متفاوتی انتخاب کنند.
مثال 6: رفتار پارتیشن تکردیفی
این حالت مرزی برای شعبه یا محصول تازهوارد اهمیت دارد.
WITH D AS (SELECT * FROM (VALUES (1,N'قدیمی',10),(2,N'قدیمی',20),(3,N'جدید',50))V(Id,Grp,Value))
SELECT Grp,Value,CUME_DIST() OVER(PARTITION BY Grp ORDER BY Value) AS P FROM D ORDER BY Grp,Value;
نکته کاربردی: در گزارش، گروه تکعضوی را علامتگذاری کنید تا صفر یا یک بهاشتباه برتری آماری تعبیر نشود.
مثال 7: مدیریت NULL
SQL Server در ترتیب صعودی NULL را در ابتدای مجموعه قرار میدهد؛ میتوان آن را فیلتر یا با CASE مرتب کرد.
WITH D AS (SELECT * FROM (VALUES (1,CAST(NULL AS int)),(2,10),(3,20))V(Id,Value))
SELECT Id,Value,CUME_DIST() OVER(ORDER BY CASE WHEN Value IS NULL THEN 1 ELSE 0 END,Value) AS P FROM D ORDER BY Id;
نکته کاربردی: سیاست NULL باید صریح باشد؛ حذف، انتهای ترتیب یا یک گروه جدا سه معنای متفاوت دارند.
مثال 8: گزارش طبقهبندی مشتریان
خروجی نسبی میتواند برای برچسب سطح مشتری با CASE استفاده شود.
WITH Scores AS (SELECT * FROM (VALUES (1,N'الف',40),(2,N'ب',60),(3,N'پ',60),(4,N'ت',80),(5,N'ث',100))V(Id,Name,Score))
,W AS(SELECT *,CUME_DIST() OVER(ORDER BY Score) AS P FROM Scores) SELECT Name,Score,CASE WHEN P>=0.8 THEN N'طلایی' WHEN P>=0.4 THEN N'نقرهای' ELSE N'عادی' END AS Segment FROM W ORDER BY Score;
نکته کاربردی: حدود Segment را با تحلیل توزیع و ظرفیت خدمترسانی تعیین کنید، نه با اعداد قراردادی بدون آزمون.
مثال 9: مقایسه روش نادرست تقسیم ROW_NUMBER
تقسیم ROW_NUMBER بر COUNT رفتار tie و نقطه شروع تابع استاندارد را بازتولید نمیکند.
WITH Scores AS (SELECT * FROM (VALUES (1,N'الف',40),(2,N'ب',60),(3,N'پ',60),(4,N'ت',80),(5,N'ث',100))V(Id,Name,Score))
SELECT Name,Score,1.0*ROW_NUMBER() OVER(ORDER BY Score)/COUNT(*) OVER() AS WrongFormula,CUME_DIST() OVER(ORDER BY Score) AS CorrectValue FROM Scores ORDER BY Score,Id;
| روش | ردیف اول |
|---|
| تقسیم ROW_NUMBER | 0.20 |
| CUME_DIST | 0.20 |
نکته کاربردی: از تابع داخلی استفاده کنید تا تعریف آماری و رفتار مقادیر مساوی دقیق بماند.
مثال 10: ایندکس مناسب رتبهبندی
ایندکس روی کلید گروه و مقدار مرتبسازی میتواند Sort را سبکتر کند.
CREATE TABLE #Scores(DeptId int,Score int,Id bigint);
INSERT #Scores VALUES(1,40,1),(1,60,2),(1,80,3),(2,50,4);
CREATE INDEX IX_Scores_Window ON #Scores(DeptId,Score,Id);
SELECT DeptId,Score,CUME_DIST() OVER(PARTITION BY DeptId ORDER BY Score) AS P FROM #Scores;
DROP TABLE #Scores;
| شاخص کنترل | هدف |
|---|
| Actual Plan | بررسی Sort و Spill |
نکته کاربردی: نتیجه را با STATISTICS IO/TIME و طرح اجرای واقعی اندازهگیری کنید؛ وجود ایندکس بهتنهایی تضمین استفاده نیست.