HiveDML数据操作之查询

一、常用函数

总行数

select count(*) from emp;

最大值

select max(id) from emp;

最小值

select min(id) from emp;

总和

select sum(id) from emp;

平均值

select avg(id) from emp;

二、案例实操

2.1 比较运算符

查出薪水大于1000的虽有员工emp

select * from emp where sal > 1000;

查出薪水等于1000的员工

select * from emp where sal = 1000;

查询工资在1000到5000的员工

select * from emp where sal between 1000 and 5000;

查询comm为空的所有员工

select * from emp where comm is null;

查询工资是1000或者5000的员工

select * from emp where sal in (1000, 5000);

2.2 Like RLike

查询薪资是以2开头的员工

select * from emp where sal like '2%';

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

select * from emp where sal like '_2%';

查询薪水中含有2的员工信息 (rlike是Hive的一个扩展功能，可以通过正则表达式来指定匹配条件)

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

2.3 Group By

计算各个部门的平均工资

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

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

select deptno, job, max(sql) from emp group by deptno, job;

2.4 Having

1.having 与 where 不同点
(1)where 针对表中的列发挥作用，查询数据;having 针对查询结果中的列发挥作用， 筛选数据。
(2)where 后面不能写聚合函数，而 having 后面可以使用聚合函数。 (3)having 只用于 group by 分组统计语句。
计算平均薪资大于2000的部门

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

2.5 JOIN

Join 连接谓词中不支持 or
左连接

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

右连接

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

内连接

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

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

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

笛卡尔积
1.笛卡尔集会在下面条件下产生
(1)省略连接条件 (2)连接条件无效 (3)所有表中的所有行互相连接

select empno, dname from emp, dept;

2.6 排序

• Order By: 全局排序，一个Reducer
• Sort By: 每个Reducer内部进行排序，对全局结果集来说不是排序。
• Distribute By: 类似MR中partition，进行分区，结合sort by使用。
• Cluster By: 当distribute by和sorts by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

2.7 分桶表

略

2.8 CASE WHEN

1. 数据准备

name	dept_id	sex
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count, sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by
  dept_id;

2.9 其他常用的查询函数

空字段赋值
NVL赋值，它的格式是NVL(string1, replace_with)。它的功能是如果string1是NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都是NULL，则返回NULL。

1)date_format:格式化时间

hive(default)> select date_format('2019-06-29','yyyy-MM-dd');
OK
_c0
2019-06-29

2)date_add:时间跟天数相加

hive (default)> select date_add('2019-06-29',5);
OK
_c0
2019-07-04
hive (default)> select date_add('2019-06-29',-5);
OK
_c0
2019-06-24

3)date_sub:时间跟天数相减

hive (default)> select date_sub('2019-06-29',5);
OK
_c0
2019-06-24
hive (default)> select date_sub('2019-06-29 12:12:12',5);
OK
_c0
2019-06-24
hive (default)> select date_sub('2019-06-29',-5);
OK
_c0
2019-07-04

4)datediff:两个时间相减

hive (default)> select datediff('2019-06-29','2019-06-24');
OK
_c0
5
hive (default)> select datediff('2019-06-24','2019-06-29');
OK
_c0
-5
hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29');
OK
_c0
-5
hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29 13:13:13');
OK
_c0
-5