2014/12/07

PostgreSQL 9.4 で最頻値とパーセンタイル

[PostgreSQL]
2014/12/04 に続き PostgreSQL 9.4 RC1 を使って新しいクエリを試します。新しい集約関数のうち最頻値を出す mode と、任意のパーセンタイル(分位数)を出す percentile_cont の例です。OS は Windows 7 32bit。下がドキュメントの当該箇所。

■ PostgreSQL 9.4rc1 Documentation : Ordered-Set Aggregate Functions


↓ mode の例は PostgreSQL Wiki にもあります。また下記にある percentile_disc は離散的なパーセンタイルで(値と値の間を内挿補間しない)、別記事で取り上げる予定。disc は discrete の略です。今日使う percentile_cont は continuous つまり連続値。

■ PostgreSQL Wiki : What's new in PostgreSQL 9.4 : Ordered-set aggregates


さて、mode と percentile_cont は普通の集約関数と別の Order-set Aggregate Functions というカテゴリに位置し、使い方も少し違います。普通の集約関数はデータの並び順に関係なく同じ結果が出ますが、最頻値やパーセンタイルはそうと限りません(後方の例を参照)。この点が、Order-set という新しいカテゴリ名および関数の使い方に反映しています。

↓ サンプルデータとして 2014/12/04 に unnest … WITH ORDINALITY で作った3群の表を、適当なテーブルにして使います。(今日は "201412"."07_sample" に保存)

SELECT * FROM unnest(
ARRAY[145,110,121,108,133,167,125,167,141]
, ARRAY[143,134,154,122,156,175,128,143]
, ARRAY[123,178,156,188,198,134,165,182,144,167]
) WITH ORDINALITY foo(grp_a, grp_b, grp_c, n) ;


↓ 最初に普通の集約関数の例。count と sum を使って各群の個数と合計を出しました。NULL は自動的に除外されます。


次に mode を試しますが、実データでは最頻値が複数存在する場合があります。その時の mode の結果を確認するため、テーブルの一部を修正して複数の最頻値を発生させます。↓

UPDATE "201412"."07_sample"
SET grp_a = 145, grp_b = 154
WHERE n = 2 ;

SELECT * FROM "201412"."07_sample" ORDER BY n ;


↓ mode の使用例。mode 直後の括弧内は何も書かず、続く WITHIN GROUP (ORDER BY …) で対象の列を指定するという、ちょっと意外な書き方です。最頻値が一つなら ORDER BY の順序は関係なく、最頻値が複数ある場合は ORDER BY の順で最初に来る値が返ります。つまり昇順なら「最小の最頻値」、降順なら「最大の最頻値」です。

SELECT mode() WITHIN GROUP (ORDER BY grp_a)
, mode() WITHIN GROUP (ORDER BY grp_b)
, mode() WITHIN GROUP (ORDER BY grp_c)
FROM "201412"."07_sample" ;

-- // 並び順を逆転 -> 複数の最頻値がある場合、そのうち最大の値が返る
SELECT mode() WITHIN GROUP (ORDER BY grp_a DESC)
, mode() WITHIN GROUP (ORDER BY grp_b DESC)
, mode() WITHIN GROUP (ORDER BY grp_c DESC)
FROM "201412"."07_sample" ;



上の例で grp_c 列はどの値も頻度が等しく1なので、ORDER BY が昇順なら全体の最小値、順順なら最大値と同じになります。

このように最頻値は「最頻値が複数ある時の方針」によって結果が異なる点が、普通の集約関数と違います。この方針の一つとして最小または最大の値を取ることが考えられ、そのどちらかを ORDER BY のソート順で指定できるわけです。

最頻値はデータの出現頻度だけを見るので、数値以外にも適用可能です。この mode 関数も、ソート順を指定できるデータ型なら何でも対象にできます。その例は機会があれば別記事で。

↓ 次に percentile_cont の例で、簡単のため列 grp_c だけを対象にします。例えば中央値なら percentile_cont(0.5) WITHIN GROUP (ORDER BY 対象列)と書きます。普通パーセンタイルと言えば昇順に並べた時に小さい方から数えたパーセント点に当たる値ですが、場合によっては降順で並べる(昇順で並べて上から数える)ことがあるかもしれません。その際は ORDER BY を降順にして対応できます。下の75%値がその例です。

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY grp_c) median
-- // 中央値

, percentile_cont(0.5) WITHIN GROUP (ORDER BY grp_c DESC) median
-- // 中央値は ORDER BY 無関係

, percentile_cont(0.25) WITHIN GROUP (ORDER BY grp_c) pct25
-- // 25%値

, percentile_cont(0.25) WITHIN GROUP (ORDER BY grp_c DESC) pct75
-- // データを降順に並べた時の25%値 = 75%値

, percentile_cont(0.75) WITHIN GROUP (ORDER BY grp_c) pct75
-- // 75%値

, percentile_cont(0.76) WITHIN GROUP (ORDER BY grp_c) pct76
-- // 76%値

, percentile_cont(0.77) WITHIN GROUP (ORDER BY grp_c) pct77
-- // 77%値

FROM "201412"."07_sample" ;


上で75〜77%値はいずれも元データの178〜182の間にあり、パーセントに応じて内挿補間されているのが分かります。バーセンタイルの計算方法の中には「データ間の値はすべて真ん中を取る」のもありますが(詳細 : Wikipedia)この percentile_cont は違います。

↓ より実用的に、複数のパーセントを一度に渡すことができます。下は第1・2・3四分位数を得る例。複数のパーセントを配列で渡し、結果も配列になります。

SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) -- // 四分位点を指定
WITHIN GROUP (ORDER BY grp_c)
FROM "201412"."07_sample" ;


↓ 最後に、パーセントの配列を他所から持ってきて結合する想定の例。percentile_cont が集約関数なので、パーセント配列を GROUP BY の対象にする必要があります。

WITH a (pct) AS (
-- // 四分位点, 10%点, 90%点
VALUES (ARRAY[0.1, 0.25, 0.5, 0.75, 0.9])
)
SELECT percentile_cont(pct) WITHIN GROUP (ORDER BY grp_c)
FROM a, "201412"."07_sample"
GROUP BY pct ;

×

この広告は180日以上新しい記事の投稿がないブログに表示されております。