hive函数大全

hive函数大全

一、关系运算

1. 等值比较: =

语法：a=b
操作类型：所有基本类型
描述：如果表达式a与表达式b相等，则为TRUE;否则为FALSE
举例：
select 1 from nanase where 1=1;

2. 不等值比较: <>

语法：a<>b
操作类型：所有基本类型
描述：如果表达式a为NULL，或者表达式b为NULL，返回NULL;如果表达式A与表达式B不相等，则为TRUE;否则为FALSE
举例：
select 1 from nanase where 1<>2;
true

3. 小于比较: <

语法：a<b
操作类型:所有基本类型
描述：如果表达式a为NULL,或者表达式b为NULL,返回NULL;如果表达式a小于表达式b,则为TRUE;否则为FLASE
举例：
select 1 from nanase where 1<2;
true

4. 小于等于比较: <=

语法：a<=b
操作类型:所有基本类型
描述：如果表达式a为NULL,或者表达式b为NULL,返回NULL;如果表达式a小于或者等于表达式b,则为TRUE;否则为FLASE
举例：
select 1 from nanase where 1<=1;
true

5. 大于比较: >

语法：a>b
操作类型:所有基本类型
描述：如果表达式a为NULL,或者表达式b为NULL,返回NULL;如果表达式a大于表达式b,则为TRUE;否则为FLASE
举例：
select 1 from nanase where 2>1;
true

6. 大于等于比较: >=

语法：a>=b
操作类型:所有基本类型
描述：如果表达式a为NULL,或者表达式b为NULL,返回NULL;如果表达式a大于或者等于表达式b,则为TRUE;否则为FLASE
举例：
select 1 from nanase where 1>=1;
true

注意：String的比较要注意（常用的时间比较可以先to_date之后再比较）
select * from nanase;
OK
2011111209 00:00:00 2011111209

hive> select a,b,a<b,a>b,a=b from nanase;
2011111209 00:00:00 2011111209 false true false

7. 空值判断: IS NULL

语法: a IS NULL
操作类型: 所有类型
描述: 如果表达式a的值为NULL,则为TRUE;否则为FALSE
举例:select 1 from nanase where null is null;
true

8. 非空判断: IS NOT NULL

语法: a IS NOT NULL
操作类型: 所有类型
描述:如果表达式A的值为NULL,则为FALSE;否则为TRUE
举例:select 1 from nanase where 1 is not null;
true

9. LIKE比较: LIKE

语法: a LIKE b
操作类型:strings
描述:如果字符串a或者字符串b为NULL,则返回NULL;如果字符串a符合表达式b的正则语法，则为TRUE;否则为FALSE。b中字符"“表示任意单个字符，而字符”%"表示任意数量的字符。
举例:select 1 from nanase where ‘football’ like ‘foot%’;
true
select 1 from nanase where ‘football’ like 'foot’;
true
注意:否定比较时用NOT A LIKE B
select 1 from nanase where NOT ‘football’ like ‘fff%’;
true

10. JAVA的LIKE操作: RLIKE

语法：A RLIKE B
操作类型：strings
描述：如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE;否则为FALSE。
举例：
select 1 from nanase ‘footbar’ rlike ‘^f.*rKaTeX parse error: Got function '\newline' with no arguments as superscript at position 1: \̲n̲e̲w̲l̲i̲n̲e̲’;
true
select 1 from nanase where ‘123456aa’ rlike ‘^\d+$’;
false

11. REGEXP操作: REGEXP

语法：A REGEXP B
操作类型：strings
描述：功能与RLIKE相同
举例：select 1 from nanase where ‘footbar’ REGEXP ‘^f.*r$’;
true

二、数学运算

1. 加法操作: +

语法：A + B
操作类型：所有数值类型
描述：返回A与B相加的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。比如，int+int一般结果为int类型，而int+double一般结果为double类型
举例：
select 1+9 from nanase ;
10
create table nanase as select 1 + 1.5 from nanase;
describe nanse;
_c0 double

2. 减法操作: -

