PERCENT_RANK function in SQL Server 2012


PERCENT_RANK is a new function introduced in SQL Server 2012. The function calculates the relative rank of a row within a subset of rows or in simpler terms  it shows the percentage of values that are less than or equal to the current value.

The syntax for the function is as follows

 PERCENT_RANK( )
 OVER ( [ partition_by_clause ] order_by_clause )

Let us quickly implement PERCENT_RANK and understand how it works.

Use the following code to create a database with values in it.

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
BEGIN
DROP DATABASE PERCENTRANK;
END
GO
CREATE DATABASE PERCENTRANK;
GO
USE PERCENTRANK
GO
CREATE TABLE PERCENTRANKDEMO
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into PERCENTRANKDEMO
VALUES
(10,1000,2005),(20,1215,2005),(30,3327,2005),
(10,2000,2006),(20,2415,2006),(30,3429,2006),
(10,3050,2007),(20,3216,2007),(30,3737,2007),
(10,3026,2008),(20,1618,2008),(30,3452,2008),
(10,2004,2009),(20,4315,2009),(30,5435,2009),
(10,3040,2010),(20,4015,2010),(30,4343,2010),
(10,3060,2011),(20,4345,2011),(30,3237,2011),
(10,3075,2012),(20,4035,2012),(30,7450,2012),
(10,3055,2013),(20,5012,2013),(30,3321,2013),
(10,3059,2014),(20,6019,2014),(30,3245,2014),
(10,3150,2015),(20,6815,2015),(30,4000,2015);

Now lets use the PERCENT_RANK function on the above database created and view the outcome. We will use the following code below.

SELECT ProductId,SalesQty,Year,
PERCENT_RANK() OVER ( ORDER BY [SalesQty] )
AS PercentRank
FROM PERCENTRANKDEMO
WHERE productid = 30;

The outcome would be as below. The column PercentRank shows the percent of values that are less or equal to the current value of the SalesQty column. The first row in any set has a PERCENT_RANK of 0. That’s how it has been designed by default. In the result set 0.1 denotes 10 percent while 1 denotes 100 percent.
PR1

Advertisements

Tagged: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: