第10周 · 函数的使用

函数的使用

11 函数的基本用法

以下命令可用于查询所有内置函数的相关信息。

查看系统内置函数

如图1-1所示，使用如下命令，查看系统内置函数。

show functions;

📷 图1-1 查看系统内置函数

查看内置函数用法

如图1-2所示，使用如下命令查看upper函数的用法。

desc function upper;

📷 图1-2 查看内置函数用法

查看内置函数详细信息

如图1-3所示，使用如下命令查看upper函数的详细信息。

desc function extended upper;

📷 图1-3 查看内置函数详细信息

12 内置函数

单行函数的特点是一进一出，即输入一行，输出一行。

单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等。

111 算术运算函数

运算符 描述 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;

112 数值函数

1）round：四舍五入

如图1-4所示，显示3.3四舍五入后的值。

select round(3.3);

📷 图1-4 round函数的使用

ceil：向上取整

如图1-5所示，对3.1进行向上取整。

select ceil(3.1) ;

📷 图1-5 ceil函数的使用

floor：向下取整

如图1-6所示，对4.81进行向下取整。

select floor(4.8);

📷 图1-6 floor函数的使用

113 字符串函数

1）substring：截取字符串

语法一：substring(string A, int start)

返回值：string

说明：返回字符串A从start位置到结尾的字符串

语法二：substring(string A, int start, int len)

返回值：string

说明：返回字符串A从start位置开始，长度为len的字符串

案例实操：

（1）如图1-7所示，获取第二个字符以后的所有字符

select substring("hadoop",2);

📷 图1-7 使用substring函数获取第二个字符以后的所有字符

（2）如图1-8所示，获取倒数第三个字符以后的所有字符

select substring("hadoop",-3);

📷 图1-8 使用substring函数获取倒数第三个字符以后的所有字符

（3）如图1-9所示，从第3个字符开始，向后获取2个字符

select substring("hadoop",3,2);

📷 图1-9 使用substring函数获取从第3个字符开始，向后获取2个字符

2）replace ：替换

语法：replace(string A, string B, string C)

返回值：string

说明：将字符串A中的子字符串B替换为C。

如图1-10所示，将hadoop中的a替换为A。

select replace('hadoop', 'a', 'A')

📷 图1-10 replace函数的使用

3）regexp_replace：正则替换

语法：regexp_replace(string A, string B, string C)

返回值：string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符。

如图1-11所示，将‘100-200’中的整数替换为‘num’。

select regexp_replace('100-200', '(\\d+)', 'num') ;

📷 图1-11 regexp_replace函数的使用

4）regexp：正则匹配

语法：字符串 regexp 正则表达式

返回值：boolean

说明：若字符串符合正则表达式，则返回true，否则返回false。

（1）如图1-12所示，正则匹配成功，输出true

select 'dfsaaaa' regexp 'dfsa+'

📷 图1-12 regexp正则匹配成功，输出true

（2）如图1-13所示，正则匹配失败，输出false

select 'dfsaaaa' regexp 'dfsb+';

📷 图1-13 regexp正则匹配成功，输出false

5）repeat：重复字符串

语法：repeat(string A, int n)

返回值：string

说明：将字符串A重复n遍。

如图1-14所示，将‘123’重复3遍。

select repeat('123', 3);

📷 图1-14 repeat函数的使用

6）split ：字符串切割

语法：split(string str, string pat)

返回值：array

说明：按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。

如图1-15所示，用‘-’分割'a-b-c-d'成一个数组。

select split('a-b-c-d','-');

📷 图1-15 split函数的使用

7）nvl ：替换null值

语法：nvl(A,B)

说明：若A的值不为null，则返回A，否则返回B。

如图1-16所示，第一个值为null，返回1。

select nvl(null,1);

📷 图1-16 nvl函数的使用

8）concat ：拼接字符串

语法：concat(string A, string B, string C, ……)

返回：string

说明：将A,B,C……等字符拼接为一个字符串

如图1-17所示，将5个字符串连接起来。

select concat('beijing','-','shanghai','-','shenzhen');

📷 图1-17 concat函数的使用

9）concat_ws：以指定分隔符拼接字符串或者字符串数组

语法：concat_ws(string A, string…| array(string))

返回值：string

说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。

如图1-18所示，使用‘-’，将后面三个字符串连接起来。

select concat_ws('-',array('beijing','shenzhen','shanghai'));

📷 图1-18 concat_ws函数的使用

10）get_json_object：解析json字符串

语法：get_json_object(string json_string, string path)

返回值：string

说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

（1）如图1-19所示，获取json数组里面的json具体数据

hive> select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0].name');

📷 图1-19 获取json数组里面的json具体数据

如图1-20所示，获取json数组里面的数据

select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0]');

📷 图1-20 获取json数组里面的数据

114 日期函数

1）unix_timestamp：返回当前或指定时间的时间戳

语法：unix_timestamp()

返回值：bigint

如图1-21所示返回'2022/08/08 08-08-08'的时间戳。

select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');

📷 图1-21 unix_timestamp函数的使用

说明：-前面是日期后面是指，日期传进来的具体格式

2）from_unixtime：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式

语法：from_unixtime(bigint unixtime[, string format])

返回值：string

如图1-22所示，转换时间戳为当前时间。

select from_unixtime(1659946088);

📷 图1-22 from_unixtime函数的使用

current_date：当前日期

如图1-23所示，显示当前日期。

select current_date;

📷 图1-23 current_date函数的使用

current_timestamp：当前的日期加时间，并且精确的毫秒

