hive使用rank实现topN的查询

前置条件：
安装好hive

背景：
hive表中存有cookieid和time两个字段
目标：
通过rank方法查出每个cookieid在哪一天的次数最多

具体步骤如下：
一、创建表

CREATE EXTERNAL TABLE tmp_dh_topN (
cookieid string,
vtime string –day
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
stored as textfile location ‘/tmp/dh/tmp_dh_topN/’;

1
2
3
4
5
6

二、导入数据到表

topN的数据链接： http://download.csdn.net/detail/hua245942641/9355221
数据文件存放在/tmp目录下，把数据导入hive的tmp_dh_topN表中

load data local inpath ‘/tmp/tmp_topN.txt’ into table tmp_dh_topN;

1

三、具体查询步骤
3.1 group by计算每个cookie在每天的总数

SELECT cookieid, vtime, COUNT(vtime) AS pv
FROM tmp_dh_topN
GROUP BY cookieid, vtime
ORDER BY cookieid, vtime

1
2
3
4

得到结果如下：

cookie1 2015-04-10 3
cookie1 2015-04-11 3
cookie1 2015-04-12 3
cookie1 2015-04-13 2
cookie1 2015-04-14 2
cookie1 2015-04-15 1
cookie2 2015-04-10 3
cookie2 2015-04-11 3
cookie2 2015-04-12 3
cookie2 2015-04-13 2
cookie2 2015-04-14 2
cookie2 2015-04-15 1

1
2
3
4
5
6
7
8
9
10
11
12

3.2 使用RANK把pv进行排序

SELECT cookieid, vtime, pv, RANK() OVER (PARTITION BY cookieid ORDER BY pv DESC, vtime DESC) as pv_rank FROM (
SELECT cookieid, vtime, COUNT(vtime) AS pv
FROM tmp_dh_topN
GROUP BY cookieid, vtime
ORDER BY cookieid, vtime
) tmp_dh_pv

1
2
3
4
5
6

使用rank方法能把结果进行排序，over中的partition是进行排序分区，order by是在内部排序时的条件。这里的意思是在每个cookieid分组在按照pv、vtime来进行排序，最后得出结果。
得到结果如下：

cookie1 2015-04-12 3 1
cookie1 2015-04-11 3 2
cookie1 2015-04-10 3 3
cookie1 2015-04-14 2 4
cookie1 2015-04-13 2 5
cookie1 2015-04-15 1 6
cookie2 2015-04-12 3 1
cookie2 2015-04-11 3 2
cookie2 2015-04-10 3 3
cookie2 2015-04-14 2 4
cookie2 2015-04-13 2 5
cookie2 2015-04-15 1 6

1
2
3
4
5
6
7
8
9
10
11
12

3.3、得到pv_rank为1的行

SELECT cookieid, vtime, pv FROM (
SELECT cookieid, vtime, pv, RANK() OVER (PARTITION BY cookieid ORDER BY pv DESC, vtime DESC) as pv_rank FROM (
SELECT cookieid, vtime, COUNT(vtime) AS pv
FROM tmp_dh_topN
GROUP BY cookieid, vtime
ORDER BY cookieid, vtime
) tmp_dh_pv
)
WHERE pv_rank = 1;

1
2
3
4
5
6
7
8
9

最后一步，去pv_rank为1就是得到次数最多的那个记录。
得到的结果如下：

cookie1 2015-04-12 3
cookie2 2015-04-12 3