مطالبی را که در ادامه به آنها پرداخته می شوند جزء پرس و جوهای پیشرفته (Advanced Querying) طبقه بندی می شوند. البته فراموش نکنید که زبان SQL تنها به Querying خلاصه نمی شود، بلکه مباحث جذاب دیگری را شامل می شود، بطور نمونه تعریف ساختار جداول بانک. عموما بدلیل اینکه SQL بطور فریب انگیزی ساده به نظر می رسد افراد کمی برای حرفه ای شدن در آن وقت صرف می کنند.
برای اینکه افراد با مباحث پیشرفته و حرفه ای آشنا شوند تاکنون سعی بر آن داشتم که به اینگونه عناوین بپردازم. مثل cross tabbing, removing duplicates, splitting, relational division, concatenating. و در این لحظه برای بحث Analytical Ranking Function وقت صرف خواهیم کرد.
Analytical Ranking Functions
SQL Server از چهار تابع Ranking پشتیبانی می کند. یعنی RANK, DENSE_RANK, ROW_NUMBER و NTILE. این توابع روش بسیار موثر و ساده ای را برای تولید و محاسبه ی Ranking در اختیار گذاشته اند. البته باید توجه داشت که در نسخه ی 2000 این توابع داخلی (Built-in) وجود ندارند. و ناچاریم از روشهای جایگزین (Alternate Solution) این محاسبات را انجام دهیم. در این مقاله به روش های جایگزین نیز پرداخته خواهد شد. یکی از دلایل آن افزایش تجربه در منطق querying است.
توابع ranking جزء توابع OLAP یا (online analytic processing) به حساب می آیند. و به این توابع window function نیز گفته می شود.
توابع Ranking دارای 3 زیر ماده (subclauses) هستند، ranking function و ordering و partitioning:
ranking_function OVER([PARTITION BY col_list] ORDER BY col_list)
ماده ی اختیاری partition_by به ما اجازه می دهد که از توابع برای هر بخش (یا گروهی) بطور مستقل استفاده کنیم.
Row_Number
این تابع اعداد ترتیبی را به هر سطر از جدول بر اساس ترتیب مشخص شده به نتیجه ی کوئری (query result set) می دهد. به مثال زیر توجه کنید که از دو طریق شماره ترتیبی به نتیجه ی یک کوئری بر اساس ستون nbr داده شده است:
WITH test_table(nbr) AS
(
SELECT 1 UNION
SELECT 2 UNION
SELECT 5 UNION
SELECT 12
)
SELECT nbr,
--Using Built-in Function
ROW_NUMBER() OVER(ORDER BY nbr ASC) AS [Rank_ROW_NUMBER],
--Using Subquery
(SELECT COUNT(*)
FROM test_table
WHERE nbr <= t.nbr) AS [Rank_Subquery]
FROM test_table AS t;
/*Reslut Set
nbr Rank_ROW_NUMBER Rank_Subquery
----------- -------------------- -------------
1 1 1
2 2 2
5 3 3
12 4 4
*/
داده های موجود در ستون nbr جدول فوق منحصر بفرد هستند. ولی زمانی که داده های تکراری در sort column (که در اینجا ستون nbr است) موجود باشد. آیا subquery فوق جواب خواهد داد؟ جواب خیر است. پس بایستی از چه طریقی این مشکل را حل کرد؟
اگر در جدول ستون دیگری بود که مقادیر منحصر بفردی داشت می توانستیم از آن به عنوان تفکیک کننده (tiebreaker) استفاده کنیم. به مثال زیر توجه کنید که از ستون emp به عنوان tiebreaker استفاده شده است:
در مثال زیر، در ماده ی ORDER BY مربوط به تابع ROW_NUMBER نیازی به استفاده از ستون emp نیست. ولی به دلیل بحث determinism آن را قرار دادم.
WITH test_table(emp, nbr) AS
(
SELECT 'A', 14 UNION
SELECT 'B', 10 UNION
SELECT 'C', 05 UNION
SELECT 'D', 10 UNION
SELECT 'E', 19
)
SELECT *,
--Using Built-in Function
ROW_NUMBER() OVER(ORDER BY nbr ASC, emp ASC) AS [Rank_ROW_NUMBER],
--Using Subquery
(SELECT COUNT(*)
FROM test_table
WHERE nbr < t.nbr
OR (nbr = t.nbr AND emp <= t.emp)) AS [Rank_Subquery]
FROM test_table AS t
ORDER BY [Rank_Subquery] ;
/*Reslut Set
emp nbr Rank_ROW_NUMBER Rank_Subquery
---- ----------- -------------------- -------------
C 5 1 1
B 10 2 2
D 10 3 3
A 14 4 4
E 19 5 5
*/
حالا زمانی را فرض کنید که هیچ tiebreaker ای در جدول وجود ندارد که بخواهیم از آن برای حل مساله ی مقادیر تکراری sort column استفاده کنیم. در اینجا مساله به شکل قابل توجهی پیچیده خواهد شد. البته اگر با جدول اعداد و استفاده از آن که تا حدودی قبلا به آن پرداختم آشنایی پیدا کرده باشین می توانید راه حلی را که در ادامه قرار داده شده است را متوجه شوید:
;WITH test_table AS
(
SELECT *
FROM (VALUES (1),(1),(1),(2),(3),(3),(4),(4),(4),(4)) D(nbr)
),
Nums AS
(
SELECT *
FROM (VALUES(1),(2),(3),(4)) N(n)
)
, C AS
(SELECT nbr, COUNT(*) AS Cnt,
(SELECT COUNT(*)
FROM test_table
WHERE nbr < t.nbr) AS Smaller
FROM test_table t
GROUP BY nbr
)
SELECT C.nbr, Smaller + n AS rownum
FROM C
CROSS JOIN Nums N
WHERE N.n <= C.Cnt
ORDER BY rownum ;
/* Query Result Set
nbr rownum
----------- -----------
1 1
1 2
1 3
2 4
3 5
3 6
4 7
4 8
4 9
4 10
*/
برای راحتی کار از عبارتهای جدولی و VALUES برای تولید داده های آزمایشی استفاده شده است. شما بایستی جدول اعداد را قبلا ایجاد کرده باشید و از آن استفاده کنید. نه انیکه در خود کوئری اعداد متوالی را تولید کنید.
شما می توانید از یک جدول موقت و خصیصه ی IDENTITY نتیجه ای مشابه به نتیجه ی مورد نظر را بدست آورید. حتی می توانیم یک ستون دیگر به جدول اضافه کنیم و یک قید پیشفرض با مقدار NEWID برای آن تعریف کرده و به عنوان tiebreaker از آن برای حل مساله استفاده کنیم.
روش subquery بدون در نظر گرفتن partitioning هزینه ی بسیار بالایی در مجموعه داده های بالا دارد. با فرض اینکه داده ها از روی index خوانده شوند تعداد رویت ها بسیار بالا است. اگر 10 سطر داشته باشیم. 1+2+3+4+5+6+7+8+9+10 بار سطرها از روی index خوانده خواهند شد. یعنی 11*5 که برابر با 55 است. برای بدست آوردن این سری فوق العاده ساده از یک فرمول بسیار ساده می توانیم استفاده کنیم. اولین عنصر را با آخرین عنصر جمع کرده و به تعداد نصف کل عناصر با همدیگر جمع می کنیم. یعنی با فرض اینکه بخواهیم اعداد 1 تا 10 را با یکدیگر جمع کنیم داریم:
(1+10) + (2+9) + (3+8) + ... + (5+6) =
11 + 11 + ... + 11 = (10/2) * 11 = 55
==> 1+2+3+...+n = (n+1) * (n/2) = [(n*n) + n] / 2
همانطوری که از فرمول بدست آمد مشخص هست، الگوریتم هایی که بر اساس subquery پایه ریزی شده اند دارای پیچیدگی محاسباتی نمایی هستند.
مطالبی که راجب این بحث باقی می مانند، partitioning و دیگر توابع می باشند. که مطلب خاصی ندارند. چون به مهمترین مطالب در این مقاله پرداخته شد.
بهترین کتابی که برای مطالعه بیشتر می توانم معرفی کنم کتاب زیر است. البته قطعا کتاب های بسیاری هستند که به اینگونه توابع در فصول پیشرفته ی خود پرداخته اند. ولی خصوصیت منحصر بفرد کتابهای سری Inside این است که تمام مطالب آن پیشرفته هستند.
Microsodt Press: Microsoft SQL Server 2008/2005 T-SQL: Querying by Itzik Ben Gan