内置函数
类型转换。
cast(expr as
例如：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('hellonicehaha','(.*)',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 |
