SQL PERCENT_RANK Function

The PERCENT_RANK function returns the relative rank for each value.

Rankings can also be applied to groups (partitions) within the rows.

The values returned by PERCENT_RANK are from 0 to 1.

PERCENT_RANK is similar to the CUME_DIST function.

Example

#

Calculate the relative rank of each product price.

SELECT ProductName, UnitPrice,
       PERCENT_RANK() OVER(ORDER BY UnitPrice) AS 'Percent Rank'
  FROM Product
 ORDER BY UnitPrice
Result:  78 records
ProductName UnitPrice Percent Rank
Geitost 2.50 0
Guaraná Fantástica 4.50 0.012987012987013
Konbu 6.00 0.025974025974026
Filo Mix 7.00 0.038961038961039
Sir Rodney's Marmalade 81.00 0.961038961038961
Mishi Kobe Niku 97.00 0.974025974025974
Thüringer Rostbratwurst 123.79 0.987012987012987
Côte de Blaye 263.50 1

The percent rank starts at 0 and goes up to 1 (the last row).


Syntax

#

Syntax of the PERCENT_RANK function.

PERCENT_RANK() 
  OVER( PARTITION BY expression, ... 
        ORDER BY expression, ... )

PARTITION BY expression -- optional. Organizes rows into different groups where the PERCENT_RANK will be applied. If not specified, the entire result set is treated as a single group.

ORDER BY expression -- specifies the sort order of the result set.

More Examples

PERCENT_RANK with PARTITION

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List the orders for the year 2013. Include the percentage rank of the order amounts within each month sorted by amount.
SELECT MONTH(OrderDate) AS Month, 
       CONCAT(FirstName, ' ', LastName) AS Customer, TotalAmount, 
       PERCENT_RANK() OVER(PARTITION BY MONTH(OrderDate)
                        ORDER BY TotalAmount) AS 'Percent Rank'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
 WHERE YEAR(OrderDate) = 2013
 ORDER BY MONTH(OrderDate), TotalAmount
Result:  408 records
Order Month Customer TotalAmount Percent Rank
1 Paolo Accorti 49.80 0
1 Yoshi Latimer 102.40 0.03125
1 Maurizio Moroni 192.00 0.03125
1 Roland Mendel 5796.00 0.9375
1 Jytte Petersen 11283.20 0.96875
1 Jean Fresnière 11493.20 1
2, 3, 4, ... 11
12 Patricio Simpson 12.50 0
12 Paolo Accorti 18.40 0.0212765957446809
12 Pascale Cartrain 28.00 0.0425531914893617
12 Martín Sommer 4035.80 0.957446808510638
12 Marias Larsson 4337.00 0.978723404255319
12 Roland Mendel 6984.50 1

PERCENT_RANK with GROUP BY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the monthly sales for the year 2013. Include the percentage rank values for each month sorted by month.
SELECT MONTH(OrderDate) AS 'Month', 
       SUM(TotalAmount) AS 'Monthly Sales', 
       PERCENT_RANK() OVER(ORDER BY SUM(TotalAmount)) 
                        AS 'Sales Distribution'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
 ORDER BY MONTH(OrderDate)
Result:  12 records
Month Monthly Sales Sales Distribution
1 66692.80 0.818181818181818
2 41207.20 0.181818181818182
3 39979.90 0.0909090909090909
4 55699.39 0.545454545454545
5 56823.70 0.636363636363636
6 39088.00 0
7 55464.93 0.454545454545455
8 49981.69 0.363636363636364
9 59733.02 0.727272727272727
10 70328.50 0.909090909090909
11 45913.36 0.272727272727273
12 77476.26 1

50% of the sales from year 2013 are greater than 50,000.


You may also like



Guides


vsn 3.1