Quoted from SQL:2008
"If CUME_DIST is specified, then the relative rank of a row R is defined as
NP/NR, where NP is defined
to be the number of rows preceding or peer with R in the window ordering of
the window partition of R
and NR is defined to be the number of rows in the window partition of R."
I guess there is a difference between "row_number" and "number of rows preceding or peer with R"
"number of rows preceding or peer with R" == count(*) over (order by salary)
As far as I understand, the following query should calculate cume_dist properly (and it does so in Oracle):
SELECT name,CAST(r AS FLOAT) / c, cd
FROM (SELECT name,
COUNT(*) OVER(ORDER BY salary) as r,
COUNT(*) OVER() AS c,
CUME_DIST() OVER(ORDER BY salary) AS cd
FROM employees
) t;
Sincerely yours,
Vladimir Sitnikov
