CODEWITHSUNDEEP
sqlsql-servermathsql-server-2012statistics
mcUser
mcUser

Reputation: 753

What is the mathematical way of calculating PERCENT_RANK() ,CUME_DIST() ,PERCENTILE_CONT() and PERCENTILE_DISC()

I am trying to figure out how these newly introduced function in SQL Server Denali CTP 3 is working but didnot understood properly.

There are ofcourse some articles on this but it has not been clearly mentioned as how they are working ... in other words the maths runing behind the scene.

Could anyone please explain that with some simple example.

I found one here but when I tried to put the logic of this author in the first link for getting the Percent_Rank and Cume_Dist of 5th item I am getting a different result.

Upvotes: 3

Views: 1084

Answers (1)

Quassnoi
Quassnoi

Reputation: 425613

The columns in this query will be equal:

SELECT  value,
        PERCENT_RANK() OVER (ORDER BY value),
        (
        SELECT  COUNT(CASE WHEN qo.value < q.value THEN 1 END) / (COUNT(*) - 1)
        FROM    mytable qo
        ) AS percent_rank_formula,
        CUME_DIST() OVER (ORDER BY value),
        (
        SELECT  COUNT(CASE WHEN qo.value <= q.value THEN 1 END) / COUNT(*)
        FROM    mytable qo
        ) AS cume_dist_formula
FROM    mytable q

Upvotes: 7

Related Questions