توجه
Ranking Functions از SQL Server 2008 به SQL Server اضافه شده است.
با سلام
در مبحث قبلی در مورد فانکشنهای RANK و DENSE_RANK صحبت کردم به عنوان مثال ما میخوایم در دیتابیس Northwind لیست مشتریان هر کشور با مجموع خرید هر مشتری رو تهیه کنیم و به هر مشتری در هر کشور بنا به میزان خریدش یه RANK اختصاص بدیم پس ما باید لیست مشتریان رو با نام کشورشون و جمع خرید هر مشتری رو به صورت نزولی لیست کنیم و به بیشترین خرید در هر کشور رنک ۱ رو بدیم کوئری یه صورت زیر درمیاد :
|
1 2 3 4 5 6 |
SELECT Customers.Country, Customers.CustomerID, Customers.CompanyName, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS TotalPurchase , RANK() OVER (PARTITION BY Customers.Country ORDER BY SUM([Order Details].UnitPrice * [Order Details].Quantity) DESC) AS PR FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Customers.Country, Customers.CustomerID, Customers.CompanyName |
خروجی :
| Country | CustomerID | CompanyName | TotalPurchase | PR |
|---|---|---|---|---|
| Argentina | OCEAN | Océano Atlántico Ltda. | ۳۴۶۰٫۲۰ | ۱ |
| Argentina | RANCH | Rancho grande | ۲۸۴۴٫۱۰ | ۲ |
| Argentina | CACTU | Cactus Comidas para llevar | ۱۸۱۴٫۸۰ | ۳ |
| Austria | ERNSH | Ernst Handel | ۱۱۰۷۷۱٫۸۸ | ۱ |
| Austria | PICCO | Piccolo und mehr | ۲۶۲۵۹٫۹۵ | ۲ |
| Belgium | SUPRD | Suprêmes délices | ۲۴۷۰۴٫۴۰ | ۱ |
| Belgium | MAISD | Maison Dewey | ۱۰۴۳۰٫۵۸ | ۲ |
این کوئری در Northwind تعداد ۸۹ رکورد را نمایش میدهد که من به علت کمبود فضا ۷ تاشو کپی کردم.
مثال بعدی : میخوایم لیست گرانترین محصولات هر مجموعه را در Northwind بدست بیاریم:
اگر بخوایم از طریق فانکشن RANK این مسئله رو حل کنیم باید از Common Table Expression یا CTE استفاده کنیم.
|
1 2 3 4 5 6 7 8 9 10 11 |
WITH T AS ( SELECT C.CategoryID , C.CategoryName , P.ProductID , P.ProductName , P.UnitPrice , RANK() OVER (PARTITION BY C.CategoryID ORDER BY P.UnitPrice DESC) AS PriceRank FROM Categories C INNER JOIN Products P ON C.CategoryID = P.CategoryID ) SELECT CategoryID , CategoryName , ProductID , ProductName , UnitPrice FROM T WHERE PriceRank = 1 |
خروجی :
| CategoryID | CategoryName | ProductID | ProductName | UnitPrice |
|---|---|---|---|---|
| ۱ | Beverages | ۳۸ | Côte de Blaye | ۲۶۳٫۵۰ |
| ۲ | Condiments | ۶۳ | Vegie-spread | ۴۳٫۹۰ |
| ۳ | Confections | ۲۰ | Sir Rodney’s Marmalade | ۸۱٫۰۰ |
| ۴ | Dairy Products | ۵۹ | Raclette Courdavault | ۵۵٫۰۰ |
| ۵ | Grains/Cereals | ۵۶ | Gnocchi di nonna Alice | ۳۸٫۰۰ |
| ۶ | Meat/Poultry | ۲۹ | Thüringer Rostbratwurst | ۱۲۳٫۷۹ |
| ۷ | Produce | ۵۱ | Manjimup Dried Apples | ۵۳٫۰۰ |
| ۸ | Seafood | ۱۸ | Carnarvon Tigers | ۶۲٫۵۰ |
