前回の記事の続きです。
参考: Prestoで行番号や順位を振る関数まとめ
Presto(トレジャーデータ)で行番号を振る3つの関数を紹介したのですが、ドキュメントの同じページの同じセクションには、これら3つの関数以外にも複数の関数が紹介されています。
実は僕はこれらを使ったことがなくて、ドキュメントをみて初めて存在を知ったので、この機会に挙動を確認しておこうと思いました。
参考: Window Functions の Ranking Functionsのセクション
一つ目は CUME_DIST() です。
ドキュメントによると、値の累積分布を返す関数だとあります。各行について、その行より前の行か同じ値の行の数をグループ内の行数で割った値を返すそうです。前の行が小さい値を指すのか、大きい値を指すのかは、ORDER BY で指定した昇順(ASC)/降順(DESC)によって決まります。
例えば、値が 10, 20, 20, 40 ならば、 0.25, 0.75, 0.75, 1 を返してくるイメージですね。
一瞬、RANK()を行数で割った値のことか？と勘違いしたのですが、RANK()は1,2,2,4 なので、これを行数(4)で割ると0.25, 0.5, 0.5, 1 なので少し違います。
少し多めに8個の値で実行してみたのが次の例です。
比較用にRANK() を8で割った結果も一緒につけました。
結果をシンプルにするためにこの例では使いませんでしたが、もちろんOVERの中でPARTITION BY を使ってグループごとに算出することもできますよ。
WITH
sample_table AS (
SELECT
id,
value
FROM
UNNEST(
ARRAY[1, 2, 3, 4, 5, 6, 7, 8],
ARRAY[20, 10, 20, 30, 15, 30, 25, 40]
) AS t(
id,
value
)
)
SELECT
id,
value,
CUME_DIST() OVER (ORDER BY value) AS cume_dist_,
CAST(RANK() OVER (ORDER BY value) AS DOUBLE)/8 AS rank_norm
FROM
sample_table
-- 以下出力結果
id, value, cume_dist_, rank_norm
2, 10, 0.125, 0.125
5, 15, 0.25, 0.25
1, 20, 0.5, 0.375
3, 20, 0.5, 0.375
7, 25, 0.625, 0.625
4, 30, 0.875, 0.75
6, 30, 0.875, 0.75
8, 40, 1.0, 1.0CUME_DIST と RANK(を行数で割った値)を比較すると、同じ値になる行もあれば、複数行で同じ値になっている行については結果が異なることも見て取れますね。
次の関数は、NTILE です。これは NTILE(n) OVER(〜) のように、バケット数n を指定して使います。これは、PARTITION BYで区切られた各グループの行たちを、さらにそれぞれn個のバケットに分割して、バケット番号を振るものです。8行のデータに対して、ntile(4)を実行したら、1,1,2,2,3,3,4,4 と番号を振る感じですね。元の行数がバケット数でちょうど割り切れない場合は、最初の方のバケットに優先的に割り当てられます。つまり、7行のデータを4バケットに割ったら、1〜3番目のバケットに2個、4番目のバケットに1個割り当てられて、1,1,2,2,3,3,4 となります。
実際にやってみます。(この例でもPARTITION BY使ってませんが、実際は使えます。)
WITH
sample_table AS (
SELECT
id,
value
FROM
UNNEST(
ARRAY[1, 2, 3, 4, 5, 6, 7],
ARRAY[20, 10, 20, 30, 35, 30, 25]
) AS t(
id,
value
)
)
SELECT
id,
value,
NTILE(4) OVER (ORDER BY value) AS ntile_
FROM
sample_table
-- 以下出力結果
id, value, ntile_
2, 10, 1
3, 20, 1
1, 20, 2
7, 25, 2
4, 30, 3
6, 30, 3
5, 35, 4想定通りの結果が出ましたね。今回の例では意図的に、バケットの境目になる2番目と3番目(id 1と3)のvalue を同じ20にしていたのですが、一方がバケット番号1でもう一方は2と振り分けられました。値が同じことよりも各バケットに規定の個数振り分けることが優先されている動きになりました。
この記事で最後に紹介するのは、 PERCENT_RANK() です。
その行のRANK() の結果を $r$、そのグループの行数を$n$とすると、$(r-1)/(n-1)$を返す、というドキュメント通りの説明が一番わかりやすいと思います。
そのままなので、やってみます。(先の2関数と同じように、PERCENT_RANKも本当はPARTITION BYが使えます。) 比較用にRANK() の結果もつけました。
WITH
sample_table AS (
SELECT
id,
value
FROM
UNNEST(
ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9],
ARRAY[20, 10, 20, 30, 35, 30, 25, 15, 40]
) AS t(
id,
value
)
)
SELECT
id,
value,
RANK() OVER (ORDER BY value) AS rank_,
PERCENT_RANK() OVER (ORDER BY value) AS percent_rank_
FROM
sample_table
-- 以下出力結果
id, value, rank_, percent_rank_
2, 10, 1, 0.0
8, 15, 2, 0.125
3, 20, 3, 0.25
1, 20, 3, 0.25
7, 25, 5, 0.5
4, 30, 6, 0.625
6, 30, 6, 0.625
5, 35, 8, 0.875
9, 40, 9, 1.00〜1 までの値を振ってくれるので個人的には結構好みの結果です。
さて、(そんな利用例は滅多にないと思いますが、)元々抽出されたレコードが1件だたり、PARTITION BY で区切られたグループ内のデータの行数が1件だけだった場合、
$r=1, n=1$ なので、$(r-1)/(n-1)=0/0$となり、$0$除算のエラーにより結果はNULLになるんじゃないか、と思ってたのですが、どうやらこの場合は$0$を返してくるようです。
NULL考慮をしなくていいので楽ですね。(ちなみにこれは、トレジャーデータで試した場合の挙動の話です。もしかしたら他のPresto環境やDBMSの種類によっては違う動きになるかもしれません。)