Edited at

Presto, Hive の「行に番号を振る」関数のまとめ

More than 1 year has passed since last update.

Presto, Hive にある行番号や順位を振る関数の違いがややこしくて覚えられないのでまとめておく。

ちなみに「どれが速いか」についてはわかっていないので、わかる方がいたらコメント頂けると嬉しい。


Presto

WITH tbl AS (

SELECT val
FROM (SELECT ARRAY[100, 200, 200, 300] AS arr) AS t
CROSS JOIN UNNEST(arr) AS t(val)
)
SELECT
val,
RANK() OVER (ORDER BY val),
DENSE_RANK() OVER (ORDER BY val),
ROW_NUMBER() OVER (ORDER BY val)
FROM tbl

RANK
DENSE_RANK
ROW_NUMBER

100
1
1
1

200
2
2
2

200
2
2
3

300
4
3
4


  • 同じの値があった場合、RANK は次の順位を飛ばすが DENSE_RANK は飛ばさない


  • ROW_NUMBER は同じの値でも違う番号を振る


Hive

Hive 0.11 以前は ROW_NUMBER が使えなかったが、最近の Hive では使えるので Presto と同じように使うことが出来る。

WITH tbl AS (

SELECT EXPLODE(ARRAY(100, 200, 200, 300)) AS val
)
SELECT
val,
RANK() OVER (ORDER BY val),
DENSE_RANK() OVER (ORDER BY val),
ROW_NUMBER() OVER (ORDER BY val)
FROM tbl

RANK
DENSE_RANK
ROW_NUMBER

100
1
1
1

200
2
2
2

200
2
2
3

300
4
3
4

また、Treasure Data の Hive には TD_X_RANK という UDF がある。

WITH tbl AS (

SELECT "a" AS key, val
FROM (SELECT EXPLODE(ARRAY(100, 200, 200, 300)) AS val) AS t
ORDER BY key
)
SELECT
key,
val,
TD_X_RANK(key)
FROM tbl

key
val
TD_X_RANK

a
300
1

a
200
2

a
200
3

a
100
4


  • サブクエリであらかじめ ORDER BY key もしくは CLUSTER BY key しておく必要がある


  • key ごとに区切って行番号が振られる



    • ROW_NUMBER() OVER (PARTITION BY key) と同じ



今の Hive は ROW_NUMBER があるのでそっちを使えばいいのでは?と思うのだけど、 TD_X_RANK のほうがパフォーマンスが良い、とかそういうのあるのだろうか。