模仿建表语句,创建subject表,并使用hdfs dfs -put 命令加载数据
hive> create table subject(
> subject_id bigint comment '科目id'
> ,subject_name string comment '科目名称'
> ,subject_score bigint comment '科目总分'
> ) row format delimited fields terminated by ',';
OK
Time taken: 0.063 seconds
hive> load data local inpath '/usr/local/soft/data/subject.txt' into table subject;
Loading data to table test5.subject
Table test5.subject stats: [numFiles=1, totalSize=180]
OK
Time taken: 0.544 seconds
hive> select * from subject;
OK
1000001 语文 150
1000002 数学 150
1000003 英语 150
1000004 政治 100
1000005 历史 100
1000006 物理 100
1000007 化学 100
1000008 地理 100
1000009 生物 100
Time taken: 0.035 seconds, Fetched: 9 row(s)
查询学生分数(输出:学号,姓名,班级,科目id,科目名称,成绩
hive> select t1.id
> ,t1.name
> ,t1.clazz
> ,t2.score_id
> ,t3.subject_name
> ,t2.score
> from students t1
> left join score t2
> on t1.id = t2.id
> left join subject t3
> on t2.score_id = t3.subject_id;
查询学生总分(输出:学号,姓名,班级,总分)
-- 先关联再聚合-- 先聚合再关联
hive> select tt1.id
> ,tt1.name
> ,tt1.clazz
> ,sum(tt1.score) as sum_score
> from (
> select t1.id
> ,t1.name
> ,t1.clazz
> ,t2.score
> from students t1
> left join score t2
> on t1.id = t2.id
> ) tt1 group by tt1.id,tt1.name,tt1.clazz;
hive> select t1.id
> ,t1.name
> ,t1.clazz
> ,t2.sum_score
> from students t1
> left join(
> select id
> ,sum(score) as sum_score
> from score
> group by id
> ) t2 on t1.id = t2.id;
查询全年级总分排名前三(不分文理科)的学生(输出:学号,姓名,班级,总分)
hive> select t1.id
> ,t1.name
> ,t1.clazz
> ,t2.sum_score
> from students t1
> left join(
> select id
> ,sum(score) as sum_score
> from score
> group by id
> ) t2 on t1.id = t2.id
> order by t2.sum_score desc
> limit 3;
查询文科一班学生总分排名前10的学生(输出:学号,姓名,班级,总分)
hive> select t1.id
> ,t1.name
> ,t1.clazz
> ,t2.sum_score
> from (
> select id
> ,name
> ,clazz
> from students
> where clazz = '文科一班'
> ) t1 left join(
> select id
> ,sum(score) as sum_score
> from score
> group by id
> ) t2 on t1.id = t2.id
> order by t2.sum_score desc
> limit 10;
查询每个班级学生总分的平均成绩(输出:班级,平均分)
select tt1.clazz
,round(avg(tt1.sum_score),3) as avg_score
from (
select t1.id
,t1.name
,t1.clazz
,t2.sum_score
from students t1
left join(
select id
,sum(score) as sum_score
from score
group by id
) t2 on t1.id = t2.id
) tt1 group by tt1.clazz;
查询每个班级的最高总分(输出:班级,总分)
hive> select tt1.clazz
> ,max(tt1.sum_score) as max_score
> from (
> select t1.id
> ,t1.name
> ,t1.clazz
> ,t2.sum_score
> from students t1
> left join(
> select id
> ,sum(score) as sum_score
> from score
> group by id
> ) t2 on t1.id = t2.id
> ) tt1 group by tt1.clazz;