(10)Hive的查询运算,分组,join语句

原创

wx5ba7ab4695f27 2021-05-31 18:45:09 ©著作权

©著作权归作者所有：来自51CTO博客作者wx5ba7ab4695f27的原创作品，请联系作者获取转载授权，否则将追究法律责任

查询语句语法：

[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available
starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

全表和特定列查询

创建部门表

create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

创建员工表

create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

导入数据
1．全表查询

hive (default)> select * from emp;

选择特定列查询

hive (default)> select empno, ename from emp;

注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

列别名

1．重命名一个列
2．便于计算
3．紧跟列名，或列名和别名之间加入关键字‘AS’
4．案例实操
查询名称和部门

hive (default)> select ename AS name, deptno dn from emp;

算术运算符

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A\|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

查询出所有员工的薪水后加1显示。

hive (default)> select sal +1 from emp;

常用函数

1．求总行数（count）

hive (default)> select count(*) cnt from emp;

2．求工资的最大值（max）

hive (default)> select max(sal) max_sal from emp;

3．求工资的最小值（min）

hive (default)> select min(sal) min_sal from emp;

4．求工资的总和（sum）

hive (default)> select sum(sal) sum_sal from emp;

5．求工资的平均值（avg）

hive (default)> select avg(sal) avg_sal from emp;

Limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

hive (default)> select * from emp limit 5;

Where语句

1．使用WHERE子句，将不满足条件的行过滤掉
2．WHERE子句紧随FROM子句
3．案例实操
查询出薪水大于1000的所有员工

hive (default)> select * from emp where sal >1000;

注意：where子句中不能使用字段别名。

比较运算符（Between/In/ Is Null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

2）案例实操
（1）查询出薪水等于5000的所有员工

hive (default)> select * from emp where sal =5000;

（2）查询工资在500到1000的员工信息

hive (default)> select * from emp where sal between 500 and 1000;

（3）查询comm为空的所有员工信息

hive (default)> select * from emp where comm is null;

（4）查询工资是1500或5000的员工信息

hive (default)> select * from emp where sal IN (1500, 5000);

Like和RLike

1）使用LI KE运算选择类似的值
2）选择条件可以包含字符或数字
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4）案例实操
查询以2开头薪水的员工信息

select * from emp where sal like '2%'

查询第二个数值是2的薪水的员工信息

select * from emp where sal LIKE '_%2';

查找薪水中含有2的员工信息

select * from emp where sal RLIKE '[2]';

逻辑运算符（And/Or/Not）

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

查询薪水大于1000，部门是30

select * from emp where sal>1000 and deptno=30;

查询薪水大于1000或者部门是30

select * from emp where sal>1000 or deptno =30;

查询除了20和30部门号的员工信息

select * from emp where deptno not in (30,20);

分组

Group By语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例实操：
计算emp表每个部门的平均工资

select t.deptno,avg(t.sal) avg_sal from emp t group by t.deptno;

计算emp每个部门中每个岗位的最高薪水

select t.deptno, t.job,max(t.sal) max_sal from emp t group by t.deptno, t.job;

Having语句

1．having与where不同点
（1）where后面不能写分组函数，而having后面可以使用分组函数。
（2）having只用于group by分组统计语句。
求每个部门的平均薪水大于2000的部门
1.先求每个部门平均工资

select deptno, avg(sal) from emp group by deptno

2.每个部门平均工资大于2000的部门

select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

Join语句

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
案例:
根据员工表和部门表中部门编号相等,查询员工编号,员工名称和部门名称

select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

表的别名

1．好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
合并员工表和部门表

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
 = d.deptno;

内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
 = d.deptno;

左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。左表为准

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。右表为准

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

满外连接

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno
 = d.deptno;

多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
**注意：**为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
**优化：**当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

具体差异可以查看:
https://blog.csdn.net/weixin_39220472/article/details/81193617

笛卡尔积

产生条件:

（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

案例

hive (default)> select empno, dname from emp, dept;

注意:
hive join目前不支持在on子句中使用谓词or

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno or e.ename=d.ename;   错误的

上一篇：(11)Hive的查询排序和分桶

下一篇：(9)Hive的DML数据操作

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