函数简介
首先知道怎么查询函数的用法呢?
1、查看有哪些函数
show functions;
2、查看内置函数用法
desc function 函数名;
3、查看内置函数详细信息
desc function extended 函数名;
单行函数
运算符 | 描述 |
---|---|
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按位取反 |
数值函数
round:四舍五入
语法:
round(数值,保留的小数位数) 两个参数,不写第二个参数默认为整数
实例1:
select round(3.3,3);
实例2:
select round(3.3);
ceil:向下取整
实例:
select ceil(1.4);
floor:向下取整
实例:
select floor(1.6);
字符串函数
substring:截取字符串
语法:
# 法1
substring(字符串,起始位置)
# 法2
substring(字符串,起始位置,截取的长度)
实例1:
select substr('nihao',2,2);
说明:截取从第二个字符开始,长度为2,从i开始,截取出为ih
实例2:
select substr('nihaoya',2);
说明:不写第三个参数,默认从第二个位置i一直到最后一个字符,截取字符串为ihaoya
实例3:
select substr('nihao',-2);
说明:-2代表,从右往左第二个字符a,最后一直到最后一个字符,截取字符串为ao
replace(替换)三个参数
语法:
replace(字符串,替换的字符串,替换成的字符串)
返回一个字符串
实例1:
select replace('nihaoya','ao','AO');
说明:将nihaoya中的ao用AO替换掉
regexp_replace:正则替换
语法:
regexp_replace(string a,string b,string c)
将字符串a中的符合正则表达式b的部分替换成c,一些情况需要转义
实例1:
select regexp_replace("abcd-123-abcd","[0-9]+","*");
说明:将字符串中的数字替换成*,加号的作用就是整体替换,而不会一个数字对应一个*
需要学习正则表达式
regexp:正则匹配
语法:
字符串regexp正则表达式
返回值是一个布尔值
若字符串符合正则表达式,则返回true,否则返回false
实例1:
select "string" regexp ".*str.*";
repeat:重复字符串
语法:
repeat(string a,string b)
返回一个字符串
将字符串a重复b遍
实例1:
select repeat("str",3);
split:字符串切割
语法:
split(字符串,正则表达式)
按照正则表达式匹配到的内容来切分字符串,将切分后的字符串以数组的形式返回
实例1:
select split("192.168.10.102","\\.")[0];
说明:下标0返回第一个元素
nvl:替换null值
语法:
nvl(a,b)
如果a的值不为null,则返回a,否则返回b
实例1:
select nvl(1,0);
实例2:
select nvl(null,0);
concat:连接字符串
语法:
concat(string a,string b,string c,...)
返回一个字符串
将a,b,c拼接成为一个字符串
hive中也可以用||来拼接字符串
实例1:
select concat("ab","-","bb","-","cc");
实例2:
select "java"||"python";
concat_ws:以指定分割符拼接字符串或者字符串数组
语法:
concat(string a,string b ...|[array[string]])
返回一个字符串
使用分割符a拼接多个字符串,或者返回一个数组的所有元素
实例1:
select concat_ws("-","aa","bb","cc");
实例2:
select array("aa","bb","cc"); -- 构造一个数组
select concat_ws('-',array("aa","bb","cc"));
说明:先利用array构造一个数组,然后利用分隔符-将字符串数据的各个元素拼接
get_json_object:解析json字符串
语法:
get_json_object(string json_string,string path)
返回一个字符串
解析json字符串json_string,返回path指定内容,如果json字符串无效,则返回NULL
select get_json_object('[{"name":"鑫凯","age":21},{"name":"哈啊哈","age":34}]',"$[0].name");
说明:利用$获取整个数组,然后下标0拿到第一个元素,第一个元素是json对象,.name拿到name的值鑫凯
日期函数
unix_timestamp():返回当前或者指定时间的时间戳,会出现时区问题
语法:
unix_timestamp()
返回一个bigint类型数据
实例1:
获取当前时间的时间戳
select unix_timestamp(); --获取当前的时间戳
案例2:
将该时间转换为时间戳
select unix_timestamp('2023/01/02 16-54-56','yyyy/MM/dd HH-mm-ss');
from_unixtime():把时间戳转换成时间字符串,会转换成0时区的时间
注意我们所在时区不是0时区,是东八区GMT+8
语法:
from_unixtime(bigint unixtime [,string format])
返回一个字符串
实例1:将该时间戳转换为日期+时间(不是东八区的时间)
select from_unixtime(1672649540,'yyyy-MM-dd HH:mm:ss');
from_utc_timestamp:将时间戳转换为GMT+8时间
实例1:
select from_utc_timestamp(cast(1672649540 as bigint)*1000,'GMT+8');
不转换成bigint会报错,已经超过int类型数据范围了
current_date:当前日期
实例1:
select `current_date`();
month:获取日期中的月
语法:
month(string date)
返回int类型数据
实例1:
select month('2023-01-02 17:13:23');
day:获取日期中的日
语法:
day(string date)
返回int类型的值
实例1:
select day('2023-01-02 17:13:23');
hour:获取日期中的小时
语法:
hour(string date)
返回int类型的值
实例1:
select hour('2023-01-02 17:13:23');
datediff:两个日期相差的天数
语法:
datediff(string 结束日期,string 结束日期)
返回值类型为int
实例1:
select datediff('2022-01-02','2021-03-21');
date_add:日期加天数
语法:
date_add(string 开始日期,int 天数)
返回值类型为string
实例1:
select date_add('2020-06-30',1);
date_sub:日期减天数
语法:
date_sub(string 开始日期,int 天数)
返回值为string类型
实例1:
select date_sub('2020-06-30',1);
date_format:将标准日期解析成指定格式字符串
实例1:
select date_format('2022-10-08','yyyy年MM月dd日')
流程控制函数
case when:条件判断函数
语法:
case when a then b when c then d else e end
实例1:将分数>=90的成绩评为A,>=80 and <90的为B,依次类推
表score_info有三个字段:学生id,课程id,成绩
select
stu_id,
course_id,
case
when score>=90 then 'A'
when score>=80 then 'B'
when score>=70 then 'C'
when score>=60 then 'D'
else '不及格'
end
from score_info;
如果是等于某个确定的值,可以使用case score when 90 then ‘A’ when 80 then ‘B’ else ‘不及格’
if条件判断
语法:
if(条件表达式,值1,值2)
如果条件表达式为true,返回值1,否则返回值2
实例1:
select if(10>5,'正确','错误');
集合函数
array
size:集合中元素的个数
select size(`array`(1,4,5,6));
利用array创建数组,然后size统计元素个数
array_contains:判断array中是否包含某个元素
实例:
select array_contains(`array`(1,2,3,4),2);
sort_array:将array中的元素排序
实例:
select sort_array(`array`(3,5,18,6));
map
map
实例:
select map("xiaoli",1,"dada","22");
map_keys:返回所有的key值
实例:
select map_keys(map("xiaoli",1,"dada","22"));
struct
struct
实例:
select struct('python','java','c');
named_struct:将内容格式化成struct
实例:
select named_struct('name','xinkai','age',20);