如图1-24所示，显示当前日期加时间。

select current_timestamp;

📷 图1-24 current_timestamp函数的使用

5）month：获取日期中的月

语法：month (string date)

返回值：int

如图1-25所示，获取日期中的月份。

select month('2022-08-08 08:08:08');

📷 图1-25 month函数的使用

6）day：获取日期中的日

语法：day (string date)

返回值：int

如图1-26所示，显示日中的日。

select day('2022-08-08 08:08:08')

📷 图1-26 day函数的使用

7）hour：获取日期中的小时

语法：hour (string date)

返回值：int

如图1-27所示，显示日期中的小时。

select hour('2022-08-08 08:08:08');

📷 图1-27 hour函数的使用

8）datediff：两个日期相差的天数（结束日期减去开始日期的天数）

语法：datediff(string enddate, string startdate)

返回值：int

如图1-28所示，计算两个日期相差的天数。

select datediff('2021-08-08','2022-10-09');

📷 图1-28 datediff函数的使用

9）date_add：日期加天数

语法：date_add(string startdate, int days)

返回值：string

说明：返回开始日期 startdate 增加 days 天后的日期

如图1-29所示，返回'2022-08-08'后2天的日期。

select date_add('2022-08-08',2);

📷 图1-29 date_add函数的使用

10）date_sub：日期减天数

语法：date_sub (string startdate, int days)

返回值：string

说明：返回开始日期startdate减少days天后的日期。

如图1-30所示，'2022-08-08'减少两天的日期。

select date_sub('2022-08-08',2);

📷 图1-30 date_sub函数的使用

date_format:将标准日期解析成指定格式字符串

如图1-31所示，将日期格式化。

select date_format('2022-08-08','yyyy年-MM月-dd日')

📷 图1-31 date_format函数的使用

115 流程控制函数

1）case when：条件判断函数

语法一：case when a then b [when c then d]* [else e] end

返回值：T

说明：如果a为true，则返回b；如果c为true，则返回d；否则返回 e

如图1-31所示，演示语法一的用法。

select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from temperature;

📷 图1-32 case when语法一的用法

语法二： case a when b then c [when d then e]* [else f] end

返回值: T

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

如图1-33所示，演示语法二的用法。

select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from temperature;

📷 图1-33 case when语法二的用法

2）if: 条件判断，类似于Java中三元运算符

语法：if（boolean testCondition, T valueTrue, T valueFalseOrNull）

返回值：T

说明：当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull

（1）如图1-34所示，条件满足，输出正确

select if(10 > 5,'正确','错误');

📷 图1-34 if条件判断（一）

（2）如图1-35所示，条件满足，输出错误

select if(10 < 5,'正确','错误);

📷 图1-35 if条件判断（二）

116 集合函数

1）size：集合中元素的个数

select size(friends) from test; --2/2 每一行数据中的friends集合里的个数

2）map：创建map集合

语法：map (key1, value1, key2, value2, …)

说明：根据输入的key和value对构建map类型

如图1-36所示，将输入的key和value对构建map类型。

select map('xiaohai',1,'dahai',2);

📷 图1-36 map函数的用法

map_keys： 返回map中的key

如图1-37所示，返回map中的key。

select map_keys(map('xiaohai',1,'dahai',2));

📷 图1-37 map_keys函数的用法

map_values: 返回map中的value

如图1-38所示，返回map中的value。

select map_values(map('xiaohai',1,'dahai',2));

📷 图1-38 map_keys函数的用法

5）array 声明array集合

语法：array(val1, val2, …)

说明：根据输入的参数构建数组array类

如图1-39所示，声明array集合。

select array('1','2','3','4');

📷 图1-39 array函数的用法

6）array_contains: 判断array中是否包含某个元素

如图1-40所示，判断array中是否包含某个元素。

select array_contains(array('a','b','c','d'),'a');

📷 图1-40 array_contains函数的用法

sort_array：将array中的元素排序

如图1-41所示，将array中的元素排序。

select sort_array(array('a','d','c'));

📷 图1-41 sort_array函数的用法

8）struct声明struct中的各属性

语法：struct(val1, val2, val3, …)

说明：根据输入的参数构建结构体struct类

如图1-42所示，声明struct中的各属性。

select struct('name','age','weight');

📷 图1-42 struct函数的用法

9）named_struct声明struct的属性和值

如图1-43所示，声明struct的属性和值。

select named_struct('name','xiaosong','age',18,'weight',80);

📷 图1-43 named_struct函数的用法

117 聚合函数

1）语法

count(*)，表示统计所有行数，包含null值；

count(某列)，表示该列一共有多少行，不包含null值；

max()，求最大值，不包含null，除非所有值都是null；

min()，求最小值，不包含null，除非所有值都是null；

sum()，求和，不包含null。

avg()，求平均值，不包含null。

2）案例实操

（1）如图1-44所示，求总行数（count）

select count(*) cnt from temperature;

📷 图1-44 count函数的用法

（2）如图1-45所示，求温度的最大值（max）

select max(temperature) max_tem from temperature;

📷 图1-45 max函数的用法

（3）如图1-46所示，求温度的最小值（min）

select min(temperature) min_tem from temperature;

📷 图1-46 min函数的用法

（4）如图4-47所示，求温度的总和（sum）

select sum(temperature) sum_tem from temperature;

📷 图1-47 sum函数的用法

（5）如图1-48所示，求温度的平均值（avg）

select avg(temperature) avg_tem from temperature;

📷 图1-48 sum函数的用法

II