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

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 のほうがパフォーマンスが良い、とかそういうのあるのだろうか。