PERCENT_RANK

PERCENT_RANK calculates the relative rank of a row within a group of rows. PERCENT_RANK is used to evaluate the relative standing of a value within a rowset or partition. The range of values returned by PERCENT_RANK is greater than 0 and less than or equal to 1.

PERCENT_RANK( )
    OVER (
        [PARTITION BY <identifier>; …[n]]
        ORDER BY <identifier,> …[n] [ASC|DESC]
    ) AS <alias>;

Notes

• The first row in any set has a PERCENT_RANK of 0.
• NULL values are treated as the lowest possible values.
• PERCENT_RANK is similar to the CUME_DIST the function. Unlike CUME_DIST, PERCENT_RANK is always 0 for the first row.

The following example uses the PERCENT_RANK function to compute the latency percentile for each query within a vertical. The PARTITION BY clause is specified to partition the rows in the result set by the vertical. The ORDER BY clause in the OVER clause orders the rows in each partition. The value returned by the PERCENT_RANK function represents the rank of the queries' latency within a vertical as a percentage.

@result=
    SELECT
        *,
        PERCENT_RANK() 
            OVER (PARTITION BY Vertical ORDER BY Latency) AS PercentRank
    FROM @querylog;