apache spark SQL:连接两个表以查找子集中的第一个匹配记录

Question

我有包含请求/响应消息的数据。我把它分成两个表。表 t1 包含所有请求，t2 包含所有响应。 帧表示帧号，时间表示时间戳。现在我需要将请求映射到相应的响应消息。

这是我目前的查询，它按预期工作。

SELECT t1.frame as frame1, min(t2.frame) as frame2, t1.key, 
(min(t2.time)-t1.time) as delta 
FROM t1 
JOIN t2 
ON t2.frame > t1.frame and 
t2.key LIKE CONCAT('%', t1.key, '%')
GROUP BY t1.frame, t1.time, t1.key 
ORDER BY t1.frame

我在这里做的是，对于来自t1(请求消息(的每条记录，找到t2中的第一条记录(响应消息(，该记录位于t1's记录之后(基于时间戳(，并且来自t1的键存在于t2中。然后获取这 2 条记录之间的时差。 现在它正在做它应该做的事情。但我的问题是，在某些情况下可能会缺少响应(这就是我想在测量响应时间时捕获的内容(。因此，可能会发生以下情况：

1) Request for ID1<---
2) <No Response for ID1>  
3) Request for ID1<---
4) Response for ID1-->

上述查询将报告#4作为#1和#3的响应，这是不正确的。 我希望对于请求#1，它返回null或一些字符串，然后我可以搜索并说出有多少请求未得到响应。 看起来我必须使用多个子查询。我正在使用Apache SparkSQL，但这应该是一个通用的SQL问题。 对此的任何帮助将不胜感激。谢谢。

编辑：示例数据和结果

t1:
frame, Time,  Key
1      2.3   0x01
4      3.5   0x02
13     4.6   0x03
22     6.2   0x03
t2:
frame, Time,  Key
3      2.5   0x01
8      3.8   0x02
25     6.3   0x03
Expected Result:
t1.frame, t2.frame,  delta, key
1         3          0.2    0x01
4         8          0.3    0x02
13        null       null   null
22        25         0.1    0x03

呵呵

Answer 1

在通用 SQL 中，这可以在 LEFT JOIN 的帮助下实现
和ANTI JOIN(NOT EXISTS运算符以及相关的子查询(，
但我不知道Spark SQL是否支持它们。

演示：SQLFiddle

---

SELECT t1.frame as frame1, min(t2.frame) as frame2, t1.key, 
(min(t2.time)-t1.time) as delta 
FROM t1 
LEFT JOIN t2 
ON t2.frame > t1.frame 
and t2.key LIKE CONCAT('%', t1.key, '%')
and NOT EXISTS ( 
SELECT 5 FROM t1 t11 
WHERE t11.key = t1.key
AND t11.time > t1.time
AND t11.time < t2.time
)
GROUP BY t1.frame, t1.time, t1.key 
ORDER BY t1.frame

---