hive实例-网站日志数据分析

数据样例

建表语句

create external table sogou(
date_time string,
uid string,
name string,
num1 int,
num2 int,
ipaddr string)
row format delimited fields terminated by '\t'
location '/sogou';

案例需求

(1)查询输出uid和关键字字段，输出前5条

select uid,name
from sogou
where uid != ''
and name != ''
group by uid,name
order by uid limit5;

(2)查询搜索过’仙剑奇侠传’的用户和keyword

select uid,name
from sogou
where name like '%仙剑奇侠传%'
group by uid,name;

(3)每个用户查询过多少次

select uid,count(uid)
from sogou
where uid != ''
group by uid;

(4)查询频度排名前50的,一个keyword 被查询过多少次，次数的排序

select name,count(name) as cn
from sogou
group by name
order by cn desc limit 50;

(5)搜索过’仙剑奇侠传’的用户还搜索过什么

select m.uid,m.name
from sogou m
join (
select distinct uid
from sogou
where name like'%仙剑奇侠传%') n
on m.uid=n.uid
where m.name not like '%仙剑奇侠传%';