hivesql中的常用函数
单行函数:
字符串 :
切割: split("ab_c","_")
去空格:trim(" abc ")
拼接: concat("a","b") concat_ws(",","ab","cd")--> ab,cd
子串: substr("abcd",2) --> bcd substr("abcd",2,2) -->bc
转大小写: upper("abcd") lower("abcd")
类型转换:
cast("1" as int)
cast("2019-06-18" as date)
cast("1.08" as double)
to_date("2019-06-18")
条件表达:
select id,name,if(age>10 and age<15,"young","old"),
case
when age<10 then '儿童'
when age between 10 and 20 then '少年'
when age>=20 and age<40 then '青年'
when age>=40 and age<50 then '中年'
else '老年'
end as flag
case age
when 10 then '10岁'
when 20 then '20岁'
else '其他'
end as f2
数字运算
abs(-2385)
floor(3.12) -> 3
ceiling(3.12) -> 4
round(3.123,2) -> 3.12
sqrt(4) -> 2
pow(4,0.5) -> 2
pow(4,2) -> 16
日期操作
year("2017-06-17")
month("2017-06-17")
day("2017-06-17")
hour("2017-06-17 10:30:40")
date_sub("2017-06-17",1)
datediff("2017-05-31","2017-06-17")
date_add("2017-06-17",1)
unix_timestamp("2017/06/17 10:30:40","yyyy/MM/dd HH:mm:ss")
from_unixtime(1497695440,"yyyy/MM/dd HH:mm:ss")
分组聚合函数:
max
min
sum
count
avg
collect_set("course") -- set会去重
collect_list("course") -- list不去重
name,course
zs, shuxue
zs, huaxue
zs, huaxue
zs, wuli
ls, yuwen
ls, yingyu
ls, dili
select name,collect_set("course")
from t
group by name
==》:
zs [shuxue,huaxue,wuli]
ls [yuwen,yingyu,dili]
select name,collect_list("course")
from t
group by name
==》:
zs [shuxue,huaxue,huaxue,wuli]
ls [yuwen,yingyu,dili]
窗口分析函数:
row_number() over() 排序 打标签 1 2 3 4 5
rank() over() 排名 遇到相同的值打同一个标签 下一个值加对应的跳跃值 1 2 3 3 5
dense_rank() over() 排名 遇到相同的值打同一个标签 下一个值不进行跳跃 1 2 3 3 4
ntile(n) over() 将数据切分成n个区,并返回属于第几个分区
sum() over()
count() over()
first_value() over() 取分组内排序后,截止到当前行,第一个值
last_value() over() 取分组内排序后,截止到当前行,最后一个值
lead() over() : LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反
lag() over() : LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值