用户名，月份，访问次数

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

use myhive;

create external table if not exists t_access(

uname string comment '用户名',

umonth string comment '月份',

ucount int comment '访问次数'

) comment '用户访问表'

row format delimited fields terminated by ","

location "/hive/t_access"; 

load data local inpath "/home/hadoop/access.txt" into table t_access;

select * from t_access;

--求当月访问次数

create table tmp_access(

name string,

mon string,

num int

); 

insert into table tmp_access

select uname,umonth,sum(ucount)

 from t_access t group by t.uname,t.umonth;

select * from tmp_access;

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 anme,amon,anum,max(bnum) as max_access,sum(bnum) as sum_access

from tmp_view

where amon>=bmon

group by anme,amon,anum;

use myhive;

CREATE TABLE `course` (

  `id` int,

  `sid` int ,

  `course` string,

  `score` int

) ;

// 插入数据

// 字段解释：id, 学号， 课程， 成绩

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;

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 * from tmp_course_view1 where shuxue > yuwen;

2010012325

2010012325表示在2010年01月23日的气温为25度

create table tmp_weather as

select substr(data,1,4) years,substr(data,5,2) months,substr(data,7,2) days,substr(data,9,2) temp from weather;

select * from tmp_weather;

create table tmp_year_weather as

select substr(data,1,4) years,max(substr(data,9,2)) max_temp from weather group by substr(data,1,4);

select * from tmp_year_weather;

select * from tmp_year_weather a join tmp_weather b on a.years=b.years and a.max_temp=b.temp;

id course

1,a

1,b

1,c

1,e

2,a

2,c

2,d

2,f

3,a

3,b

3,c

3,e

create table t_course(id int,course string)

row format delimited fields terminated by ",";

load data local inpath "/home/hadoop/course/course.txt" into table t_course;

id    a    b    c    d    e    f

1     1    1    1    0    1    0

2     1    0    1    1    0    1

3     1    1    1    0    1    0

select collect_set(course) as courses from id_course;

set hive.strict.checks.cartesian.product=false;

create table id_courses as select t1.id as id,t1.course as id_courses,t2.course courses

from

( select id as id,collect_set(course) as course from id_course group by id ) t1

join

(select collect_set(course) as course from id_course) t2;

select id,

case when array_contains(id_courses, courses[]) then 1 else 0 end as a,

case when array_contains(id_courses, courses[]) then 1 else 0 end as b,

case when array_contains(id_courses, courses[]) then 1 else 0 end as c,

case when array_contains(id_courses, courses[]) then 1 else 0 end as d,

case when array_contains(id_courses, courses[]) then 1 else 0 end as e,

case when array_contains(id_courses, courses[]) then 1 else 0 end as f

from id_courses;

a,01,150

a,01,200

b,01,1000

b,01,800

c,01,250

c,01,220

b,01,6000

a,02,2000

a,02,3000

b,02,1000

b,02,1500

c,02,350

c,02,280

a,03,350

a,03,250

use class;

create table t_store(

name string,

months int,

money int

)

row format delimited fields terminated by ",";

load data local inpath "/home/hadoop/store.txt" into table t_store;

create table tmp_store1 as

select name,months,sum(money) as money from t_store group by name,months;

select * from tmp_store1;

create table tmp_store2 as

select a.name aname,a.months amonths,a.money amoney,b.name bname,b.months bmonths,b.money bmoney from tmp_store1 a

join tmp_store1 b on a.name=b.name order by aname,amonths;

select * from tmp_store2;

select aname,amonths,amoney,sum(bmoney) as total from tmp_store2 where amonths >= bmonths group by aname,amonths,amoney;