hive 模糊匹配 join之locate

locate基本用法

locate(string substr, string str[, int pos])
hive> select locate('a','abcd'), locate('b', 'abcd'), locate('f', 'abcd'),locate('20', '222002')

结果: 1 2 0 3

join模糊匹配

hive> select * from a left join b on 1=1 where locate(a.col,b.col)>0
hive> select * from a   right join b on 1=1 where locate(a.col,b.col)>0
hive> select * from a full join b  where locate(a.col,b.col)>0

select 
a.*,b.*
from
(SELECT * from ods_wxapp_sqoop.user_base where to_date(createdate)='2020-05-18') as a 
join
(SELECT * from ods_wxapp_sqoop.province ) as b
on 1=1 
WHERE locate(a.province,b.province_name)>0

将locate()直接写在where条件里，也可以使用row_number()来搭配使用

select col
from(
    select 
        if(locate(a.col, b.col)>0, b.col, a.col) as col, 
        row_number() over(partition by a.col order by locate(a.col, b.col) desc) as rn
    from a 
    left join b on 1=1
) as a
where rn=1