【博学谷学习记录】超强总结，用心分享 | hive 查询、分组、join

【博学谷IT技术支持】

查询

常用函数

• 常用函数

--- 常用函数
-- 求总行数（count）
select count(1) from score; -- 36
-- 求分数的最大值（max）
select max(sscore) from score; --- 99
-- 求分数的最小值（min）
select min(sscore) from score; -- 20
-- 求分数的总和（sum）
select sum(sscore) from score; --- 2510
-- 求分数的平均值（avg）
select avg(sscore) from score; --- 69.72222222222223

• LIMIT, where, between and, is null, in

-- LIMIT语句
select * from score limit 3, 5; -- 从索引为3的行开始，显示5行
--- where 将不满足条件的行过滤掉
select * from score where sscore> 60;
-- 查询分数在80到90的所有数据
select * from score where sscore between 80 and 90;
--- 查询成绩为空的所有数据
select * from score where sscore is null;
-- 查询成绩是80或 90的数据
select * from score where sscore in (80, 90);

• like,rlike, not in

--- 查看开头是8的所有成绩
select * from score where sscore like '8%';
--- 查看第二个数据为9的成绩
select * from score where sscore like '_9%';
-- 查找id中含1的所有成绩信息
select * from score where sscore rlike '[1]';
desc score;
-- 查询成绩大于80，并且sid是01的数据
select * from score where sscore > 80 and sid='01';
-- 查询sid  不是 01和02的学生
select * from score where sid not in (01, 02);

• group by

--- group by

-- 计算每个学生的平均分数
select sid, avg(sscore) from score group by sid;
-- HAVING语句
-- having与where不同点
-- （1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
-- （2）where后面不能写分组函数，而having后面可以使用分组函数。
-- （3）having只用于group by分组统计语句。

-- 求每个学生的平均分数
select sid, avg(sscore) from score group by sid;
-- 求每个学生平均分数大于85的人
select sid, avg(sscore) avgscore from score group by sid having avgscore > 85;

join语句

-- JOIN语句
-- 内连接（INNER JOIN）
select * from teacher t, course c where t.tid = c.tid; -- 隐式内连接
select * from teacher t join course c on t.tid = c.tid; -- 显式内连接
select * from teacher t inner join course c on t.tid = c.tid; -- 显式内连接

--- 左外连接（LEFT OUTER JOIN）
-- 左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
-- 查询老师对应的课程
select * from teacher t left join course c on t.tid = c.tid;
-- 右外连接（RIGHT OUTER JOIN）
-- 右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select * from course c right join teacher t on c.tid = t.tid;
-- 满外连接（FULL OUTER JOIN）
-- 满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select * from teacher t Full Join course c on t.tid = c.tid;

排序

• order by

--- order by
-- Order By：全局排序，一个reduce
-- 查询学生的成绩，并按照分数降序排列
select * from student s left join score sco on s.sid = sco.sid order by sscore desc ;
-- 按照分数的平均值排序
select sid, cid, avg(sscore) avg from score group by sid, cid order by avg;

• Sort By-每个MapReduce内部局部排序

-- Sort By-每个MapReduce内部局部排序
-- Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。
-- 设置reduce个数
set mapreduce.job.reduces=3;
-- 查询成绩按照成绩降序排列
select * from score sort by sscore;
-- 将查询结果导入到文件中（按照成绩降序排列）
insert overwrite local directory '/export/data/hivedatas/sort'
select * from score sort by sscore;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cQrwMga9-1667291463952)(https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/69457d7f032f4373824eb08261b36ad4~tplv-k3u1fbpfcp-watermark.image?)]

• Distribute By-分区排序

-- Distribute By-分区排序
-- Distribute By：类似MR中partition，进行分区，结合sort by使用。
-- 注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
set mapreduce.job.reduces=7;
-- 通过distribute by进行数据的分区
insert overwrite local directory '/export/data/hivedatas/distribute'
select * from score distribute by sid sort by sscore;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xBaVtC7d-1667291463954)(https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/76b57734f2c348f2a8fd9bef4b7841b2~tplv-k3u1fbpfcp-watermark.image?)]

• Cluster By

-- Cluster By
-- 当distribute by和sort by字段相同时，可以使用cluster by方式。
-- cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
select * from score cluster by sid; 
select * from score distribute by sid sort by sid;