265
Chapter 10: Aggregating, Windowing, and Ranking Data
10
911 6 2
927 9 2
898 9 2
897 2 1
942 5 1
943 6 1Like the other three ranking functions, you can use NTILE()with a partitioned OVER()
clause. Similar to the ranking example, you could partition the previous example by prod-
uct category to generate percentiles within each category.Aggregate Functions
SQL query functions all fi t together like a magnifi cent puzzle. A fi ne example is how win-
dowing can use not only the four ranking functions — ROW_NUMBER(), RANK(), DENSE_
RANK(), and NTILE()— but also the standard aggregate functions: COUNT(*), MIN(),
MAX(), and so on.Usually the aggregate functions fi t well within a normal aggregate query; however, follow-
ing is an example of using the SUM() aggregate function in a window to calculate the total
sales order count for each product subcategory. Using that result from the window, you can
calculate the percentage of sales orders for each product within its subcategory:USE AdventureWorks
GOSELECT ProductID, Product, SalesCount,
NTILE(100) OVER (ORDER BY SalesCount) as Percentile,
SubCat,
CAST(CAST(SalesCount AS NUMERIC(9,2))
/ SUM(SalesCount) OVER(Partition BY SubCat)
* 100 AS NUMERIC (4,1)) AS SubPer
FROM (SELECT P.ProductID, P.[Name] AS Product,
PSC.Name AS SubCat, COUNT(*) as SalesCount
FROM Sales.SalesOrderDetail AS SOD
JOIN Production.Product AS P
ON SOD.ProductID = P.ProductID
JOIN Production.ProductSubcategory PSC
ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
GROUP BY PSC.Name, P.[Name], P.ProductID
) Q
ORDER BY Percentile DESCc10.indd 265c10.indd 265 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM
http://www.it-ebooks.info