Hive的经典案例

数据字段：用户名、月份、访问次数
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,1

create external table month(
      uname string,
      umonth string,
      ucount int
) row format delimited fields terminated by "," 
location "/user/month"; #创建数据表
load data local inpath "/usr/local/month" into table month;#导入数据

create table tmp_access(
    name string,
    mon string,
    num int
); #创建临时表，用来存储临时结果集
insert into table tmp_access 
select uname,umonth,sum(ucount)
 from month t group by t.uname,t.umonth; #将临时结果集加载到临时表中
 
 
 #创建自连接视图
create view tmp_view as 
select a.name anme,a.mon amon,a.num anum,b.name bname,b.mon bmon,b.num bnum from tmp_access a join tmp_access b on a.name=b.name;
#检索视图
select * from tmp_view;

select uname,umonth,
max(num) over(partition by uname order by umonth) maxnum,
sum(num) over(partition by uname order by umonth) sumnum
from (
select uname,umonth,sum(ucount) num
from month
group by uname,umonth) temp;

create table `course` (
   `id` int,
   `sid` int ,
   `course` string,
   `score` int 
) ; #创建数据表
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65); #插入测试数据

create view tmp_course_view as
 select sid, case course when "shuxue" then score else 0 end  as shuxue,  
 case course when "yuwen" then score else 0 end  as yuwen from course;#行转列语句


select * from tmp_course_view; #检索视图结果




以sid分组聚合后获取各成绩的最大值
create view tmp_course_view1 as
select aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid; 
#获取各个成绩的最高分 
select * from tmp_course_view1;
#检索视图结果

select sid,max(yuwen) yuwen_socre,max(shuxue) shuxue_score
from(
select sid,
case when course='yuwen' then score else 0 end yuwen,
case when course='shuxue' then score else 0 end shuxue
from course) temp
group by sid
having yuwen_socre<=shuxue_score;