Re: Windowing Function Patch Review -> Standard Conformance
| От | Vladimir Sitnikov |
|---|---|
| Тема | Re: Windowing Function Patch Review -> Standard Conformance |
| Дата | |
| Msg-id | 1d709ecc0811041702m7fb8f515gfefb357e28585a63@mail.gmail.com обсуждение исходный текст |
| Ответ на | Windowing Function Patch Review -> Standard Conformance ("David Rowley" <dgrowley@gmail.com>) |
| Ответы |
Re: Windowing Function Patch Review -> Standard Conformance
|
| Список | pgsql-hackers |
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
"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
В списке pgsql-hackers по дате отправления: