The EDB Blog
2017年11月14日

私がPostgres Window Magicを研究している最中、percent_rankとcumm_distの異常な動作を調べました累積分布)。それに関しては、Postgresのドキュメントは役に立ちませんでした。私は最終的にPostgres 10のドキュメントにこの段落を追加するという答えを得ました:

cume_distは、現在の行とそのピア以下のパーティション行の割合を計算し、percent_rankは現在の行がパーティションに存在しないと仮定して、現在の行よりも小さいパーティション行の割合を計算します。

そこには、いくつかの重要なフレーズがあります。まず、cume_distは「同じかそれより小さい」行の割合を計算し、percent_rankは現在の行「より小さい」類似率を計算しますが、それは、現在の行がパーティション内にないことを前提としています。

現在の行をカウントしないという最後の問題が、私は変だと思いました。このことについて、私は何人かの統計学者に話を聞き、いくつかウェブ検索をしました。もっともよくわかっているのは、この特別な percent_rank 反応は、パーティションに最大値がある場合、それをうまく処理するよう設計されているということです。例えば:

SELECT x, (PERCENT_RANK() OVER w)::numeric(10, 2),
       (CUME_DIST() OVER w)::numeric(10, 2)
FROM generate_series(1, 5) AS f(x)
WINDOW w AS (ORDER BY x);
x | percent_rank | cume_dist
---+--------------+-----------
1 |         0.00 |      0.20
2 |         0.25 |      0.40
3 |         0.50 |      0.60
4 |         0.75 |      0.80
5 |         1.00 |      1.00

In this example, what are the ideal ratios for the last line? Cume_dist is easy since is it "less than or equal" to the current row, so that is clearly 1.00. What about percent_rank, which is "less than?" Without the "assume the current row isn't in the partition" exclusion, it would show 0.80. That would be a silly answer since all the rows are less than the last row, except the last row itself. I think this is why that exclusion was added.

This issue becomes even more complex when there are many duplicates. For example, imagine this data set:

WITH cte (x) AS (
        SELECT 0
        UNION ALL
        SELECT 1 FROM generate_series(1, 5)
        UNION ALL
        SELECT 2
)
SELECT x, (PERCENT_RANK() OVER w)::numeric(10, 2),
       (CUME_DIST() OVER w)::numeric(10, 2)
FROM cte
WINDOW w AS (ORDER BY x);
x | percent_rank | cume_dist
---+--------------+-----------
0 |         0.00 |      0.14
1 |         0.17 |      0.86
1 |         0.17 |      0.86
1 |         0.17 |      0.86
1 |         0.17 |      0.86
1 |         0.17 |      0.86
2 |         1.00 |      1.00

Frankly, neither percent_rank nor cume_dist look like they produce reasonable results. If I got a score of 1, is saying that a ratio of 0.17 rows are less than mine, or that 0.86 are equal or less than mine, useful? Imagine this was an sat test and many people got 650. Wouldn't it be reasonable to count half of the people who had matching scores less then or equal to mine, which would compute 0.50, rather than including all or none of the matching scores?

Right now, there is no way in Postgres to do that, but I think it would be a nice thing to add. I assume a server-side function could be written to do this. Computing the average of percent_rank and cume_dist would yield a similar result.

Bruce Momjian氏は、EnterpriseDBの上級データベース設計者です。 

This post originally appeared on his personal blog.

 

 

bruce.momjian's picture

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business...