前置条件:
安装好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