【Hive】join中的模糊匹配（locate）

HIVE不支持非等值连接，但可以通过locate()函数进行功能转换。


locate(string substr, string str[, int pos])
查找字符串str中的pos位置后字符串substr第一次出现的位置，若为找到，则返回0。

hive> select locate('a','abcd'), locate('b', 'abcd'), locate('f', 'abcd')
结果:  1  2  0

join模糊匹配
left join , right join , full 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

除了将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