Percentile Calculation

  • 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.

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

  • 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

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

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

  • 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.

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

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