Percentile Calculation

Question

Percentile Calculation

scotsditch

Ten Centuries

Points: 1020
More actions
September 16, 2015 at 9:20 pm

#399057

Does anyone know a good way to add a percentile calculation to a table in sql server 2008 r2?
I've got 4 fields and I'd like to add a 5th labeling the records according to the percentile they fall in for one of the fields.
Any tips greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Can you give more detail regarding you actual scenario, like table structure, some sample data and your desired output to better understand your problem?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

You need PERCENT_RANK which is not available In SQL 2012.

To get the Percent Rank Pre-2012:

If rk is the RANK for the specified partition

AND rc is the row count for the specified partition

The formula to calculate Percent Rank is (rk-1)/(rc-1).

Since I don't have sample data to work with I'll create some along with an example of how you'd do this Pre-SQL 2012...

-- Sales Table for sample data

DECLARE @Sales TABLE (SalesPersonID int, SaleYear int, SalesAmt int);

-- Create Sample Data

WITH

iTally AS (SELECT N FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) T(N)),

Years AS (SELECT YR FROM (VALUES (2010),(2011),(2012)) T(YR))

INSERT @Sales

SELECT SalesPersonID = N, SaleYear = YR, SalesAmt = ABS(CHECKSUM(newid())%20)+1

FROM Years CROSS JOIN iTally;

-- Calculate Percent Rank for each sales person per year.

WITH X AS

(

SELECT SalesPersonID, SaleYear, SalesAmt,

rk = RANK() OVER (PARTITION BY SaleYear ORDER BY SalesAmt),

rc = COUNT(*) OVER (PARTITION BY SaleYear)

FROM @Sales

)

SELECT SalesPersonID, SaleYear, SalesAmt,

PercentRank = 1.*(rk-1)/(rc-1)

FROM X

Edit: Forgot to mention. The solution above is calculating the percent rank for each salesperson per year. If you are not grouping the data like I did (for year) then the partition clauses are not needed. Also, COUNT(*) OVER is somewhat expensive. My solution can be tuned but I am out of time 😉

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 3

What about NTILE in a Windows function? Would that work in your scenario?

***SQL born on date Spring 2013:-)

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 4

Alan.B (9/16/2015)
You need PERCENT_RANK which is not available In SQL 2012.
To get the Percent Rank Pre-2012:
If rk is the RANK for the specified partition
AND rc is the row count for the specified partition
The formula to calculate Percent Rank is (rk-1)/(rc-1).
Since I don't have sample data to work with I'll create some along with an example of how you'd do this Pre-SQL 2012...
-- Sales Table for sample data
DECLARE @Sales TABLE (SalesPersonID int, SaleYear int, SalesAmt int);
-- Create Sample Data
WITH
iTally AS (SELECT N FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) T(N)),
Years AS (SELECT YR FROM (VALUES (2010),(2011),(2012)) T(YR))
INSERT @Sales
SELECT SalesPersonID = N, SaleYear = YR, SalesAmt = ABS(CHECKSUM(newid())%20)+1
FROM Years CROSS JOIN iTally;
-- Calculate Percent Rank for each sales person per year.
WITH X AS
(
SELECT SalesPersonID, SaleYear, SalesAmt,
rk = RANK() OVER (PARTITION BY SaleYear ORDER BY SalesAmt),
rc = COUNT(*) OVER (PARTITION BY SaleYear)
FROM @Sales
)
SELECT SalesPersonID, SaleYear, SalesAmt,
PercentRank = 1.*(rk-1)/(rc-1)
FROM X
Edit: Forgot to mention. The solution above is calculating the percent rank for each salesperson per year. If you are not grouping the data like I did (for year) then the partition clauses are not needed. Also, COUNT(*) OVER is somewhat expensive. My solution can be tuned but I am out of time 😉

Works perfectly in a scenario where I need a similar set for quarters. Had to create a quarterly set and group the final result at this level instead of year.