hivesubstr函数_数据分析⼯具篇——HQL函数及逻辑
本篇⽂章我们梳理⼀下hive常⽤的函数，对于hive⽽⾔，常⽤的函数并不是特别多，往往记住关键⼏个，就可以解决80%的问题，这也是⼤
家喜欢hive的原因，那么，常⽤的函数有哪些呢？
时间函数
1)时间格式转化
常⽤的⽇期格式主要有两个：时间戳和⽇期格式，时间戳便于计算，⽇期格式便于阅读，两者各有利弊，所以需要相互转换，转换的对应函
数主要有：
from_unixtime(timestamp,dateformat)：将时间戳转化为⽇期格式，格式必须是10位，毫秒级的时间戳需要⽤cast转化成秒级。unix_timestamp(date,dateformat)：⽇例如：
时间戳转⽇期格式：
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as date_time, from_unixtime(1537924406,'yyyy-MM-dd') as date_time1from dual;
如果时间戳是毫秒级，则字段修改为：
from_unixtime(cast(unix_timestamp()/1000 as int),'yyyy-MM-dd HH:mm:ss') as date_time
⽇期格式转时间戳：
select unix_timestamp() as time_stamp, --返回当前时间戳； unix_timestamp('2018-09-26 9:13:26','yyyy-MM-ddHH:mm:ss') as time_stamp1from dual;
作为⼀个数据，在不同系统之间游⾛，有没有经常遇到时间格式不匹配的情况，所以需要做格式的转化：
date_format(string/date,dateformate)：把字符串或者⽇期转成指定格式的⽇期。
⽇期格式调整：
select date_format('2018-09-12','yyyy-MM-dd HH:mm:ss') as date_time, date_format('2018-09-12','yyyyMMdd') as date_time1from dual;--2018-09-12 00
有没有发现⼀个问题，date_format只能从yyyy-MM-dd格式转化成其他格式，对于其他类型，例如：yyyyMMdd，则不能处理，所以需
要灵活⼀点，将各个格式的时间转化成时间戳，再通过时间戳转化成其他格式：
select from_unixtime(unix_timestamp(substr('20191007000000',1,8),'yyyyMMdd'),'yyyy-MM-dd');
如此⼀来，各种格式都可以转化。
2)⽇期的加减差运算：
⽇期的加减是对⽇期格式数据的基本运算，常⽤的是时间差计算、加⼀天、减⼀天等。
时间差计算：
datediff(date,date1)；⽇期差，即两个⽇期差⼏天。months_between(date,date1)；两个⽇期差⼏个⽉，⽤法⼀致。select datediff('2015-05-22','2015-05-29') as da
在原⽇期上加n天：
date_add：在现在⽇期上增加天数，
add_months是增加⽉份，⽤法⼀致。
select date_add('2015-05-22 15:34:23',2); --2015-05-24
在原⽇期上减n天：
date_sub：在现在⽇期上减少天数。
select date_sub('2015-05-22 15:34:23',2); --2015-05-20
3)时间截取：
如果我们获取到⼀个完整的时间，但是只想⽤其中的⼀部分，势必牵扯到时间截取的功能，常见的时间截取主要是如下⼏个函数：
to_date：获取完整时间中的⽇期部分：
select to_date('2015-06-01 15:34:23'); --2015-06-01
year：获取完整时间中的年份：
select year('2015-05-22 15:34:23'); --2015
month：获取完整时间中的⽉份：
select month('2015-05-22 15:34:23'); --5
day：获取完整时间中的⽇期：
select day('2015-05-22 15:34:23'); --22
4)⽇期中其他灵活操作：
⽇期除了计算多少天，截取某⼀段，还会牵扯⼀些⽐较灵活的操作⽅法，例如：判断当天是周⼏，判断年周、⽉周，当⽉的最后⼀天和第⼀天，下n天，上n天等。
获取⼀年中的第⼏周：weekofyear：
select weekofyear('2015-05-22 15:34:23'); --21
获取当⽉中的第⼏周：
select weekofyear('2015-05-22')-weekofyear(trunc('2015-05-22', ‘MM’))+1;
这其中有⼀个有意思的点：每⼀周是按照周⼀到周⽇为⼀个完整周，与英美的周⽇到周六的逻辑有些不⼀样，所以在如下⽇历中：
11⽉1⽇为周⽇，按照代码中的逻辑与10⽉31⽇是⼀周。
判断当天为周⼏：
Select 8-datediff(next_day(‘2020-12-20’, ‘MO’), ‘2020-12-20’) as week;
获取⽉末最后⼀天:
last_day(date)：
select last_day('2018-09-30') as date_time, last_day('2018-09-27 21:16:13') as date_time1from dual;--2018-09-30
获取⽉初、年初：
unix时间戳转换日期格式trunc(date,format)
format:MONTH/MON/MM, YEAR/YYYY/YY
select trunc('2018-09-27','YY') as date_time,--返回年初 trunc('2018-09-27 21:16:13','MM') as date_time1 --返回⽉初from dual;--2018-01-01和2018-09-01
当前⽇期下个星期X的⽇期:
next_day(date,formate) format：英⽂星期⼏的缩写或者全拼。
select next_day('2018-09-27','TH') as date_time, next_day('2018-09-27 21:16:13','TU') as date_time1from dual;--2018-10-04和2018-10-02
汇总SQL为(下⽂SQL为对应的⽇期写法，可以作为上⾯简单罗列的补充版)：
select day -- 时间 ,date_add(day,1 - dayofweek(day)) as week_数据运算总结
Hive中数据⼀般是可以⽤常规的运算符号表⽰的，例如：“加减乘除”⽤“+-*/”表⽰，但是对于初次之外的运算需要⽤到对应的函数：
sum()：求和；count()：求数据量；avg()：求平均值；distinct：求不同值数(去重)；min：求最⼩值；max：求最⼤值；pmod(int a, int b)：返回a除以b的余数的绝对值在数据运算过程中经常会遇到⽤字符串形式存储的数值，计算过程中需要将其转化成数值型，然后进⾏运算，其中运算⽅法为：
cast(aaa as int)：将string转化成int；cast(aaa as decimal(10, 2))：将string转化成float，保留两位⼩数；
当然也可以将数值转化成字符串：
cast(aaa as string)：将int转化成string；
除了上⾯这些⽅法外，hive在数据运算⽅⾯还有⼤于、⼩于、等于等操作，这些操作都有对应的运算符号表⽰，在此就不多描述了。
字符串操作总结
字符串可以说是最常⽤的处理思路，在特征处理、数据清洗过程中⾼频使⽤，这些函数⽤的好会为接下来数据建模、统计运算带来极⼤便
利。下⾯我们来梳理⼀下常⽤的字符串处理⽅法：
1)空格处理：
trim(String A)：去除A两侧的空格；ltrim(String A)：去除左边空格；rtrim(String A)：去除右边空格select trim('abc') from lxw_dual;
2)单个字符串操作：
reverse('abcde')=edcba：字符串反转
lower：转成⼩写
select lower('Hive'); --hive
upper：转成⼤写
select lower('Hive'); --HIVE
length：长度
select length('Hive'); --4
substr：求⼦串
select substr('hive',2); --iveselect substr('hive',2,1); --i
lpad：左填充
对hive填充到10位，补位⽤#
select lpad('hive',10,'#'); -->#hive
rpad：右填充
select rpad('hive',10,'#'); --hive>#
regexp_extract()：正则表达式处理
regexp_extract(string subject, string pattern, int index); --函数的应⽤；
将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符:
select clientcode, regexp_extract(filterlist,'(filtertype"\\:")(\\d+)(",)',2) as filtertypefrom tmp_action_click
在正则表达式中，经常会⽤到贪⼼算法(.*?)和⾮贪⼼算法(.*)⽤法，⽤来清洗字符串中的内容。
正则表达式替换函数：
regexp_replace('foobar','oo|ar','')=fb #替换oo|ar为空字符
有意思的是，hive中并没有直接的replace函数，如果是整个字符串的判断可以⽤case when的⽅式操作，如果只替换其中⼀部分可以⽤
regexp_replace操作。
get_json_object(string json_string,string path)
该函数的第⼀个参数是json对象变量，第⼆个参数使⽤$表⽰json变量标识，然后⽤.或[]读取对象或数组。
例如：
如果json为：
Json={"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} }, "email":"amy@only_for_js 对应的SQL为：
SELECT get_json_object(json, '$.owner') FROM src_json;--amySELECT get_json_object(json, '$.store.fruit\[0]') FROM src_json;--{"weight":8,"type":"apple
第⼆个常⽤的函数为：
json_tuple(string json_string,string k1,)
该函数的第⼀个参数是json对象变量，之后的参数是不定长参数，是⼀组键，返回值是元组，该⽅法⽐get_json_object⾼效，因为
可以在⼀次调⽤中输⼊多个键值。
select src.timestamp, b.*from src_json srclateral view json_tuple(src.json, 'email', 'owner') b as f1, f2;
另外，单个字符串如果是url，可以直接使⽤pares_url函数，个⼈感觉这个函数⽐较鸡肋，看起来很有⽤，但是⼤部分功能可以⽤string
拼接的⽅式搞定。
URL解析函数：parse_url
语法:
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
mysql时间比较_mysql中时间比较的实现
« 上一篇
PostgreSQL详解
下一篇 »
发表评论