准备数据
详见 链接
上传
上传到 hadoop1 的 /program/data/ 目录
上传数据到 HDFS
判断hdfs上sogou_ext目录是否存在，存在则删除，不存在无操作
hadoop fs -test -e /sogou_ext/
目录以存在，执行删除
hadoop fs -rmr /sogou_ext/
HDFS创建 /sogou_ext/20111230 目录
hadoop fs -mkdir -p /sogou_ext/20111230
上传文件到 /sogou_ext/20111230 目录中
hadoop fs -put /azkaban/data/sogou.txt /sogou_ext/20111230
建库建表
如果存在就删除
drop database if exists sogou cascade;
创建数据库
create database sogou;
创建表
创建外部表，并指定hdfs数据存放目录，字段对应数据文件中的字段
CREATE EXTERNAL TABLE sogou.sogou_ext_20111230(ts STRING, uid STRING, keyword STRING, rank INT, `order` INT, url STRING, year INT, month INT, day INT, hour INT ) COMMENT "This is the sogou search data of extend data" ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" STORED AS TEXTFILE LOCATION "/sogou_ext/20111230";
统计分析1
统计数据中条数
select count(*) from sogou.sogou_ext_20111230;
统计非空查询
select count(*) from sogou.sogou_ext_20111230 where keyword is not null and keyword !="";
无重复总条数
无重复总条数（根据ts、uid、keyword、url）
select count(*) from (select ts,uid,keyword,url from sogou.sogou_ext_20111230 group by ts,uid,keyword,url having count(*) =1) a;
统计独立UID总数
select count(distinct(uid)) from sogou.sogou_ext_20111230;
统计分析2
查询关键词长度统计
select avg(a.cnt) from (select size(split(keyword, "\\s+")) as cnt from sogou.sogou_ext_20111230) a;
查询频度排名（频度最高的前50词）
select keyword,count(*) as cnt from sogou.sogou_ext_20111230 group by keyword order by cnt desc limit 50;
UID分析分析
UID的查询次数分布（查询1次的UID个数，…查询N次的UID个数）
select SUM(IF(uids.cnt=1,1,0)),SUM(IF(uids.cnt=2,1,0)),SUM(IF(uids.cnt=3,1,0)),SUM(IF(uids.cnt>3,1,0)) from(select uid,count(*) as cnt from sogou.sogou_ext_20111230 group by uid) uids;
UID平均查询次数
select sum(a.cnt)/count(a.uid) from (select uid,count(*) as cnt from sogou.sogou_ext_20111230 group by uid) a;
查询次数大于2次的用户总数
select count(a.uid) from (select uid,count(*) as cnt from sogou.sogou_ext_20111230 group by uid having cnt > 2) a;
查询次数大于2次的用户占比
select b.bnt/c.dnt from(select count(a.uid) bnt from (select uid,count(*) as cnt from sogou.sogou_ext_20111230 group by uid having cnt > 2)a )b,(select count(distinct (uid)) dnt from sogou.sogou_ext_20111230)c;
查询次数大于2次的数据展示
select b.* from (select uid,count(*) as cnt from sogou.sogou_ext_20111230 group by uid having cnt > 2) a join sogou.sogou_ext_20111230 b on a.uid=b.uid limit 50;
用户行为分析
点击次数与Rank之间的关系分析
Select a.ant/b.bnt from (select count(*) ant from sogou.sogou_ext_20111230 where rank < 11)a,(select count(*) bnt from sogou.sogou_ext_20111230)b;
直接输入URL作为查询词的比例
Select a.ant/b.bnt from (select count(*) ant from sogou.sogou_ext_20111230 where keyword like "%www%")a,(select count(*) bnt from sogou.sogou_ext_20111230)b;
直接输入URL的查询中，点击数点击的结果就是用户输入的URL的网址 所占的比例
Select c.ant/b.bnt from (select SUM(IF(instr(url,keyword)>0,1,0)) ant from (select * from sogou.sogou_ext_20111230 where keyword like "%www%") a)c,( select count(*) bnt from sogou.sogou_ext_20111230 where keyword like "%www%")b;
查询搜索过”仙剑奇侠传“的uid，并且次数大于3
select uid,count(*) as cnt from sogou.sogou_ext_20111230 where keyword="仙剑奇侠传" group by uid having cnt > 3;
查找uid是653d48aa356d5111ac0e59f9fe736429的相关搜索记录
select * from sogou.sogou_ext_20111230 where uid="653d48aa356d5111ac0e59f9fe736429" and keyword like "%仙剑奇侠传%";
查找uid是e11c6273e337c1d1032229f1b2321a75的相关搜索记录
select * from sogou.sogou_ext_20111230 where uid="e11c6273e337c1d1032229f1b2321a75" and keyword like "%仙剑奇侠传%";
uid分析
创建临时表
create table sogou.uid_cnt(uid STRING, cnt INT) COMMENT "This is the sogou search data of one day" ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" STORED AS TEXTFILE;
查询并插入
INSERT OVERWRITE TABLE sogou.uid_cnt select uid,count(*) as cnt from sogou.sogou_ext_20111230 group by uid;