SQL进阶技巧：Hive中Left Join基于or形式匹配连接的一种优雅实现方式？

会飞的一十六 2024-09-30

446

“ Hive中对于模糊匹配关联是不支持的，如or 连接，基于like的模糊匹配连接，对于此类问题往往需要找新的方案，对于or连接网上给出了解决方案如union的实现形式，本文借助于locate()+concat_ws()函数进行优雅的实现。”

—

场景描述

t表

id    dt
1    2022-06-03
2    2022-05-04
3    2022-04-01
4    2022-05-22

t1表：

id    dt1                                                                            dt2
1    2022-06-03                                                               2022-05-03
2    2022-05-25                                                               2022-05-04
3    2022-03-01                                                               2022-05-04

找出t表中时间字段dt在t1表中dt1,dt2任意出现的id，及时间，保留t表中数据，如果能够匹配到取匹配的时间，未匹配到置为NULL

—

数据准备

create table t as
select 1 as id,'2022-06-03' as dt
union all 
select 2 as id,'2022-05-04' as dt
union all 
select 3 as id,'2022-04-01' as dt
union all
select 4 as id,'2022-05-22' as dt
------------------
create table t1 as
select 1 as id,'2022-06-03' as dt1,'2022-05-03' as dt2
union all
select 2 as id,'2022-05-25' as dt, '2022-05-04' as dt2
union all
select 3 as id,'2022-03-01' as dt1, ' 2022-05-04' as dt2

—

问题分析

针对本问题如果在oracle或mysql中则比较好实现，具体如下：

select t.id,case when t1.id is not null then t.dt else null end as dt
from t
 join t1
on t.id = t1.id
and (t.dt=t1.dt1 or t.dt=t1.dt2)

但由于Hive低版本中不支持不等连接，使本问题增加了难度，传统的解法，采用union+去重的实现方式，具体SQL如下：

select id1 as id
   ,max(case when id2 is not null then dt else null end) as dt
from 
(select cast(t.id as string) as id1,t.dt as dt,cast(t1.id as string) as id2
from t
 left join t1
on t.id = t1.id
and t.dt=t1.dt1 
union 
select cast(t.id as string) as id1,t.dt as dt,cast(t1.id as string) as id2
from t
left join t1
on t.id = t1.id
and t.dt=t1.dt2 
) t
group by id1

结果如下：
OK
id      dt
1       2022-06-03
2       2022-05-04
3       NULL
4       NULL
Time taken: 3.343 seconds, Fetched: 4 row(s)

但是上述的方式显示显得很啰嗦，如果后面需要匹配的or比较多，比如有5个的时候，那么同样的逻辑就要union 5次代码看起来相当繁琐，且性能较低。

针对以上问题，本文采用一种优雅的实现方式：我们知道采用or连接的时候，无非就是t表中的字段在t1表中匹配到了就成功，对于这种需要匹配就成功的连接方式，我们自然想到hive中高效的实现方式locate()函数，对于该函数的理解，可以具体参考我如下文章：

SQL进阶技巧：如何从不固定位置提取字符串元素？【日志清洗】

由于t表中的某个字段需要在t1表中的多个字段中去匹配，为了满足locate()函数使用条件，我们利用concat_ws()函数进行列转行拼成一个串，然后让该字段在串中去匹配如果能匹配成功则表明该条数据可以匹配到，然后在where语句或case when中进行过滤。注意locate()这种模糊匹配也是不能放在on条件中的，这种本质上也是一种不等连接，只能放在where或case when中过滤，至于为什么要用locate()而不用like函数，原因是locate()的性能要优于like。因此我们具有如下实现方式：

首先用left join做等值连接，然后select语句通过case when去判断匹配，具体SQL如下：

select t.id
,case when locate(t.dt,concat_ws(',',t1.dt1,t1.dt2))>0 then t.dt else null end as dt
from t
left join t1
on t.id = t1.id

具体结果如下：

t.id    dt
2       2022-05-04
3       NULL
1       2022-06-03
4       NULL
Time taken: 2.275 seconds, Fetched: 4 row(s)

从执行性能上来看此种的实现方式也是要优于union的实现方式。

—

小结

本文介绍了一种在Hive中处理不支持模糊匹配关联问题的高效解决方案，利用locate()和concat_ws()函数实现or连接，避免了冗长的union操作，提升了性能。作者通过实际案例展示了如何使用这些函数进行时间字段的精确匹配或NULL填充。

往期精彩

SQL进阶技巧：如何将字符串数组清洗为简单map结构? | translate + regexp_replace方法

SQL进阶技巧：如何利用if语句简化where或join中的条件

SQL进阶技巧：如何查询最近一笔有效订单？ | 近距离有效匹配问题

SQL进阶技巧：每个HR负责的部门的人数之和 | 层次查询父子关系问题

SQL进阶技巧：如何对数据进行两两组合分析？| 广告策略投放转化问题

SQL进阶技巧：如何优雅实现根据组内排序规则生成新列问题？ | 分组TON问题

SQL进阶技巧：如何从不固定位置提取字符串元素？【日志清洗】

SQL进阶技巧：时点值状态统计如何分析？【某时点旅店客人在住房间数量统计】

SQL进阶技巧：如何利用Bitmap思想优化Array_contains()函数？

SQL进阶技巧：数据清洗如何分析商品入库采购成本数据缺失问题？

SQL进阶技巧：如何计算先进先出库龄问题？

SQL进阶技巧：如何计算重叠区间合并问题？

SQL进阶技巧：如何进行用户行为路径分析、构建桑基图？

文章转载自会飞的一十六，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL进阶技巧：Hive中Left Join基于or形式匹配连接的一种优雅实现方式？

评论