hive SQL_蓉儿的草莓酸奶的博客

发布时间：2021-12-26 15:58:15

617 阅读

0 评论

文章标签：函数语法类型

hive是通过获取到时间戳的形式来获取到时间，然后通过函数FROM_UNIXTIME 转化为日期时间的形式

hive查询时间

查询当前月份	`SELECT SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,7) AS 当前月份;`	2021-09
	`SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());`	2021-09-17 09:51:42
获取当前时间戳bigint unix_timestamp()	`select unix_timestamp()`
bigint unix_timestamp(string_date) 将格式“yyyy-MM-dd HH:mm:ss”的字符串转成时间戳，如果格式不对返回null	`select unix_timestamp('2021-09-17 11:02:21')`
bigint unix_timestamp(string date,string pattern) 将指定时间字符串格式转成时间戳，如果格式不对返回null	`select unix_timestamp('20181016','yyyyMMdd')`
string from_unixtime(bigint,format) 将时间戳转为format格式，可以为“yyyy-MM-dd HH:mm:ss”,"yyyy-MM-dd HH","yyyy-MM-dd","yyyyMMdd"等	`select from_unixtime(1539691341,'yyyy-MM-dd HH:mm:ss')`
string to_date(string timestamp) 返回时间字符串的日期部分	`select to_date('2018-10-16 20:02:21')`
string datediff(string enddate,string startdate) 计算两个日期相差的天数	`select datediff('2018-10-16 11:22:00','2018-06-08 22:11:00');`
string date_add(string startdate,int days) select date_sub('2018-10-16',6); 开始时间加上days 开始时间减去days	`select date_add('2018-10-16',6);` `select date_sub('2018-10-16',6);`
current_date 获取当前日期	`select current_date()`
current_timestamp 获取当前时间戳	`select current_timestamp()`
year(string date) month(string date) day(string date) hour(string date) minute(string date) second(string date) weekofyear(string date) 返回日期中的年\月\日\小时\分钟\秒当前周数	`select weekofyear('2019-07-11 10:20:30');`

逻辑运算
AND	语法: A AND B 操作类型：boolean 说明：如果A和B均为TRUE，则为TRUE；否则为FALSE。如果A为NULL或B为NULL，则为NULL	`select 1=1 and 1=2`	false
OR	语法: A OR B 操作类型：boolean 说明：如果A为TRUE，或者B为TRUE，或者A和B均为TRUE，则为TRUE；否则为FALSE	`select 1=1 or 1=2`	true
NOT	语法: NOT A 操作类型：boolean 说明：如果A为FALSE，或者A为NULL，则为TRUE；否则为FALSE	`select not 1=2`	true

运算
运算	注释	代码	运算结果
is null is not null	语法: A IS NULL 操作类型: 所有类型描述: 如果表达式A的值为NULL，则为TRUE；否则为FALSE 语法: A IS NOT NULL 操作类型: 所有类型描述: 如果表达式A的值为NULL，则为FALSE；否则为TRUE	`select null is null` `select 1 is null` `select 1 is not null` `select null is not null`	true false true false
LIKE	语法: A LIKE B 操作类型: strings 描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。	`select 'football' like 'foot%';` `select 'football' like 'foot____';` `select NOT 'football' like 'fff%';`	true ture ture
REGEXP	语法: A REGEXP B 操作类型: strings 描述: 功能与RLIKE相同 (java语法: A RLIKE B 操作类型: strings 描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。)	`select 'footbar' REGEXP '^f.*r$';`	true
按位与&	语法: A & B 操作类型：所有数值类型说明：返回A和B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。	`select 4 & 8`	0
位或操作\|	语法: A \| B 操作类型：所有数值类型说明：返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）	`select 4 \| 8`	12
位异或^	语法: A ^ B 操作类型：所有数值类型说明：返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。	`select 4 ^ 8`	12
位取反操作	语法: ~A 操作类型：所有数值类型说明：返回A按位取反操作的结果。结果的数值类型等于A的类型。	`select ~6` `select ~4`	-7 -5

