Hive(七)【内置函数】

一.系统内置函数

1.查看系统自带内置函数

show functions;

2.查看函数的具体用法

如查看upper函数

desc function extended upper;

二.常用内置函数

1.数学函数

round

round(DOUBLE a)：返回对a四舍五入的BIGINT值

round(DOUBLE a, INT d)：返回DOUBLE型d的保留n位小数的DOUBLE型的近似值

案例

0: jdbc:hive2://hadoop102:10000> select round(4.5),round(4.12345,2);
+------+-------+
| _c0  |  _c1  |
+------+-------+
| 5    | 4.12  |
+------+-------+

2.字符函数

split

split(string str, string pat) 返回类型：array

解析：按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回。

案例

0: jdbc:hive2://hadoop102:10000> select split(‘aaa-bbb-ccc‘,‘-‘);
+----------------------+
|         _c0          |
+----------------------+
| ["aaa","bbb","ccc"]  |
+----------------------+

substring

substring(string A, int start, int len) 返回类型：string

解析：对于字符串A,从start位置开始截取长度为length的字符串并返回

案例

0: jdbc:hive2://hadoop102:10000> select substring(‘2020-06-29‘,1,7);
+----------+
|   _c0    |
+----------+
| 2020-06  |
+----------+

concat

concat(string A, string B...) 返回类型：string

解析：将每个字符串拼接，也可以是sql查询的字段

案例

0: jdbc:hive2://hadoop102:10000> select concat(‘aaa‘,‘--‘,‘bbb‘,‘|‘,‘ccc‘);
+---------------+
|      _c0      |
+---------------+
| aaa--bbb|ccc  |
+---------------+

concat_ws

concat_ws(string SEP, string A, string B...)

解析：sep是分割符，其余字符串以这个分割符拼接

案例

0: jdbc:hive2://hadoop102:10000> select concat_ws(‘-‘,‘aaa‘,‘bbb‘,‘ccc‘);
+--------------+
|     _c0      |
+--------------+
| aaa-bbb-ccc  |
+--------------+

lower,upper

解析：lower将字符串转全部转为小写，upper将字符串转全部转为大写

案例

0: jdbc:hive2://hadoop102:10000> select lower(‘Hello World‘) ,upper(‘Hello World‘);
+--------------+--------------+
|     _c0      |     _c1      |
+--------------+--------------+
| hello world  | HELLO WORLD  |
+--------------+--------------+

trim

解析：去前后空格

length

解析：字符串长度，字符数

3.日期函数

to_date

解析：从一个字符串中取出为日期的部分

案例

0: jdbc:hive2://hadoop102:10000> select to_date(‘2020-06-29 20:34:01‘);
+-------------+
|     _c0     |
+-------------+
| 2020-06-29  |
+-------------+

yaer、month、day

解析：从一个日期中取出相应的年、月、日

案例

0: jdbc:hive2://hadoop102:10000> select year(‘2020-06-29 20:34:01‘),month(‘2020-06-29 20:34:01‘),day(‘2020-06-29 20:34:01‘);
+-------+------+------+
|  _c0  | _c1  | _c2  |
+-------+------+------+
| 2020  | 6    | 29   |
+-------+------+------+

4.条件函数

case...when...

语法格式：case A when B then C [when D then E]* [else F] end

解析：对于A来说，如果判断为B则返回C，如果判断为D则返回E（此处判断条件可为多个），如果以上都不是则返回F。注意，最后还有还有一个end结束符

案例

需求：求不同部门的男女各有多少人

原始数据 : emp_sex.txt

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

建表

create table emp_sex(
name string,
dept_id string,
sex string
)
row format delimited fields terminated by ‘\t‘;

把原始数据导入表

load data local inpath ‘/opt/module/testdata/emp_sex.txt‘ into table emp_sex;

查看一下

select * from emp_sex;

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

实现：分组统计某个字段不同值得数量；

select 
dept_id,
sum(case when sex=‘男‘ then 1 else 0 end) man_count,
sum(case when sex=‘女‘ then 1 else 0 end) woman_count
from emp_sex
group by dept_id;

nvl

语法：NVL( 字段值，value)

解析：给空字段赋值，如果字段值为空则赋值为value，否则字段值不变；若都为null那么返回null

案例

0: jdbc:hive2://hadoop102:10000> select nvl(‘aaa‘,1),nvl(null,1);
+------+------+
| _c0  | _c1  |
+------+------+
| aaa  | 1    |
+------+------+

Hive(七)【内置函数】

原文：https://www.cnblogs.com/wh984763176/p/13210430.html