hive函数积累

1. 时间转标准格式：

（时间字段：‘20221213061224’）

from_unixtime(unix_timestamp(‘时间字段’,’yyyyMMddHHmmss’), ‘yyyy-MM-dd HH:mm:ss’)

（时间字段：‘2022年12月13日 06时12分24秒’）

replace(replace(replace(replace(replace(replace(‘时间字段’,’年’,’-’),’月’,’-‘),’日’,’‘),’时’,’:’),’分’,’:’),’秒’,’’),’时间字段’)

2.同一字段的内容合并成一条记录：

concat_ws(‘,’,collect_set(‘字段名’))  from ‘table’ group by ‘**’

说明：将某个字段的多行数据，合并成一行。（按’订单号’合并’项目’）

公式：regexp_replace(cast(collect_list([项目]) over(partition by [订单号]) as string),'(?:\\[|\\])','')

3.划分字段：如（江西 南昌）：

江西：substr(‘字段名’，1，instring(‘字段名’,’ ’)-1)

南昌：substr(‘字段名’，instring(‘字段名’)+1)

4.将一行记录拆分成多行：

select 表字段 from table lateral view explode (split(表字段,’,’)) 新表名 as 新表字段

5.窗口函数，根据单独一个字段分组：

count(‘计算字段’) over(partition by ‘分组字段’) as ‘新字段名’

6.返回参数中的第一个非空值和非空串:

ccoalesce(‘字段1’,’字段2’)

//if所有值都为null或空串，则返回空串

7.空值+数值:

nvl(‘字段1’,0)+nvl(‘字段2’,0)

8.从字符串中提取身份证:

regexp_extract(‘字段’,’\\d{18}|(\\d{17}X)|(\\d{17}x)’,0)

9.计算年龄：

floor(month_diff(now(),from_unixtime(unix_timestamp(substr(‘身份证号’,7,8),‘yyyyMMdd’)))/12)

10.将分组中的某列转换成一个数组：

select name,collect_list(score) from table group by name（不会去重）

select name,collect_set(score) from table group by name（去重）

11.字段类型转换：

cast(‘字段’ as float)

12.判断一个字符串是否全为数字：

select 1 from lxw_dual where '123456' rlike '^\\d+$';

13.图平台挖掘边：

select  ‘sfz’ from key,

‘同sfz’ as to_key,

count(1) as 同次数，

concat(‘[[{indexName:\’事件中文名\’,eventid:\’’,concat_ws(‘\’},{indexName:\’事件中文名\’,eventide:\’’,collect_list(‘object_id’)),’\’}]]’) as event_ids,

md5(concat(‘sfz’,’同sfz’)) as object_key,

current_timestamp time from ‘SQL’

group by ‘sfz’,’同sfz’

14.图平台明细边和汇总边：

（注意事项：

1.from_key和to_key中concat实体英文名后面加‘/’

2.汇总边接入数据时，修改detail_schema和detail_ids为英文名）

select concat(‘st_people/’,’实体主键’1) from_key,

concat(‘st_car/’,’实体主键2’) as to_key,

md5(concat(‘实体主键1’,’实体主键2’)) as object_key,

‘明细边英文名’ as detail_schema,

count(1) as 次数，

concat(‘[\’’,concat_ws(‘\’,\’’,collect_list(‘明细边主键’)),’\’]’) as detail_ids

from ‘SQL’

group by ‘实体主键1’，’实体主键2’

select object_key,

from_key,

to_key,

cast(count(‘实体主键1’) over(partition by ‘实体主键1’,’实体主键2’) as int) as 关系次数

from ‘SQL’

15.字段判空：

‘字段’ is not null and trim(‘字段’)!=’’

16.内容关联：

‘字段1’like concat(‘%’,’字段2’,’%’)

业务场景如下：A like '%xxx%' or A like '%yyy%' or A like '%ooo%' or ....等这些公式完全可以用 A rlike '(?:xxx|yyy|ooo|...)' 来代替。

17.身份证正则表达式提取：

1）提取15位或18位标准身份证：

regexp_extract(`字段`,’([1-9]{1}[0-9]{5}(19|20)[0-9]{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)([0-9]{3})|()’,0) as 身份证正则提取

2）提取15位或18位身份证：

REGEXP_EXTRACT(`身份证`,'([1-9]{1}[0-9]{5}(19|20)[0-9]{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)([0-9]{3}[0-9Xx]))|([1-9]{1}[0-9]{5}[0-9]{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)[0-9]{2}[0-9Xx])',0) as 身份证4

18.手机号码正则表达式提取：

regexp_extract(`字段`,’^((13[0-9]|14[5-9]|15[0-35-9]|16[5-6]|17[0-8]|18[0-9]|19[158-9])[0-9]{8})’,0) as 手机号码正则提取