数值计算
函数	语法	代码	运算结果
取整函数round	语法: round(double a) 返回值: BIGINT 说明: 返回double类型的整数值部分（遵循四舍五入）	`select round(3.1415926)` `select round(3.5)`	3 4
指定精度取整函数round	语法: round(double a, int d) 返回值: DOUBLE 说明: 返回指定精度d的double类型	`select round(3.1415926,4)`	3.1416
向下取整函数floor	语法: floor(double a) 返回值: BIGINT 说明: 返回等于或者小于该double变量的最大的整数	`select floor(3.1415926)` `select floor(25)`	3 25
向上取整函数ceil\ceiling	语法: ceil(double a) 返回值: BIGINT 说明: 返回等于或者大于该double变量的最小的整数	`select ceil(3.1415926)` `select ceil(46)`	4 46
取随机数函数	语法: rand(),rand(int seed) 返回值: double 说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列	`select rand(25)` `select rand()` `select rand(100)`	0.545622741591941 0.000471474603038 0.183029190256739
自然指数函数exp	语法: exp(double a) 返回值: double 说明: 返回自然对数e的a次方	`select exp(2)` `select ln(7.38905609893065)`	7.38905609893065 2
以10为低的对数函数以2为低的对数函数对数函数	语法: log10(double a) 返回值: double 说明: 返回以10为底的a的对数语法: log2(double a) 返回值: double 说明: 返回以2为底的a的对数语法: log(double base, double a) 返回值: double 说明: 返回以base为底的a的对数	`select log10(100)` `select log2(8)` `select log(4,256)`	2 3 4
幂运算函数 pow\power	语法: pow(double a, double p) 返回值: double 说明: 返回a的p次幂	`select pow(2,4)`	16
开平方函数sqrt	语法: sqrt(double a) 返回值: double 说明: 返回a的平方根	`select sqrt(16)`	4
二进制函数bin 十六进制函数hex 反转十六进制函数unhex	语法: bin(BIGINT a) 返回值: string 说明: 返回a的二进制代码表示语法: hex(BIGINT a) 返回值: string 说明: 如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示语法: unhex(string a) 返回值: string 说明: 返回该十六进制字符串所代码的字符串	`select bin(7)` `select hex(17)` `select hex('abc')` `select unhex('616263')`	111 11 616263 abc
进制zh转换函数conv	语法: conv(BIGINT num, int from_base, int to_base) 返回值: string 说明: 将数值num从from_base进制转化到to_base进制	`select conv(17,10,16)`	11
绝对值函数abs	语法: abs(double a) abs(int a) 返回值: double int 说明: 返回数值a的绝对值	`select abs(-3.9)` `select abs(10.9)`	3.9 10.9
正取余函数pmod 正弦函数sin 反正弦函数asin	语法: pmod(int a, int b),pmod(double a, double b) 返回值: int double 说明: 返回正的a除以b的余数语法: sin(double a) 返回值: double 说明: 返回a的正弦值语法: asin(double a) 返回值: double 说明: 返回a的反正弦值	`select pmod(-9,4)` `select pmod(9,4)` `select sin(0.8)` `select asin(0.7173560908995228)`	3 1 0.7173560908995228 0.8
余弦函数cos 反余弦函数acos	语法: cos(double a) 返回值: double 说明: 返回a的余弦值语法: acos(double a) 返回值: double 说明: 返回a的反余弦值	`select cos(0.9)` `select acos(0.6216099682706644)`	0.6216099682706644 0.9000000000000001
positiveh函数 negativeh函数	语法: positive(int a), positive(double a) 返回值: int double 说明: 返回a 语法: negative(int a), negative(double a) 返回值: int double 说明: 返回-a	`select positive(-5) select positive(8)` `select negative(-5) select negative(8)`	-5 8 5 -8

函数类型举例代码结果

1.字符串A的长度 length(string A)

2.字符串反转函数reverse(string A)

3.字符串连接函数concat(string A, string B…)

4.带分隔符字符串连接函数concat_ws(string SEP, string A, string B…)。返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符。

5.字符串截取函数substr(string A, int start),substring(string A, int start)

返回字符串A从start位置到结尾的字符串

select length('flora')

select reverse('flora')

select concat('abc','def','gh','flora')

select concat_ws(',','flora','deep','sp')

select substr('abcde',3) 
select substring('abcde',3)

select substr('abcde',-1)

6.字符串转大写函数upper(string A) ucase(string A)

7.字符串转小写lower(string A) lcase(string A)

select upper('flora')

select lcase('abSEd')

8.去空格函数trim

trim(string A)去除字符串两边的空格

ltrim(string A)去除字符串左边的空格

rtrim(string A)去除字符串右边的空格

select trim(' abc ')

select ltrim(' abc ')

select rtrim(' abc ')

9.正则表达式替换函数regexp_replace(string A, string B, string C)

select regexp_replace('foobar', 'oo|ar', '1')

10.正则表达式解析函数regexp_extract(string subject, string pattern, int index)

select regexp_extract('foothebar', 'foo(.*?)(bar)', 1)

select regexp_extract('foothebar', 'foo(.*?)(bar)', 2)

select regexp_extract('foothebar', 'foo(.*?)(bar)',0)

11.URL解析函数

parse_url(string urlString, string partToExtract [, string keyToExtract])

返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

 select parse_url('https://www.iteblog.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')

select parse_url('https://www.iteblog.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')

12.json解析函数

get_json_object(string json_string, string path)

解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

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')

13空格字符串函数

space(int n)

返回长度为n的字符串

select space(10)

select length(space(10))

NULL

14.重复字符串函数

repeat(string str,int n)

返回重复n次后的str字符串

select repeat('flora',3)

15.首字符ascii码函数

