This month’s T-SQL Tuesday is hosted by Jes Borland and it is all about grouping and aggregate functions.
With the introduction of window functions in T-SQL language in SQL 2005, getting top N values per group became a simple task of utilizing DENSE_RANK() or RANK() or ROW_NUMBER() function depending on the business requirements.
However, there is still an important aggregate missing from the group. In particular, PERCENT() aggregate function.
There is a Connect item in regards to the missing functions.
You may find an article by Itzik Ben-Gan on this topic Calculate Percentiles very educational.
I become interested in the problem of finding N percent of the group after reading this thread in tek-tips forum SQL Min 1 of Top 10% and Max 1 of Bottom 10%???
The first idea of approaching this problem is in using NTILE() ranking function. It is as close as we can get, but yet it’s not an exactly percentage.
Here is an example that demonstrates discrepancy:
USE AdventureWorks2008R2 GO ;with cte as (SELECT p.FirstName, p.LastName ,ROW_NUMBER() OVER (ORDER BY s.SalesYTD) AS 'Row Number' ,RANK() OVER (ORDER BY s.SalesYTD) AS 'Rank' ,DENSE_RANK() OVER (ORDER BY s.SalesYTD) AS 'Dense Rank' ,NTILE(10) OVER (ORDER BY s.SalesYTD) AS 'Quartile', s.SalesYTD, a.PostalCode, a.City FROM Sales.SalesPerson s INNER JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address a ON a.AddressID = p.BusinessEntityID ) select cte.*, TP.*, LP.* from cte LEFT join (select top 10 percent FirstName as TPFName, LastName as TPLName, SalesYTD as TPSalesYTD from cte Order by SalesYTD ) TP on cte.FirstName = TP.TPFName and cte.LastName = TP.TPLName LEFT join (select top 10 percent FirstName as LPFName, LastName as LPLName, SalesYTD as LPSalesYTD from cte Order by SalesYTD DESC) LP on cte.FirstName = LP.LPFName and cte.LastName = LP.LPLName order by cte.[Row Number]
As we can see from this example, we get correctly top 10 percent using NTILE(10) and
TOP 10 percent (LEFT JOIN) based on the SalesYTD in the ascending order, but the results from the bottom 10 percent (top 10 percent based on SalesYTD in the descending order) don’t match.
For the small result sets we can not really get a correct match using NTILE() as indicated in the Remarks section in BOL.
As we can see in our small set of only 17 records, first 7 groups have 2 records and last 3 groups have only 1 record, that’s why our top 10 percent in descending order didn’t match.
On the big enough sets the NTILE() is working correctly. Here is an example suggested by Peter Larsson, that demonstrates it:
;WITH cteRandomData(Value) AS ( SELECT ABS(CHECKSUM(NEWID())) AS Value FROM master..spt_values WHERE type = 'p' AND number BETWEEN 1 AND 1000 ), cteRanking(Value, rnk, cnt, ntil) AS ( SELECT Value, DENSE_RANK() OVER (ORDER BY Value), COUNT(*) OVER (), NTILE(10) over (order by Value) as ntil FROM cteRandomData ), ctePercentile(Value, Percentile, ntil) AS ( SELECT Value, 100 * (rnk - 1) / cnt + 1 AS Percentile, ntil FROM cteRanking ) SELECT * FROM ctePercentile --WHERE Percentile = 10