KEIS BLOGは株式会社ケイズ・ソフトウェアが運営しています。

KEIS BLOG

[Hive][HiveQL]LIMITによる件数指定をグループ毎に行う

LINEで送る
Pocket

グループ化した各グループ単位で、LIMITをかけて上位N件を取り出したい、
というのはそれなりのニーズがあるのではないか、と思います。

この手法を解説するサイトは沢山ありますが、MySQLを対象とするサイトが多いようですね。
今回は、Hiveを使ったこの処理の実現方法を紹介します。

次のようなテーブル(tbl)があり、ユーザーIDとアクセス日時が入っているとします。

userid time
4 2014-04-01
4 2014-04-02
4 2014-04-03
4 2014-04-04
3 2014-04-01
3 2014-04-02
3 2014-04-03
3 2014-04-04
2 2014-04-01
2 2014-04-02
2 2014-04-03
2 2014-04-04
1 2014-04-01
1 2014-04-02
1 2014-04-03
1 2014-04-04

ユーザーIDごとに、最新のアクセス日時上位3件を取り出したい、
というのがやりたい事です。

・Hive version >= 0.11の場合

ROW_NUMBER()を使って書くことができます。

SELECT
  *
FROM(
  SELECT
    *,
    ROW_NUMBER()
      OVER (PARTITION BY userid ORDER BY time DESC)
      AS rownum
  FROM
    tbl
)tbl2
WHERE
  rownum<=3;

結果:
userid time rownum
1 2014-04-04 1
1 2014-04-03 2
1 2014-04-02 3
2 2014-04-04 1
2 2014-04-03 2
2 2014-04-02 3
3 2014-04-04 1
3 2014-04-03 2
3 2014-04-02 3
4 2014-04-04 1
4 2014-04-03 2
4 2014-04-02 3

・Hive version < 0.11の場合

ROW_NUMBER()が使えないので、ユーザー定義関数を作って実現します。
この方法については以下のサイトで解説されています。

http://ragrawal.wordpress.com/2011/11/18/extract-top-n-records-in-each-group-in-hadoophive/

LINEで送る
Pocket