这里用的数据是:test1数据库
1.!
逻辑非
2.!=
不等值比较
a!=b-如果a不等于b,则返回TRUE
举例:
select * from score where name != '张三';
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
| 李四 | 语文 | 70 |
| 李四 | 数学 | 80 |
| 李四 | 英语 | 90 |
| 王五 | 语文 | 70 |
| 王五 | 数学 | 80 |
+-------------+-------------+---------------+--+
3.%
取余操作: %
语法: A % B
操作类型:所有数值类型
说明:返回A除以B的余数。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例:
select 41 % 5 from score;
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
select 8.4 % 4 from score;
+----------------------+--+
| _c0 |
+----------------------+--+
| 0.40000000000000036 |
+----------------------+--+
注意:精度在hive中是个很大的问题,类似这样的操作最好通过round指定精度
select round(8.4 % 4 , 2) from score;
+------+--+
| _c0 |
+------+--+
| 0.4 |
+------+--+
4.&
位与操作: &
语法: A & B
操作类型:所有数值类型
说明:返回A和B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例:
select 4 & 8 from score;
0+------+--+
| _c0 |
+------+--+
| 0 |
+------+--+
select 6 & 4 from score;
+------+--+
| _c0 |
+------+--+
| 4 |
+------+--+
5.*
乘法操作: *
语法: A * B
操作类型:所有数值类型
说明:返回A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。注意,如果A乘以B的结果超过默认结果类型的数值范围,则需要通过cast将结果转换成范围更大的数值类型
举例:
select 40 * 5 from score;
+------+--+
| _c0 |
+------+--+
| 200 |
+------+--+
6.+
加法操作: +
语法: A + B
操作类型:所有数值类型
说明:返回A与B相加的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。比如,int + int 一般结果为int类型,而int + double一般结果为double类型
举例:
select 1 + 9 from emp_basic;
+------+--+
| _c0 |
+------+--+
| 10 |
+------+--+
create table test2 as select 1 + 1.2 from emp_basic;
describe test2;
+-----------+------------+----------+--+
| col_name | data_type | comment |
+-----------+------------+----------+--+
| _c0 | double | |
+-----------+------------+----------+--+
7.-
语法: A– B
操作类型:所有数值类型
说明:返回A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。比如,int– int 一般结果为int类型,而int– double 一般结果为double类型
举例:
select 10 - 5 from emp_basic;
+------+--+
| _c0 |
+------+--+
| 5 |
+------+--+
create table test3 as select 5.6 – 4 from score;
describe test3;
+-----------+------------+----------+--+
| col_name | data_type | comment |
+-----------+------------+----------+--+
| _c0 | double | |
+-----------+------------+----------+--+
8./
除法操作: /
语法: A / B
操作类型:所有数值类型
说明:返回A除以B的结果。结果的数值类型为double
举例:
select 40 / 5 from score;
+------+--+
| _c0 |
+------+--+
| 8.0 |
+------+--+
9.<
小于比较: <
语法: A < B
操作类型:所有基本类型
描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A小于表达式B,则为TRUE;否则为FALSE
举例:
select * from score where tscore<80
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
| 张三 | 英语 | 65 |
| 李四 | 语文 | 70 |
| 王五 | 语文 | 70 |
+-------------+-------------+---------------+--+
10.<=
小于等于比较: <=
语法: A <= B
操作类型:所有基本类型
描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A小于或者等于表达式B,则为TRUE;否则为FALSE
举例:
select * from score where tscore<=80
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
| 张三 | 语文 | 80 |
| 张三 | 英语 | 65 |
| 李四 | 语文 | 70 |
| 李四 | 数学 | 80 |
| 王五 | 语文 | 70 |
| 王五 | 数学 | 80 |
+-------------+-------------+---------------+--+
11.<=>
等值比较
对于非空操作数,a<=>b-返回相同的结果,如果两个操作数都为null,则返回TRUE;如果其中一个操作数为null,则返回FALSE
12.<>
不等值比较: <>
语法: A <> B
操作类型:所有基本类型
描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A与表达式B不相等,则为TRUE;否则为FALSE
举例:
select * from score where name<>'张三'
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
| 李四 | 语文 | 70 |
| 李四 | 数学 | 80 |
| 李四 | 英语 | 90 |
| 王五 | 语文 | 70 |
| 王五 | 数学 | 80 |
+-------------+-------------+---------------+--+
13.=
等值比较: =
语法:A=B
操作类型:所有基本类型
描述:如果表达式A与表达式B相等,则为TRUE;否则为FALSE
举例:
select * from score where name='张三'
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
| 张三 | 语文 | 80 |
| 张三 | 数学 | 98 |
| 张三 | 英语 | 65 |
+-------------+-------------+---------------+--+
select name="张三" from score where name="张三"
+-------+--+
| _c0 |
+-------+--+
| true |
| true |
| true |
+-------+--+
14.==
a==b-如果a等于b,则返回TRUE,否则返回false
select * from score where tscore==80
15.>
大于比较: >
语法: A > B
操作类型:所有基本类型
描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A大于表达式B,则为TRUE;否则为FALSE
举例:
select * from score where tscore>80
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
| 张三 | 数学 | 98 |
| 李四 | 英语 | 90 |
+-------------+-------------+---------------+--+
16.>=
大于等于比较: >=
语法: A >= B
操作类型:所有基本类型
描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A大于或者等于表达式B,则为TRUE;否则为FALSE
举例:
select * from score where tscore>=80;
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
| 张三 | 语文 | 80 |
| 张三 | 数学 | 98 |
| 李四 | 数学 | 80 |
| 李四 | 英语 | 90 |
| 王五 | 数学 | 80 |
+-------------+-------------+---------------+--+
注意:String的比较要注意(常用的时间比较可以先to_date之后再比较)
hive> select* from lxw_dual;
201111120900:00:00 2011111209
hive> selecta,b,a<b,a>b,a=b from lxw_dual;
201111120900:00:00 2011111209 false true false
17.^
位异或操作: ^
语法: A ^ B
操作类型:所有数值类型
说明:返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例:
select 4 ^ 8 from score;
+------+--+
| _c0 |
+------+--+
| 12 |
+------+--+
select 6 ^ 4 from score;
+------+--+
| _c0 |
+------+--+
| 2 |
+------+--+
18.abs
绝对值函数: abs
语法: abs(double a) abs(int a)
返回值: double int
说明:返回数值a的绝对值
举例:
select abs(-3.9) from score;
+------+--+
| _c0 |
+------+--+
| 3.9 |
+------+--+
select abs(10.9) from score;
+-------+--+
| _c0 |
+-------+--+
| 10.9 |
+-------+--+
19.acos
反余弦函数: acos
语法: acos(double a)
返回值: double
说明:返回a的反余弦值
举例:
select acos(0.6216099682706644) from score;
+------+--+
| _c0 |
+------+--+
| 0.9 |
+------+--+
20.add_months
add_months(start_date,num_months)-返回开始日期后num_months的日期。
SELECT add_months(‘2009-08-31’, 1) FROM src LIMIT 1;
‘2009-09-30’
21.and
逻辑与操作: AND
语法: A AND B
操作类型:boolean
说明:如果A和B均为TRUE,则为TRUE;否则为FALSE。如果A为NULL或B为NULL,则为NULL
举例:
select 1 from score where 1=1 and 2=2;
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
array类型构建: array
语法: array(val1, val2,…)
说明:根据输入的参数构建数组array类型
举例:
create table test7 as select array("tom","mary","tim") as t from score;
describe test7;
+-----------+----------------+----------+--+
| col_name | data_type | comment |
+-----------+----------------+----------+--+
| t | array<string> | |
+-----------+----------------+----------+--+
t array<string>
select t from test7;
["tom","mary","tim"]
+-----------------------+--+
| t |
+-----------------------+--+
| ["tom","mary","tim"] |
+-----------------------+--+
23.array_contains
判断元素数组是否包含元素
select array_contains(array(‘aa’,‘bb’,‘cc’),‘bb’)
true
24.ascii
首字符ascii函数:ascii
语法: ascii(string str)
返回值: int
说明:返回字符串str第一个字符的ascii码
举例:
select ascii('abcde') from score;
+------+--+
| _c0 |
+------+--+
| 97 |
+------+--+
25.asin
反正弦函数: asin
语法: asin(double a)
返回值: double
说明:返回a的反正弦值
举例:
select asin(0.7173560908995228) from score;
+------+--+
| _c0 |
+------+--+
| 0.8 |
+------+--+
26.assert_true
assert\u true(condition)-如果“condition”不为true,则引发异常。
断言为真(条件)-如果“条件”不为真,则丢弃一个例外。
SELECT assert_true(x >= 0) FROM src LIMIT 1;
null
27.atan
atan(x)-返回x的atan(arctan)(x以弧度表示)
SELECT atan(0) FROM src LIMIT 1;
SELECT atan(0) FROM src LIMIT 1;
0
28.avg
平均值统计函数: avg
语法: avg(col), avg(DISTINCT col)
返回值: double
说明: avg(col)统计结果集中col的平均值;avg(DISTINCT col)统计结果中col不同值相加的平均值
举例:
select avg(tscore) from score;
+---------+--+
| _c0 |
+---------+--+
| 79.125 |
+---------+--+
select avg (distinct tscore) from score;
+-------+--+
| _c0 |
+-------+--+
| 80.6 |
+-------+--+
29.base64
90.between
91.bin
SELECT bin(13) FROM score LIMIT 1;
+-------+--+
| _c0 |
+-------+--+
| 1101 |
+-------+--+
92.case
条件判断函数:CASE
语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
返回值: T
说明:如果a等于b,那么返回c;如果a等于d,那么返回e;否则返回f
举例:
Select case 100 when 50 then 'tom' when 100 then 'mary'else 'tim' end from score;
+-------+--+
| _c0 |
+-------+--+
| mary |
+-------+--+
Select case 200 when 50 then 'tom' when 100 then 'mary'else 'tim' end from score;
+------+--+
| _c0 |
+------+--+
| tim |
+------+--+
条件判断函数:CASE
语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值: T
说明:如果a为TRUE,则返回b;如果c为TRUE,则返回d;否则返回e
举例:
select case when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end from score;
+-------+--+
| _c0 |
+-------+--+
| mary |
+-------+--+
select case when 1=1 then 'tom' when 2=2 then 'mary' else'tim' end from score;
+------+--+
| _c0 |
+------+--+
| tom |
+------+--+
93.cbrt
94.ceil
向上取整函数: ceil
语法: ceil(double a)
返回值: BIGINT
说明:返回等于或者大于该double变量的最小的整数
举例:
select ceil(3.1415926) from score;
+------+--+
| _c0 |
+------+--+
| 4 |
+------+--+
select ceil(46) from score;
+------+--+
| _c0 |
+------+--+
| 46 |
+------+--+
95.ceiling
向上取整函数: ceiling
语法: ceiling(double a)
返回值: BIGINT
说明:与ceil功能相同
select ceiling(3.1415926) from score;
+------+--+
| _c0 |
+------+--+
| 4 |
+------+--+
select ceiling(46) from score;
+------+--+
| _c0 |
+------+--+
| 46 |
+------+--+
96.coalesce
非空查找函数: COALESCE
语法: COALESCE(T v1, T v2,…)
返回值: T
说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL
举例:
select COALESCE(null,'100','50') from score;
+------+--+
| _c0 |
+------+--+
| 100 |
+------+--+
97.collect_list
98.collect_set
99.compute_stats
100.concat
字符串连接函数:concat
语法: concat(string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,支持任意个输入字符串
举例:
select concat('abc','def','gh') from score;
+-----------+--+
| _c0 |
+-----------+--+
| abcdefgh |
+-----------+--+
102.concat_ws
带分隔符字符串连接函数:concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符
举例:
select concat_ws(',','abc','def','gh') from score;
+-------------+--+
| _c0 |
+-------------+--+
| abc,def,gh |
+-------------+--+
103.context_ngrams
104.conv
进制转换函数: conv
语法: conv(BIGINT num, int from_base, int to_base)
返回值: string
说明:将数值num从from_base进制转化到to_base进制
举例:
select conv(17,10,16) from score;
+------+--+
| _c0 |
+------+--+
| 11 |
+------+--+
select conv(17,10,2) from score;
+--------+--+
| _c0 |
+--------+--+
| 10001 |
+--------+--+
105.corr
106.cos
余弦函数: cos
语法: cos(double a)
返回值: double
说明:返回a的余弦值
举例:
select cos(0.9) from score;
+---------------------+--+
| _c0 |
+---------------------+--+
| 0.6216099682706644 |
+---------------------+--+
107.count
个数统计函数: count
语法: count(), count(expr), count(DISTINCT expr[, expr_.])
返回值: int
说明: count()统计检索出的行的个数,包括NULL值的行;count(expr)返回指定字段的非空值的个数;count(DISTINCTexpr[, expr_.])返回指定字段的不同的非空值的个数
举例:
select count(*) from score;
+------+--+
| _c0 |
+------+--+
| 8 |
+------+--+
select count(distinct name) from score;
+------+--+
| _c0 |
+------+--+
| 3 |
+------+--+
108.covar_pop
109.covar_samp
110.crc32
111.create_union
112.cume_dist
113.current_database
114.current_date
115.current_timestamp
116.current_user
117.date_add
日期增加函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明:返回开始日期startdate增加days天后的日期。
举例:
select date_add('2012-12-08',10) from score;
+-------------+--+
| _c0 |
+-------------+--+
| 2012-12-18 |
+-------------+--+
118.date_format
119.date_sub
日期减少函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。
举例:
select date_sub('2012-12-08',10) from score;
+-------------+--+
| _c0 |
+-------------+--+
| 2012-11-28 |
+-------------+--+
120.datediff
日期比较函数: datediff
语法: datediff(string enddate, string startdate)
返回值: int
说明:返回结束日期减去开始日期的天数。
举例:
select datediff('2012-12-08','2012-05-09') from score;
+------+--+
| _c0 |
+------+--+
| 213 |
+------+--+
121.day
日期转天函数: day
语法: day (string date)
返回值: int
说明:返回日期中的天。
举例:
select day('2011-12-08 10:03:01') from score;
+------+--+
| _c0 |
+------+--+
| 8 |
+------+--+
select day('2011-12-24') from score;
+------+--+
| _c0 |
+------+--+
| 24 |
+------+--+
122.dayofmonth
123.dayofweek
124.decode
125.degrees
126.dense_rank
127.div
128.e
129.elt
130.encode
131.ewah_bitmap
132.ewah_bitmap_and
133.ewah_bitmap_empty
134.ewah_bitmap_or
135.exp
自然指数函数: exp
语法: exp(double a)
返回值: double
说明:返回自然对数e的a次方
举例:
hive> select exp(2) from score;
7.38905609893065
自然对数函数: ln
语法: ln(double a)
返回值: double
说明:返回a的自然对数
举例:
select ln(7.38905609893065) from score;
+------+--+
| _c0 |
+------+--+
| 2.0 |
+------+--+
136.explode
137.field
138.find_in_set
集合查找函数:find_in_set
语法: find_in_set(string str, string strList)
返回值: int
说明:返回str在strlist第一次出现的位置,strlist是用逗号分割的字符串。如果没有找该str字符,则返回0
举例:
select find_in_set('ab','ef,ab,de') from score;
+------+--+
| _c0 |
+------+--+
| 2 |
+------+--+
select find_in_set('at','ef,ab,de') from score;
+------+--+
| _c0 |
+------+--+
| 0 |
+------+--+
139.first_value
140.floor
向下取整函数: floor
语法: floor(double a)
返回值: BIGINT
说明:返回等于或者小于该double变量的最大的整数
举例:
select floor(3.1415926) from score;
+------+--+
| _c0 |
+------+--+
| 3 |
+------+--+
select floor(25) from score;
+------+--+
| _c0 |
+------+--+
| 25 |
+------+--+
141.format_number
142.from_unixtime
UNIX时间戳转日期函数:from_unixtime
语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明:转化UNIX时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式
举例:
select from_unixtime(1323308943,'yyyyMMdd') from score;
+-----------+--+
| _c0 |
+-----------+--+
| 20111207 |
+-----------+--+
143.from_utc_timestamp
144.get_json_object*************************************
json解析函数:get_json_object
语法: get_json_object(string json_string, string path)
返回值: string
说明:解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。
举例:
hive> select get_json_object(’{“store”:
{“fruit”:[{“weight”:8,“type”:“apple”},{“weight”:9,“type”:“pear”}],
“bicycle”:{“price”:19.95,“color”:“red”}
},
“email”:“amy@only_for_json_udf_test.net”,
“owner”:“amy”
}
‘,’$.owner’) from lxw_dual;
amy
145.greatest
146.hash
147.hex
十六进制函数: hex
语法: hex(BIGINT a)
返回值: string
说明:如果变量是int类型,那么返回a的十六进制表示;如果变量是string类型,则返回该字符串的十六进制表示
举例:
select hex(17) from score;
+------+--+
| _c0 |
+------+--+
| 11 |
+------+--+
select hex('abc') from score;
+---------+--+
| _c0 |
+---------+--+
| 616263 |
+---------+--+
148.histogram_numeric
直方图:histogram_numeric
语法: histogram_numeric(col, b)
返回值: array<struct {‘x’,‘y’}>
说明:以b为基准计算col的直方图信息。
举例:
select histogram_numeric(100,5) from score;
[{"x":100.0,"y":1.0}]
+--------------------------------------------+--+
| _c0 |
+--------------------------------------------+--+
| [{"x":100.0,"y":3.0},{"x":100.0,"y":5.0}] |
+--------------------------------------------+--+
149.hour
日期转小时函数: hour
语法: hour (string date)
返回值: int
说明:返回日期中的小时。
举例:
select hour('2011-12-08 10:03:01') from score;
+------+--+
| _c0 |
+------+--+
| 10 |
+------+--+
150.if
If函数: if
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull
举例:
select if(1=2,100,200) from score;
+------+--+
| _c0 |
+------+--+
| 200 |
+------+--+
select if(1=1,100,200) from score;
+------+--+
| _c0 |
+------+--+
| 100 |
+------+--+
151.in
152.in_file
153.index
154.initcap
155.inline
156.instr
155.isnotnull
非空判断: IS NOTNULL
语法: A IS NOT NULL
操作类型:所有类型
描述:如果表达式A的值为NULL,则为FALSE;否则为TRUE
举例:
hive> select1 from lxw_dual where 1 is not null;
157.isnull
语法: A IS NULL
操作类型:所有类型
描述:如果表达式A的值为NULL,则为TRUE;否则为FALSE
举例:
select tscore=81 from score where tscore is null;
158.java_method
159.json_tuple
160.lag
161.last_day
162.last_value
163.lcase
字符串转小写函数:lower,lcase
语法: lower(string A) lcase(string A)
返回值: string
说明:返回字符串A的小写格式
举例:
select lower('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| absed |
+--------+--+
select lcase('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| absed |
+--------+--+
164.lead
165.least
166.length
字符串长度函数:length
语法: length(string A)
返回值: int
说明:返回字符串A的长度
举例:
select length('abcedfg') from score;
+------+--+
| _c0 |
+------+--+
| 7 |
+------+--+
167.levenshtein
168.like
LIKE比较: LIKE
语法: A LIKE B
操作类型: strings
描述:如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合表达式B 的正则语法,则为TRUE;否则为FALSE。B中字符”_”表示任意单个字符,而字符”%”表示任意数量的字符。
举例:
select name from score where 'name' like 'nam%';
+-------+--+
| name |
+-------+--+
| 张三 |
| 张三 |
| 张三 |
| 李四 |
| 李四 |
| 李四 |
| 王五 |
| 王五 |
+-------+--+
select 1 from score where '张三' like '张二%';
+------+--+
| _c0 |
+------+--+
+------+--+
select * from score where '张三' like '张%';
+-------------+-------------+---------------+--+
| score.name | score.type | score.tscore |
+-------------+-------------+---------------+--+
+-------------+-------------+---------------+--+
select 1 from score where '张三' like '张_';
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
注意:否定比较时候用NOT ALIKE B
hive> select1 from lxw_dual where NOT ‘football’ like ‘fff%’;
1
169.ln
170.locate
171.log
172.log10
对数函数: log
语法: log(double base, double a)
返回值: double
说明:返回以base为底的a的对数
举例:
select log(4,256) from score;
+------+--+
| _c0 |
+------+--+
| 4.0 |
+------+--+
173.log2
174.logged_in_user
175.lower
字符串转小写函数:lower,lcase
语法: lower(string A) lcase(string A)
返回值: string
说明:返回字符串A的小写格式
举例:
select lower('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| absed |
+--------+--+
select lcase('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| absed |
+--------+--+
176.lpad
左补足函数:lpad
语法: lpad(string str, int len, string pad)
返回值: string
说明:将str进行用pad进行左补足到len位
举例:
select lpad('abc',10,'td') from score;
+-------------+--+
| _c0 |
+-------------+--+
| tdtdtdtabc |
+-------------+--+
注意:与GP,ORACLE不同,pad不能默认
177.ltrim
左边去空格函数:ltrim
语法: ltrim(string A)
返回值: string
说明:去除字符串左边的空格
举例:
select ltrim(' abc ') from score;
+-------+--+
| _c0 |
+-------+--+
| abc |
+-------+--+
178.map
Map类型构建: map
语法: map (key1, value1, key2, value2,…)
说明:根据输入的key和value对构建map类型
举例:
Create table test5 as select map('100','tom','200','mary')as t from score;
describe test5;
+-----------+---------------------+----------+--+
| col_name | data_type | comment |
+-----------+---------------------+----------+--+
| t | map<string,string> | |
+-----------+---------------------+----------+--+
t map<string,string>
****************************************
select t from test5;
{"100":"tom","200":"mary"}
+-----------------------------+--+
| t |
+-----------------------------+--+
| {"100":"tom","200":"mary"} |
+-----------------------------+--+
179.map_keys
180.map_values
181.matchpath
182.max
最大值统计函数: max
语法: maxcol)
返回值: double
说明:统计结果集中col字段的最大值
举例:
select max(tscore) from score;
+------+--+
| _c0 |
+------+--+
| 98 |
+------+--+
183.md5
184.min
最小值统计函数: min
语法: min(col)
返回值: double
说明:统计结果集中col字段的最小值
举例:
select min(tscore) from score;
+------+--+
| _c0 |
+------+--+
| 65 |
+------+--+
185.minute
日期转分钟函数: minute
语法: minute (string date)
返回值: int
说明:返回日期中的分钟。
举例:
select minute('2011-12-08 10:03:01') from score;
+------+--+
| _c0 |
+------+--+
| 3 |
+------+--+
186.month
日期转月函数: month
语法: month (string date)
返回值: int
说明:返回日期中的月份。
举例:
select month('2011-12-08 10:03:01') from score;
+-------+--+
| _c0 |
+-------+--+
| 2012 |
+-------+--+
select month('2011-08-08') from score;
+------+--+
| _c0 |
+------+--+
| 8 |
+------+--+
187.months_between
188.named_struct
189.negative
negative函数: negative
语法: negative(int a), negative(double a)
返回值: int double
说明:返回-a
举例:
select negative(-5) from score;
+------+--+
| _c0 |
+------+--+
| 5 |
+------+--+
select negative(8) from score;
+------+--+
| _c0 |
+------+--+
| -8 |
+------+--+
190.next_day
191.ngrams
192.noop
193.noopstreaming
194.noopwithmap
195.noopwithmapstreaming
196.not
逻辑非操作: NOT
语法: NOT A
操作类型:boolean
说明:如果A为FALSE,或者A为NULL,则为TRUE;否则为FALSE
举例:
select 1 from score where not 1=2;
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
197.ntile
198.nvl
199.or
逻辑或操作: OR
语法: A OR B
操作类型:boolean
说明:如果A为TRUE,或者B为TRUE,或者A和B均为TRUE,则为TRUE;否则为FALSE
举例:
select 1 from score where 1=2 or 2=2;
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
200.parse_url
URL解析函数:parse_url
语法: parse_url(string urlString, string partToExtract [, stringkeyToExtract])
返回值: string
说明:返回URL中指定的部分。partToExtract的有效值为:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
举例:
select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') score;
+---------------+--+
| score |
+---------------+--+
| facebook.com |
+---------------+--+
select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k1') from score;
+------+--+
| _c0 |
+------+--+
| v1 |
+------+--+
201.parse_url_tuple
202.percent_rank
203.percentile******************************
中位数函数:percentile
语法: percentile(BIGINT col, array(p1 [, p2]…))
返回值: array
说明:功能和上述类似,之后后面可以输入多个百分位数,返回类型也为array,其中为对应的百分位数。
举例:
select percentile(score,<0.2,0.4>) from score;
取0.2,0.4位置的数据
204.percentile_approx*****************************
近似中位数函数:percentile_approx
语法: percentile_approx(DOUBLE col, p [, B])
返回值: double
说明:求近似的第pth个百分位数,p必须介于0和1之间,返回类型为double,但是col字段支持浮点类型。参数B控制内存消耗的近似精度,B越大,结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时,结果为准确的百分位数
举例:
近似中位数函数:percentile_approx
语法: percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])
返回值: array
说明:功能和上述类似,之后后面可以输入多个百分位数,返回类型也为array,其中为对应的百分位数。
举例:
205.pi
206.pmod************************************
正取余函数: pmod
语法: pmod(int a, int b),pmod(double a, double b)
返回值: int double
说明:返回正的a除以b的余数
举例:
select pmod(9,4) from score;
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
select pmod(-9,4) from score;
+------+--+
| _c0 |
+------+--+
| 3 |
+------+--+
207.posexplode
208.positive
positive函数: positive
语法: positive(int a), positive(double a)
返回值: int double
说明:返回a
举例:
select positive(-10) from score;
+------+--+
| _c0 |
+------+--+
| -10 |
+------+--+
select positive(12) from score;
+------+--+
| _c0 |
+------+--+
| 12 |
+------+--+
209.pow
幂运算函数: pow
语法: pow(double a, double p)
返回值: double
说明:返回a的p次幂
举例:
select pow(2,4) from score;
+-------+--+
| _c0 |
+-------+--+
| 16.0 |
+-------+--+
210.power
幂运算函数: power
语法: power(double a, double p)
返回值: double
说明:返回a的p次幂,与pow功能相同
举例:
select power(2,4) from score;
+-------+--+
| _c0 |
+-------+--+
| 16.0 |
+-------+--+
211.printf
211.radians
212.rand
取随机数函数: rand
语法: rand(),rand(int seed)
返回值: double
说明:返回一个0到1范围内的随机数。如果指定种子seed,则会等到一个稳定的随机数序列
举例:
select rand() from score;
+----------------------+--+
| _c0 |
+----------------------+--+
| 0.40925741753953027 |
| 0.15321005624657547 |
| 0.7825141550181328 |
| 0.15724488988651275 |
| 0.46629705113247377 |
| 0.4956710047802584 |
| 0.8388469259931493 |
| 0.6773412687893476 |
+----------------------+--+
select rand() from score;
+----------------------+--+
| _c0 |
+----------------------+--+
| 0.7412186789144517 |
| 0.9575695740674802 |
| 0.7669451374638879 |
| 0.6666984506957401 |
| 0.9574783365591045 |
| 0.41277924162369073 |
| 0.36927615060008767 |
| 0.7300238176080147 |
+----------------------+--+
select rand(100) from score;
+----------------------+--+
| _c0 |
+----------------------+--+
| 0.7220096548596434 |
| 0.19497605734770518 |
| 0.6671595726539502 |
| 0.7784408674101491 |
| 0.6186076060240648 |
| 0.62309699131219 |
| 0.23675163488485773 |
| 0.48722715836911057 |
+----------------------+--+
select rand(100) from score;
+----------------------+--+
| _c0 |
+----------------------+--+
| 0.7220096548596434 |
| 0.19497605734770518 |
| 0.6671595726539502 |
| 0.7784408674101491 |
| 0.6186076060240648 |
| 0.62309699131219 |
| 0.23675163488485773 |
| 0.48722715836911057 |
+----------------------+--+
213.rank
214.reflect
215.reflect2
216.regexp
REGEXP操作: REGEXP
语法: A REGEXP B
操作类型: strings
描述:功能与RLIKE相同
举例:
hive> select 1 from emp_basic where ‘Teacher’ REGEXP ‘^e.*r$’;
1
217.regexp_extract
正则表达式解析函数:regexp_extract
语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明:将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符。
举例:
select regexp_extract(‘foothebar’, ‘foo(.?)(bar)’, 1) score;
the
select regexp_extract(‘foothebar’, 'foo(.?)(bar)’, 2) score;
bar
select regexp_extract(‘foothebar’, ‘foo(.?)(bar)’, 0) score;
foothebar
注意,在有些情况下要使用转义字符,下面的等号要用双竖线转义,这是java正则表达式的规则。
select data_field,
regexp_extract(data_field,’.?bgStart\=([^&]+)’,1) as aaa,
regexp_extract(data_field,’.?contentLoaded_headStart\=([^&]+)’,1) as bbb,
regexp_extract(data_field,’.?AppLoad2Req\=([^&]+)’,1) as ccc
from pt_nginx_loginlog_st
where pt = '2012-03-26’limit 2;
218.regexp_replace
正则表达式替换函数:regexp_replace
语法: regexp_replace(string A, string B, string C)
返回值: string
说明:将字符串A中的符合java正则表达式B的部分替换为C。注意,在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
举例:
select regexp_replace('foobar', 'oo|ar', '') from score;
+------+--+
| _c0 |
+------+--+
| fb |
+------+--+
219.repeat
重复字符串函数:repeat
语法: repeat(string str, int n)
返回值: string
说明:返回重复n次后的str字符串
举例:
select repeat('abc',5) from score;
+------------------+--+
| _c0 |
+------------------+--+
| abcabcabcabcabc |
+------------------+--+
220.reverse
字符串反转函数:reverse
语法: reverse(string A)
返回值: string
说明:返回字符串A的反转结果
举例:
select reverse('abcedfg') from score;
+----------+--+
| _c0 |
+----------+--+
| gfdecba |
+----------+--+
221.rlike
JAVA的LIKE操作: RLIKE
语法: A RLIKE B
操作类型: strings
描述:如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合JAVA正则表达式B的正则语法,则为TRUE;否则为FALSE。
举例:
1
注意:判断一个字符串是否全为数字:
hive>select 1from lxw_dual where ‘123456’ rlike ‘^\d+KaTeX parse error: Got function '\newline' with no arguments as superscript at position 1: \̲n̲e̲w̲l̲i̲n̲e̲’;
222.round
取整函数: round
语法: round(double a)
返回值: BIGINT
说明:返回double类型的整数值部分(遵循四舍五入)
举例:
select round(3.1415926) from score;
+------+--+
| _c0 |
+------+--+
| 3.0 |
+------+--+
select round(3.5) from score;
+------+--+
| _c0 |
+------+--+
| 4.0 |
+------+--+
create table test4 as select round(9542.158) score;
describe test4;
+-----------+------------+----------+--+
| col_name | data_type | comment |
+-----------+------------+----------+--+
| score | double | |
+-----------+------------+----------+--+
指定精度取整函数: round
语法: round(double a, int d)
返回值: DOUBLE
说明:返回指定精度d的double类型
举例:
select round(3.1415926,4) from score;
+---------+--+
| _c0 |
+---------+--+
| 3.1416 |
+---------+--+
223.row_number
224.rpad
右补足函数:rpad
语法: rpad(string str, int len, string pad)
返回值: string
说明:将str进行用pad进行右补足到len位
举例:
select rpad('abc',10,'td') from score;
+-------------+--+
| _c0 |
+-------------+--+
| abctdtdtdt |
+-------------+--+
225.rtrim
右边去空格函数:rtrim
语法: rtrim(string A)
返回值: string
说明:去除字符串右边的空格
举例:
select rtrim(' abc ') from score;
+-------+--+
| _c0 |
+-------+--+
| abc |
+-------+--+
226.second
日期转秒函数: second
语法: second (string date)
返回值: int
说明:返回日期中的秒。
举例:
select second('2011-12-08 10:03:01') from score;
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
227.sentences
228.sha2
229.sign
230.sin
正弦函数: sin
语法: sin(double a)
返回值: double
说明:返回a的正弦值
举例:
select sin(0.8) from score;
+---------------------+--+
| _c0 |
+---------------------+--+
| 0.7173560908995228 |
+---------------------+--+
231.size
232.sort_array
233.soundex
234.space
空格字符串函数:space
语法: space(int n)
返回值: string
说明:返回长度为n的字符串
举例:
select space(10) from score;
+-------------+--+
| _c0 |
+-------------+--+
| |
+-------------+--+
select length(space(10)) from score;
+------+--+
| _c0 |
+------+--+
| 10 |
+------+--+
235.split
分割字符串函数: split
语法: split(string str, stringpat)
返回值: array
说明:按照pat字符串分割str,会返回分割后的字符串数组
举例:
select split('abtcdtef','t') from score;
+-------------------+--+
| _c0 |
+-------------------+--+
| ["ab","cd","ef"] |
+-------------------+--+
236.sqrt
开平方函数: sqrt
语法: sqrt(double a)
返回值: double
说明:返回a的平方根
举例:
select sqrt(16) from score;
+------+--+
| _c0 |
+------+--+
| 4.0 |
+------+--+
237.stack
238.std
239.stddev
240.stddev_pop
总体标准偏离函数:stddev_pop
语法: stddev_pop(col)
返回值: double
说明:该函数计算总体标准偏离,并返回总体变量的平方根,其返回值与VAR_POP函数的平方根相同
举例:
241.stddev_samp
中位数函数:percentile
语法: percentile(BIGINT col, p)
返回值: double
说明:求准确的第pth个百分位数,p必须介于0和1之间,但是col字段目前只支持整数,不支持浮点数类型
举例:
242.str_to_map
243.struct
Struct类型构建: struct
语法: struct(val1, val2, val3,…)
说明:根据输入的参数构建结构体struct类型
举例:
create table test6 as select struct('tom','mary','tim')as t from score;
describe test6;
+-----------+----------------------------------------------+----------+--+
| col_name | data_type | comment |
+-----------+----------------------------------------------+----------+--+
| t | struct<col1:string,col2:string,col3:string> | |
+-----------+----------------------------------------------+----------+--+
t struct<col1:string,col2:string,col3:string>
select t from test6;
{"col1":"tom","col2":"mary","col3":"tim"}
+--------------------------------------------+--+
| t |
+--------------------------------------------+--+
| {"col1":"tom","col2":"mary","col3":"tim"} |
+--------------------------------------------+--+
244.substr
字符串截取函数:substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明:返回字符串A从start位置到结尾的字符串
举例:
select substr('abcde',3) from score;
+------+--+
| _c0 |
+------+--+
| cde |
+------+--+
select substring('abcde',3) from score;
+------+--+
| _c0 |
+------+--+
| cde |
+------+--+
select substr('abcde',-1) from score; (和ORACLE相同)
+------+--+
| _c0 |
+------+--+
| e |
+------+--+
245.substring
字符串截取函数:substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明:返回字符串A从start位置到结尾的字符串
举例:
select substr('abcde',3) from score;
+------+--+
| _c0 |
+------+--+
| cde |
+------+--+
select substring('abcde',3) from score;
+------+--+
| _c0 |
+------+--+
| cde |
+------+--+
select substr('abcde',-1) from score; (和ORACLE相同)
+------+--+
| _c0 |
+------+--+
| e |
+------+--+
字符串截取函数:substr,substring
语法: substr(string A, int start, int len),substring(string A, intstart, int len)
返回值: string
说明:返回字符串A从start位置开始,长度为len的字符串
举例:
select substr('abcde',3,2) from score;
+------+--+
| _c0 |
+------+--+
| cd |
+------+--+
select substring('abcde',3,2) from score;
+------+--+
| _c0 |
+------+--+
| cd |
+------+--+
select substring('abcde',-2,2) from score;
+------+--+
| _c0 |
+------+--+
| de |
+------+--+
246.sum
总和统计函数: sum
语法: sum(col), sum(DISTINCT col)
返回值: double
说明: sum(col)统计结果集中col的相加的结果;sum(DISTINCT col)统计结果中col不同值相加的结果
举例:
select sum(tscore) from score;
+------+--+
| _c0 |
+------+--+
| 633 |
+------+--+
select sum(distinct tscore) from score;
+------+--+
| _c0 |
+------+--+
| 403 |
+------+--+
247.tan
248.to_date
日期时间转日期函数:to_date
语法: to_date(string timestamp)
返回值: string
说明:返回日期时间字段中的日期部分。
举例:
select to_date('2011-12-08 10:03:01') from score;
+-------------+--+
| _c0 |
+-------------+--+
| 2011-12-08 |
+-------------+--+
249.to_unix_timestamp
250.to_utc_timestamp
251.translate
252.trim
去空格函数:trim
语法: trim(string A)
返回值: string
说明:去除字符串两边的空格
举例:
select trim(' abc ') from score;
+------+--+
| _c0 |
+------+--+
| abc |
+------+--+
253.trunc
254.ucase
字符串转大写函数:upper,ucase
语法: upper(string A) ucase(string A)
返回值: string
说明:返回字符串A的大写格式
举例:
select upper('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| ABSED |
+--------+--+
select ucase('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| ABSED |
+--------+--+
255.unbase64
256.unhex
反转十六进制函数: unhex
语法: unhex(string a)
返回值: string
说明:返回该十六进制字符串所代码的字符串
举例:
select unhex('616263') from score;
+------+--+
| _c0 |
+------+--+
| abc |
+------+--+
select unhex('11') from score;
+------+--+
| _c0 |
+------+--+
| |
+------+--+
select unhex(616263) from score;
+------+--+
| _c0 |
+------+--+
| abc |
+------+--+
257.unix_timestamp
获取当前UNIX时间戳函数:unix_timestamp
语法: unix_timestamp()
返回值: bigint
说明:获得当前时区的UNIX时间戳
举例:
select unix_timestamp() from score;
+-------------+--+
| _c0 |
+-------------+--+
| 1598619427 |
+-------------+--+
日期转UNIX时间戳函数:unix_timestamp
语法: unix_timestamp(string date)
返回值: bigint
说明:转换格式为"yyyy-MM-ddHH:mm:ss"的日期到UNIX时间戳。如果转化失败,则返回0。
举例:
select unix_timestamp('2011-12-07 13:01:03') from score;
+-------------+--+
| _c0 |
+-------------+--+
| 1323280863 |
+-------------+--+
指定格式日期转UNIX时间戳函数:unix_timestamp
语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明:转换pattern格式的日期到UNIX时间戳。如果转化失败,则返回0。
举例:
select unix_timestamp('20111207 13:01:03','yyyyMMddHH:mm:ss') from score;
+-------------+--+
| _c0 |
+-------------+--+
| 1323280863 |
+-------------+--+
258.upper
字符串转大写函数:upper,ucase
语法: upper(string A) ucase(string A)
返回值: string
说明:返回字符串A的大写格式
举例:
select upper('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| ABSED |
+--------+--+
select ucase('abSEd') from score;
+--------+--+
| _c0 |
+--------+--+
| ABSED |
+--------+--+
259.uuid
260.var_pop**********************************
非空集合总体变量函数:var_pop
语法: var_pop(col)
返回值: double
说明:统计结果集中col非空集合的总体变量(忽略null)
举例:
261.var_samp*****************************
非空集合样本变量函数:var_samp
语法: var_samp (col)
返回值: double
说明:统计结果集中col非空集合的样本变量(忽略null)
举例:
262.variance
263.version
264.weekofyear
日期转周函数:weekofyear
语法: weekofyear (string date)
返回值: int
说明:返回日期在当前的周数。
举例:
±-----±-+
| _c0 |
±-----±-+
| 49 |
±-----±-+
265.when
266.windowingtablefunction
267.xpath
268.xpath_boolean
269.xpath_double
270.xpath_float
271.xpath_int
272.xpath_long
273.xpath_number
274.xpath_short
275.xpath_string
276.year
日期转年函数: year
语法: year(string date)
返回值: int
说明:返回日期中的年。
举例:
select year('2011-12-08 10:03:01') from score;
+-------+--+
| _c0 |
+-------+--+
| 2011 |
+-------+--+
select year('2012-12-08') from score;
+-------+--+
| _c0 |
+-------+--+
| 2012 |
+-------+--+
218.|
位或操作: |
语法: A | B
操作类型:所有数值类型
说明:返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系)。
举例:
select 4 | 8 from score;
+------+--+
| _c0 |
+------+--+
| 12 |
+------+--+
select 6 | 8 from score;
+------+--+
| _c0 |
+------+--+
| 14 |
+------+--+
219.~
位取反操作: ~
语法: ~A
操作类型:所有数值类型
说明:返回A按位取反操作的结果。结果的数值类型等于A的类型。
举例:
select ~6 from score;
+------+--+
| _c0 |
+------+--+
| -7 |
+------+--+
select ~4 from score;
+------+--+
| _c0 |
+------+--+
| -5 |
+------+--+