hive面试题

用SQL判断字段串中连续出现两个以上的数字，并返回对应的数字
with a as (
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[0-9]+’,‘,’),‘,’)) col
) select * from a where LENGTH(col)>1
结果为sdfsd
sdf
取11位手机号码
select regexp_extract(‘MA;IIP=58.211.125.238;IPORT=39236;LIP=172.18.249.61;MAC=NA;IMEI=NA;RMPN=13861311293;UMPN=NA@DSPT-XYD-UA=www.tzt.cn Android Mobile Brow;HN=EMall-Wrs249-61’,‘(\d{11})’,0) as tel_number;

1）国内电话号码已1开头，且第二位在3-9之间，所以有^1[3-9]
2）除上述两位数字外，其余还有9位，所以有\d{9}，且以加上结尾符号$
3）可以使用或条件进行更加严格的手机号匹配
select regexp_extract(lower(tel),‘(^1[3-9]\d{9}$)’,0) as tel_number;
select ‘selfstock.tzt.stock.15180445203.-1’
,regexp_extract(‘selfstock.tzt.stock.15180445203.-1’,‘(\d{11})’,0) as tel_num
,regexp_extract(‘selfstock.tzt.stock.15180445203.-1’,‘(1[0-9]{10})’,0)

员工表中入职时间入职日期（hire_date）包含有多个形式的数据，如2021/12/13 18：00：00、2021-12-13 18：00：00、20211213 18：00：00，请将数据统一成一个格式，（如20211213180000）
select REGEXP_REPLACE(‘2021/12/13 18：00：00’, ‘[^0-9]’, ‘’)
结果为20211213180000

提取opstaion的手机号码信息（符合11位长度的数字），若不符合手机号规则，则置空值。
（E@183.135.107.156@15867854587@07396666555，E@223.104.161.221@13884412998，E@39.188.105.52@13906842974@0201111555，3@54E1ADE44071@125116208030@6666666）
Select cust_no,col1 from (
select cust_no,col1 from table lateral view explode(split(opstation,‘@’)) ad as col1
) where length(col1) = 11 and length(regexp_replace(col1,‘[a-z]+’,‘,’)) = 11；

提取数字
with a as (select ‘158元2分’ as je)
select regexp_extract(je,‘([0-9]+)’,1),split(regexp_replace(je,‘([^0-9]+)’,’ ‘),’ ')[1],je from a;

用SQL判断字段串中连续出现两个以上的数字，并返回对应的数字
with a as (
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[0-9]+’,‘,’),‘,’)) col
) select * from a where LENGTH(col)>1
结果为sdfsd
sdf
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[0-9]+’,‘,’),‘,’))
union all
select explode(split(regexp_replace(‘sdfsd1232sdf324’,‘[a-z]+’,‘,’),‘,’))

select regexp_extract(‘honeymoon’, ‘hon(.*?)(moon)’,2);

select explode(array(‘A’,‘B’,‘C’))
select explode(map(‘a’,1,‘b’,2))

select posexplode(array(‘A’,‘B’,‘C’));

with t2 as ( select ‘1<’ as client_rights,‘a’ as cust_num
union all
select ‘CWe:’ as client_rights,‘a’ as cust_num
)
select
collect_set(t2.client_rights),
– concat_ws(‘’, collect_set(t2.client_rights),
instr(concat_ws(‘’, collect_set(t2.client_rights)), ‘<’),
if(instr(concat_ws(‘’, collect_set(t2.client_rights)), ‘<’) > 0,1,0)
from t2
group by cust_num

取id后面的数字
with a as (select ‘http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F’ as info )
select info,regexp_extract(info,‘id=([0-9]+)’,1) from a

info _c1
http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F 14634061

取字母或者数字
select regexp_extract('MA;IIP=175.153.162.124;IPORT=26377;LIP=192.168.0.7;MAC=FED7A908ECBD;IMEI=NA;RMPN=18699171002;UMPN=18699171002;ICCID=NA;OSV=android10;IMSI=NA@XYD-VER=6.01.030;JYSERVER:172.18.1.133
',‘MAC=([A-Za-z0-9]+)’,1)
,regexp_extract(‘http://127.0.0.1:61062/redmall/html/empd_prodtBuy.html?id=14634061&TZTREQUESTCRC=ACAE284CE7FAD3F42050027FA1CC395F’,‘id=([0-9]+)’,1) ;
– FED7A908ECBD 14634061