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

By : mcUser
Source: Stackoverflow.com
Question!

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.

By : mcUser


Answers
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
By : Quassnoi


This video can help you solving your question :)
By: admin