hive 手机号 清洗 hive 匹配手机号

对array数据处理

with tablea as

(

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition

union all

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition

union all

select ‘中间状态客群名’ as group_condition

)

select group_condition from tablea;

with tablea as
(
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition
union all
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition
union all
select ‘中间状态客群名’ as group_condition
)
select group_condition,get_json_object(group_condition,‘$.con.fd$.con.fd’) is not null
union all
select group_condition, get_json_object(group_condition,‘$.con.sub.fd$.con.sub.fd’) is not null;

with tablea as

(

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition

union all

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition

union all

select ‘中间状态客群名’ as group_condition

)

select group_condition,get_json_object(group_condition,‘$.con.fd$.con.fd’),‘"’,‘’),‘\[|\]’,‘’)

,c1

from tablea

–where get_json_object(group_condition,‘$.con.fd$.con.fd’) ,‘"’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1

union all

select group_condition,get_json_object(group_condition,‘$.con.sub.fd$.con.sub.fd’),‘"’,‘’),‘\[|\]’,‘’)

,c1

from tablea

–where get_json_object(group_condition,‘$.con.sub.fd$.con.sub.fd’) ,‘"’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1;

with tablea as

(

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition

union all

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition

union all

select ‘中间状态客群名’ as group_condition

) ,

asd as (select group_condition,get_json_object(group_condition,‘$.con.fd$.con.fd’),‘"’,‘’),‘\[|\]’,‘’)

,c1

from tablea

–where get_json_object(group_condition,‘$.con.fd$.con.fd’) ,‘"’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1

union all

select group_condition,get_json_object(group_condition,‘$.con.sub.fd$.con.sub.fd’),‘"’,‘’),‘\[|\]’,‘’)

,c1

from tablea

–where get_json_object(group_condition,‘$.con.sub.fd$.con.sub.fd’) ,‘"’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1)

select * from asd;

with tablea as

(

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition

union all

select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition

union all

select ‘中间状态客群名’ as group_condition

) ,

asd as (select group_condition,get_json_object(group_condition,‘$.con.fd$.con.fd’),‘"’,‘’),‘\[|\]’,‘’)

,c1

from tablea

–where get_json_object(group_condition,‘$.con.fd$.con.fd’) ,‘"’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1

union all

select group_condition,get_json_object(group_condition,‘$.con.sub.fd$.con.sub.fd’),‘"’,‘’),‘\[|\]’,‘’)

,c1

from tablea

–where get_json_object(group_condition,‘$.con.sub.fd$.con.sub.fd’) ,‘"’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1)

select c1,count(1) from asd group by c1;

截取数字、去除固定文字、取文字中数字

select regexp_extract(‘501001.OF’,‘([0-9]+)’)

,regexp_extract(‘F050004.OF’,‘([0-9]+)’)

,regexp_replace(‘华夏纯债债券型证券投资基金A类’, ‘(AB)$|(A$)|(B$)|(C$)|(A类)$|(B类)$|(C类)$|(Y类)$|(A/B类)$|(A/E类)$|(A/C类)$|(A/B)$|(E类)$|(A级)$|(A1)$|(I类)$’, ‘’)

,regexp_replace(‘华夏纯债债券型证券投资基金I类’, ‘(AB)$|(A$)|(B$)|(C$)|(A类)$|(B类)$|(C类)$|(Y类)$|(A/B类)$|(A/E类)$|(A/C类)$|(A/B)$|(E类)$|(A级)$|(A1)$|(I类)$’, ‘’)

,regexp_replace(‘华夏纯债债券型证券投资基金AB类’, ‘(AB)’, ‘’)

,regexp_extract(split(‘客户姓名李明(客户号139009)为【20230924 14:09:03】策略商城产品购买断点客户，客户点击【产品名称:黄金眼(商品编号:19)】签约按钮但未成功下单（签约），请尽快跟进服务！’,‘商品编号:’)[1],‘([0-9]+)’,1);

select ‘尊敬的客户，1月新年有礼的活动已经开始报名啦，报名即可领银豆，任意福利达标可领更多奖励，具体活动详情您可登录APP查看，快捷报名入口：{{https://vip.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}}。投资有风险，入市需谨慎。’ as asd

,regexp_extract(‘尊敬的客户，1月新年有礼的活动已经开始报名啦，报名即可领银豆，任意福利达标可领更多奖励，具体活动详情您可登录APP查看，快捷报名入口：{{https://vip.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}},{{https://vip.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}}。投资有风险，入市需谨慎。’,‘(https)(.?)(}})‘,0)
,replace(regexp_extract(‘尊敬的客户，1月新年有礼的活动已经开始报名啦，报名即可领银豆，任意福利达标可领更多奖励，具体活动详情您可登录APP查看，快捷报名入口：{{https://vip.com:8088/cms-h5/index.html?works_link_id=AErjx3MVyB}}’,’(https)(.?)(}})’,0),‘}’,‘’);