语法：A - B
操作类型：所有数值类型
描述：返回A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。比如，int - int一般结果为int类型，而int-double一般结果为double类型
举例：
select 10-5 from nanase ;
5
create table nanase as select 5.5 - 1 from nanase;
describe nanse;
_c0 double

3. 乘法操作: *

语法：A * B
操作类型：所有数值类型
描述：返回A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。注意，如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型
举例：
select 40 * 5 from nanase;
200

4. 除法操作: /

语法：A / B
操作类型：所有数值类型
描述：返回A除以B的结果。结果的数值类型为double
举例：
select 40 / 5 from nanase;
8.0
注意：hive中最高精度的数据类型是double,只精确到小数点后16位，在做除法运算的时候要特别注意
select ceil(28.0/6.999999999999999999999) from nanase limit 1;
结果为4
select ceil(28.0/6.9999999999999) from nanase limit 1;
结果为5

5. 取余操作: %

语法：A % B
操作类型：所有数值类型
描述：返回A除以B的余数。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例：
select 41 % 5 from nanase;
1
select 8.4 % 4 from nanase;
0.40000000000000036
注意：精度在hive中是个很大的问题，类似这样的操作最好通过round指定精度
select round(8.4 % 4, 2) from nanase;
0.4

6. 位与操作: &

语法：A & B
操作类型：所有数值类型
描述：返回A与B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例：
select 4 & 8 from nanase;
0
select 6 & 4 from nanase;
4

7. 位或操作: |

语法：A | B
操作类型：所有数值类型
描述：返回A与B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例：
select 4 | 8 from nanase;
12
select 6 | 8 from nanase;
14

8. 位异或: |

语法：A ^ B
操作类型：所有数值类型
描述：返回A与B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例：
select 4 ^ 8 from nanase;
12
select 6 ^ 4 from nanase;
2

9. 位取反操作: ~

语法：~A
操作类型：所有数值类型
描述：返回A按位取反操作的结果。结果的数值类型等于A的类型。
举例：
select ~6 from nanase;
-7
select ~4 from nanase;
-5

三、逻辑运算

1. 逻辑与操作: AND

语法：A AND B
操作类型： boolean
说明： 如果A 和 B 均为TRUE,则为 TRUE;否则为FALSE。如果A为NULL 或 B为NULL,
则为NULL
举例：
select 1 from nanase where 1=1 and 2=2;
true

2. 逻辑或操作: OR

  语法：A 	OR 	 B

操作类型： boolean
说明： 如果A为TRUE,或者B为TRUE,或者A和B均为TRUE,则为 TRUE;否则为FALSE。
举例：
select 1 from nanase where 1=2 and 2=2;
true

3. 逻辑非操作: NOT

语法：NOT A
操作类型： boolean
说明： 如果A为FALSE,或者A为NULL,则为TRUE;否则为FALSE。
举例：
select 1 from nanase where not 1=2;
true

四、数值计算

1. 取整函数: round

语法：round(double a)
返回值： BIGINT
说明：返回 double 类型的整数值部分(遵循四舍五入)
举例：
select round(3.1415926) from nanase;
3
select round(3.50) from nanase;
4
create table nanase as select round(9542.158) from nanase;
describe nanase;
_c0 bigint

2. 指定精度取整函数: round

语法：round(double a,int d)
返回值： DOUBLE
说明：返回 指定精度d的double类型
举例：
select round(3.1415926,4) from nanase;
3.1416

3. 向下取整函数: floor

语法：floor(double a)
返回值： BIGINT
说明：返回等于或者小于该double变量的最大的整数
举例：
select floor(3.1415926,4) from nanase;
3
select floor(25) from nanase;
25

4. 向上取整函数: ceil

语法：ceil(double a)
返回值： BIGINT
说明：返回等于或者大于该double变量的最小的整数
举例：
select ceil(3.1415926) from nanase;
4
select ceil(46) from nanase;
46

5. 向上取整函数: ceiling

语法：ceiling(double a)
返回值： BIGINT
说明：与ceil功能相同
举例：