[大数据]Hive的函数

内置函数

类型转换。

cast(expr as <type>)

例如：cast(“1”?as bigint)

select cast(money as bigint)

切割

split(string str, string pat)

select split('nihao|hello|nice','\\|')

正则表达式截取字符串。

regexp_extract(string subject, string pattern, int index)

select regexp_extract('hello<B>nice</B>haha','<B>(.*)</B>',1)

select regexp_extract(字段名,正则表达式,索引)

将字符串前后出现的空格去掉。

trim(string A)

求指定列的聚合函数。

sum(col)

avg(col)

min(col)

max(col)

select subject,sum(score)

from table_name

group by subject

select中的字段，必须要在group by后面出现出行，或者用到聚合函数中。

拼接字符串。

concat(string A, string B...)

字符床的截取。

select substr('abcde',3,2)

炸裂函数。

select explode(split("nice|good|well","\\|"));

nice|good|well

Nice

Good

Well

case when

打分、评级的时候。

数据准备。

tom,95

hua,90

hong,100

lele,85

kaka,70

kebi,60

ming,55

kang,78

lolo,93

create table ods_student_level(name string,score int)

row format delimited fields terminated by ",";

load data local inpath '/usr/datadir/student_score.txt' into table ods_student_level;

select * from ods_student_level;

select name,score,

case when score >= 90 then 'very good'

when score >= 80 and score <90 then 'double good'

when score >= 70 and score <80 then 'good'

when score >= 60 and score <70 then 'go on'

else 'zhencai'

end level

from ods_student_level;

行列转换

tom a,b,c

jim b,c,d

tony a,c,d

create table test1(name string,subject string)

row format delimited

fields terminated by " ";

load data local inpath '/usr/datadir/student_info.txt' into table test1;

select name,sub

from test1

LATERAL VIEW explode(split(subject,','))temp as sub;

| tom ??| a ???|

| tom ??| b ???|

| tom ??| c ???|

| jim ???| b ???|

| jim ???| c ???|

| jim ???| d ???|

| tony ??| a ???|

| tony ??| c ???|

| tony ??| d ???|