Hive数学函数

round : 四舍五入

ceil : 向下取整

floor : 向上取整

select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2);

 Hive字符函数

select lower('Hello World'), upper('Hello World');select length('Hello World'),length('你好');select concat('Hello','World');select substr('Hello World',3);select substr('Hello World',3,4);select lpad('abc',10,'*'),lpad('abc',10,'*');

Hive收集函数和转换函数

sizecastselect size(map(1,'Tom',2,'Mary'));select cast(1 as bigint);select cast(1 as float);select cast('2015-04-10' as date);

 

Hive日期函数

to_dateyearmonthdayweekofyeardatediffdate_adddate_sub

 Hive条件函数

coalesce:从左到右返回第一个不为null的值。case... when...:case a when b then c [when d then e]* [else if] end

i.e.select ename,job,sal,    case job when 'persident' then sal+1000,             when 'manager' then sal+800,             else sal+400    endfrom emp;

Hive聚合函数和表生成函数

聚合函数 countsumminmaxavg表生成函数 explode

select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;select explode(map(1,'Tom',2,'Mary',3.'Mike'));