hive> select * from student;
# 学生ID 学生姓名 性别 年龄 所在系
# sid sname sex age dept
95002 Aiden female 19 IS
95017 Jacob female 18 IS
95018 Ethan female 19 IS
95013 Matthew male 21 CS
95014 Nicholas female 19 CS
......
hive> select * from course;
# 课程ID 课程名称
# cid cname
1 Chinese
2 Math
3 English
4 Physics
5 Chemistry
6 Biology
hive> select * from sc;
# 学生ID 课程ID 成绩
# sid cid score
95001 1 81
95001 2 85
95001 3 88
95001 4 70
95002 2 90
......
select sid, sname from student;
select distinct sid from sc;
select count(*) from student;
select cid, avg(score) from sc
where cid = '1'
group by cid;
select c.cname, tmp.avg_score from
course c join
(select cid, avg(score) avg_score from sc
group by cid) tmp
on c.cid = tmp.cid;
/*
Chinese 83.66666666666667
Math 88.66666666666667
English 81.46153846153847
Physics 83.125
Chemistry 85.0
Biology 89.45454545454545
*/
select cid, max(score) max_score from sc
where cid = '1'
group by cid;
/*
1 98
*/
select cid, count(distinct sid) count_sid from sc
group by cid;
/*
1 15
2 15
3 13
4 16
5 12
6 11
*/
select sid, count(distinct cid) count_cid from sc
group by sid
having count_cid > 3;
/*
95001 4
95002 4
95004 4
95005 4
95006 6
95007 4
95011 4
95012 4
95013 4
95015 4
95018 4
95019 5
95022 4
*/
select * from student
order by sid;
set mapred.reduce.tasks=2;(默认-1)
select * from student
distribute by sex
sort by age;
/*
95009 Alexande female 18 MA
95017 Jacob female 18 IS
95008 Zachary female 18 CS
95014 Nicholas female 19 CS
95019 Jack female 19 IS
95018 Ethan female 19 IS
95002 Aiden female 19 IS
95007 Jaden female 19 MA
95012 Andrew female 20 CS
95003 Michael female 22 MA
95021 Connor male 17 MA
95005 Tyler male 18 MA
95011 Noah male 18 MA
95015 Jackson male 18 MA
95010 Caden male 19 CS
95004 Ryan male 19 IS
95022 Logan male 20 MA
95001 Caleb male 20 CS
95020 Joshua male 21 IS
95013 Matthew male 21 CS
95016 Brayden male 21 MA
95006 Dylan male 23 CS
*/
select s.sname, c.cname from
student s join sc on s.sid = sc.sid
join course c on c.cid = sc.cid;
/*
Caleb Chinese
Caleb Math
Caleb English
Caleb Physics
Aiden Math
Aiden English
Aiden Physics
Aiden Chemistry
Michael Chinese
Michael English
......
*/
select s.sname, c.cname, sc.score from
student s join sc on s.sid = sc.sid
join course c on c.cid = sc.cid;
/*
Noah Chinese 81
Noah Math 91
Noah English 81
Noah Physics 86
Andrew Chinese 81
Andrew English 78
Andrew Physics 85
Andrew Biology 98
......
*/
select s.sname, sc.cid, sc.score from
student s join sc on s.sid = sc.sid
where sc.cid = '2' and sc.score > 90;
/*
Ryan 2 92
Tyler 2 92
Caden 2 98
Noah 2 91
Nicholas 2 100
Brayden 2 99
Ethan 2 100
Joshua 2 99
Connor 2 93
*/
select s.*, sc.cid from
student s left join sc
on s.sid = sc.sid;
/*
......
95015 Jackson male 18 MA 1
95015 Jackson male 18 MA 3
95015 Jackson male 18 MA 4
95015 Jackson male 18 MA 6
95016 Brayden male 21 MA 1
95016 Brayden male 21 MA 2
95016 Brayden male 21 MA 4
*/
select student.sname, student.dept from student left semi join
(select dept from student where sname = 'Jackson') tmp
on student.dept = tmp.dept;
/*
Michael MA
Tyler MA
Jaden MA
Connor MA
Logan MA
Noah MA
Alexande MA
Jackson MA
Brayden MA
*/