ascii(string str)

返回字符串str第一个字符的ascii码

select ascii('flora')

16.左补足函数

lpad(string str, int len, string pad)

右补足函数

rpad(string str, int len, string pad)

将str进行用pad进行左\右补足到len位

select lpad('flora',10,'de')

select rpad('flora',10,'de')

17.分割字符串函数

split(string str, string pat)

按照pat字符串分割str，会返回分割后的字符串数组

select split('abtcdtef','t')

select split('floraovdeepov','o')

18.集合查找函数

find_in_set(string str, string strList)

返回值: int

返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0

select find_in_set('ab','ef,ab,de')

select find_in_set('ov','ov,flora,ov,deep,ov')

select find_in_set('at','ef,ab,de')

1.个数统计函数count

语法: count(*), count(expr), count(DISTINCT expr[, expr_.])

说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数

2.总和统计函数sum

语法: sum(col), sum(DISTINCT col)

说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果

3.平均值统计函数avg

语法: avg(col), avg(DISTINCT col)

说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值

4.最小值min

语法: min(col)

说明: 统计结果集中col字段的最小值

5.最大值max

语法: max(col)

select count(*)

select count(distinct t)

select sum(t)

select sum(distinct t)

select avg(t)

select avg (distinct t)

select min(t)

select max(t)

6.非空集合总体变量函数

语法: var_pop(col)
返回值: double
说明: 统计结果集中col非空集合的总体变量（忽略null）

非空集样本变量函数

语法: var_samp (col)
返回值: double
说明: 统计结果集中col非空集合的样本变量（忽略null）

7.总体标准偏离函数stddev_pop(col)

语法: stddev_pop(col)
返回值: double
说明: 该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同

8.样本标准偏离函数stddev_samp (col)

语法: stddev_samp (col)
返回值: double
说明: 该函数计算样本标准偏离

9.中位数函数

语法: percentile(BIGINT col, p)
返回值: double
说明: 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型

10.中位数函数

语法: percentile(BIGINT col, array(p1 [, p2]…))
返回值: array<double>
说明: 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array<double>，其中为对应的百分位数。

11.近似中位数函数

语法: percentile_approx(DOUBLE col, p [, B])
返回值: double
说明: 求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数

12.近似中位数函数

语法: percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])
返回值: array<double>
说明: 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array<double>，其中为对应的百分位数。

13.直方图

语法: histogram_numeric(col, b)
返回值: array<struct {‘x’,‘y’}>
说明: 以b为基准计算col的直方图信息。

select histogram_numeric(100,5)

条件函数代码运行结果

1.IF函数

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

select if(1=2,100,200)

select if(1=1,100,200)

2.非空查找函数

COALESCE(T v1, T v2, …)

返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

 select COALESCE(null,'100','50')

3.条件判断函数

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

 Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end

4条件判断函数

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e

select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end

select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end

复合类型构建操作
类型	注释	代码	运行结果
MAP类型构建函数	语法: map (key1, value1, key2, value2, …) 说明：根据输入的key和value对构建map类型	`select map('100','tom','200','mary') as t`	{"100":"tom","200":"mary"}
Struct类型构建函数	语法: struct(val1, val2, val3, …) 说明：根据输入的参数构建结构体struct类型	`select struct('tom','mary','tim') as t create table struct_test2 as select * from (select struct('tom',2.3,'tim') as t union all select struct('tom',2.3,2) as t)tt; drop table struct_test show create table struct_test2`	{"col1":"tom","col2": "mary","col3":"tim"}
array类型构建函数	语法: array(val1, val2, …) 说明：根据输入的参数构建数组array类型	`select array("tom","mary","tim") as t`	["tom","mary","tim"]

复杂类型访问操作
类型	注释	代码	运行结果
MAP类型访问	语法: M[key] 操作类型: M为map类型，key为map中的key值说明：返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'	`select map('100','tom','200','mary') as t select t['200'],t['100']`	tom mary tim
Struct类型访问	语法: S.x 操作类型: S为struct类型说明：返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段	`select array("tom","mary","tim") as t select t[0],t[1],t[2] from iteblog;`	mary tom
array类型访问	语法: A[n] 操作类型: A为array类型，n为int类型说明：返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'	`select struct('tom','mary','tim') as t struct<col1:string ,col2:string,col3:string> select t.col1,t.col3 from iteblog;`	tom tim

复杂类型长度统计函数
MAP类型长度函数	arrsy类型长度函数	cast类型转换函数
语法: size(Map<k .V>) 返回值: int 说明: 返回map类型的长度	语法: size(Array<T>) 返回值: int 说明: 返回array类型的长度	类型转换函数: cast 语法: cast(expr as <type>) 返回值: Expected "=" to follow "type" 说明: 返回转换后的数据类型
`select size(map('100','tom','101','mary'))`	`select size(array('100','101','102','103'))`	`select cast(1 as bigint)`