پارتیشن بندی در فانکشنهای Rank & DENSE_RANK

با سلام

در مبحث قبلی در مورد فانکشنهای RANK و DENSE_RANK صحبت کردم به عنوان مثال ما میخوایم در دیتابیس Northwind لیست مشتریان هر کشور با مجموع خرید هر مشتری رو تهیه کنیم و به هر مشتری در هر کشور بنا به میزان خریدش یه RANK اختصاص بدیم پس ما باید لیست مشتریان رو با نام کشورشون و جمع خرید هر مشتری رو به صورت نزولی لیست کنیم و به بیشترین خرید در هر کشور رنک ۱ رو بدیم کوئری یه صورت زیر درمیاد :

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

خروجی :

این کوئری در Northwind تعداد ۸۹ رکورد را نمایش میدهد که من به علت کمبود فضا ۷ تاشو کپی کردم.

مثال بعدی : میخوایم لیست گرانترین محصولات هر مجموعه را در Northwind بدست بیاریم:

اگر بخوایم از طریق فانکشن RANK این مسئله رو حل کنیم باید از Common Table Expression یا CTE استفاده کنیم.

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

خروجی :