hive day04

rownumber() 和 over()函数

row_number() over()分组排序功能：

vi score.txt
姓名,学科id,分数
zhangsan,1,90
zhangsan,2,95
zhangsan,3,68
lisi,1,88
lisi,2,95
将以上数据求每个人的前两高的分数

建表

hive> create table t_score(name string,kcid string,score int) row format delimited fields terminated by ',';

导入数据

hive> load data local inpath '/home/score.txt' into table t_score;

以名字为分区,按score排序

hive> select *,row_number() over(partition by name order by score desc) from t_score;

取出每个人的前两项

hive>select name,kcid,srore from (select *,row_number() over(partition by name order by score desc) as rank from t_score) tmp where rank<3;

注:在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

分桶表

开启hive的桶表功能

set hive.enforce.bucketing=true;

设置reduce的个数

set mapreduce.job.reduces=3;

创建桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

因为桶表数据的加载通过hdfs,dfs,-put文件或者通过load,data都不好使,所以只能通过insert overwrite创建普通表,并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表中.

创建普通表

create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

加载数据到普通表

load data local inpath '/home/course.csv' into table course_common;

从普通表中加载数据

insert overwrite course select * from course_common;

GROUP BY语句

创建分区表语法

create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

加载数据到分区表中

load data local inpath '/home/score.csv' into table score partition (month='201909');

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

（1）计算每个学生的平均分数

	select s_id ,avg(s_score) from score group by s_id;

注意group by的字段，必须是select后面的字段，select后面的字段不能比group by的字段多

（2）计算每个学生最高成绩

	select s_id ,max(s_score) from score group by s_id;

HAVING语句

1）having与where不同点

（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。

求每个学生的平均分数

select s_id ,avg(s_score) from score group by s_id;

求每个学生平均分数大于85的人

select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

JOIN语句

等值JOIN
老版本中，不支持非等值的join
在新版中：1.2.0后，都支持非等值join，不过写法应该如下：

查询分数对应的姓名

创建学生表老师表：

create table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';
create table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';

加载数据

load data local inpath '/home/teacher.csv' into table teacher;
load data local inpath '/home/student.csv' into table student;

表连接

select s.s_id,s.s_score,stu.s_name,stu.s_birth  from score s left join student stu on s.s_id = stu.s_id;

表的别名
1）好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。

合并老师与课程表

select * from teacher t join course c on t.t_id = c.t_id;
内连接（INNER JOIN）
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select * from teacher t inner join course c on t.t_id = c.t_id;
左外连接（LEFT OUTER JOIN）
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
查询老师对应的课程
select * from teacher t left join course c on t.t_id = c.t_id;

右外连接（RIGHT OUTER JOIN）
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select * from teacher t right join course c on t.t_id = c.t_id;
满外连接（FULL OUTER JOIN）
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
SELECT * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;

多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生

设置表头：

set hive.cli.print.header=true;

多表连接

select * from teacher t 
left join course c 
on t.t_id = c.t_id
left join score s 
on s.c_id = c.c_id
left join student stu 
on s.s_id = stu.s_id;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表teacher和表course进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score;进行连接操作。