条件函数
if
-
if(boolean testCondition, T valueTrue, T valueFalseOrNull)
isnull
nvl
-
nvl( string1, replace_with)
case when
-
case A when B then C else D end
-
case when A then B else C end
coalesce
-
返回第一个非空的值
isfalse
istrue
nullif
-
nullif(expression_1,expression_2);
-
如果第一个参数等于第二个参数返回null,否则返回第一个参数
-
日期函数
todate()
-
将时间格式转化为日期格式
-
to_date(string timestamp)
-
unix_timestamp(string date, string pattern)
-
转换 pattern 格式的日期到 UNIX 时间戳。如果转换失败,则返回 NULL。
-
select unix_timestamp('20221111 02:40:00', 'yyyyMMdd HH:mm:ss');
-
from_unixtime(时间戳)
date_format(timestamp,pattern)
month/year/day/hour/minute/second
-
日期转年月日时分秒
weekofyear
-
日期转周函数
dayofmonth/dayofweek
-
日期转日函数
datediff(string enddate, string startdate)
-
结束日期减去开始日期的天数
date_add(string startdate, int days)
-
返回开始日期后增加n天后的日期
date_sub
-
与上一个函数相反
数值函数
round
-
round(num)
-
round(num,保留几位小数 )
ceil
-
向上取整
floor
-
向下取整
rand(int seed)
-
返回一个 0 到 1 范围内的随机数。如果指定种子 seed,则会得到一个稳定的随机数序列。
exp(double a)
-
返回e的a次方
log10(double a)
-
以10为底的对数函数
log(double base, double a)
log2(double a)
pow(double a, double p)
-
a的p次幂
sqrt(double a)
-
返回a的平方根
bin(bigint a)
-
返回二进制代码
hex(bigint a)
-
返回十六进制代码
conv(bigint num, int from_base, int to_base)
-
进制转换函数
abs(int a )
-
绝对值函数
sin(double a)
asin(double a)
-
反余弦函数
cos
acos
positive
-
返回本身
negative
-
返回相反数
cast(... as type)
-
类型转换
字符串函数
ascii(string)
-
返回字符串中第一个字符的ASCII码
base64(binary bin)
-
返回二进制bin的base编码字符串
concat(string A, string B...)
-
连接若干个字符串
concat_ws(string separator, string A, string B...)
concat_ws(string separator, array<string>,...)
format_number(number x, int d)
-
将数值 x 的小数位格式化成 d 位,四舍五入
substr(string,position[,lenth])
-
只有position参数,截取从position开始至字符串末尾
-
有length参数,从position开始截取length长度的字符串
instr(string,substr)
-
找到sunstr在string出现的第一个位置
-
hive的位置是从1开始计
-
length(string)
-
返回字符串的长度
locate(string substr, string str[, int pos])
-
返回从位置position开始substr在string第一次出现的位置。
-
查询不到返回0
-
printf()格式化字符串
-
printf(String format, Obj... args)
-
select printf('name : %s, id : %d',name,id) from temp_emp;
-
lower()
-
转为小写字母
upper()
-
转为大写字母
trim()
-
去除字符串两边的空格
regexp_replace(string A, string regex, string C)
-
将字符串A中符合regex格式的字符替换为C
-
select regexp_replace('aa11vv4+4nn22','\d{2}','&'); 返回 aa&vv4+4nn&
-
split(string str, string pat)根据正则表达式进行分割
-
select split('abc11def','\d+')[0];
-
输出abc
-
parse_url(url, partToExtract[, key])
-
partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]
-
with tmp as ( select parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'QUERY','keyword')as keyword, parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'QUERY','enc')as enc )select reflect('java.net.URLDecoder','decode',keyword,enc) as keyword from tmp;
-
华为手机mate60
-
集合函数
str_to_map(text[, delimiter1, delimiter2])
-
select str_to_map('a/d,b/h,c/f,e/t',',','/');
-
第一个分隔符为map元素之间的分隔符,第二个分隔符是键值对之间的分隔符
-
size()
-
返回集合的大小
-
select size(
map
('Chinese',100,'Math',20)); -
select size(
array
('Chinese','Math','English'));
-
struct()
-
逗号分割
named_struct()
-
逗号分割,键,值,键,值....
array_contains()
-
判断数组里有无指定元素
-
select array_contains(
array
('11','22','33'),'11')
-
sort_array()
-
对数组进行排序
-
select sort_array(
array
(22,33,11)); -
select sort_array(
array
( named_struct('name','zebra','age',22), named_struct('name','ant','age',22), named_struct('name','tom','age',26), named_struct('name','cat','age',26) ));
-
sort_array_by()
-
根据结构体数组关键字进行排序
-
select sort_array_by(
array
( named_struct('name','henry','age',22), named_struct('name','jerry','age',25), named_struct('name','tom','age',27) ),'age','desc');//根据年龄倒序排序
-
map_keys()
-
select map_keys(
map
('Chinese',100,'Math',99));-
返回["Chinese","Math"]
-
map_values()
-
select map_values(
map
('Chinese',100,'Math',99));
- 返回[100,99]
collect_set()
-
对某列进行去重合并
-
select customer_lname,collect_set(customer_fname) as fanme from cb_customers group by customer_lname;
-
collect_list
-
对某列进行不去重合并
-
select customer_lname,collect_list(customer_fname) as fanme from cb_customers group by customer_lname;
-