SQL连接，其中第二个表中的值是第一个表的第一个较低值 [英] SQL join where value in second table is first lower value w.r.t the first table

问题描述

假设我有2个表，并且两个表都有一列，其中包含用于各种事件的timestamp.两个表中的时间戳值都不同，因为它们是针对不同事件的. 我想加入两个表，以便将table1中的每个记录与table2上的第一个较低的时间戳结合起来.

Let's say I have 2 tables and both of them have a column that contains timestamp for various events. The timestamp values in both the tables are different as they are for different events. I want to join the two tables such that every record in table1 is joined with first lower timestamp on table2.

For e.g. 
Table1       Table2
142.13       141.16 
157.34       145.45
168.45       155.85
170.23       166.76
             168.44

Joined Table should be:
142.13,141.16
157.34,155.85
168.45,166.76
170.23,168.44

我正在使用Apache Spark SQL.

I am using Apache Spark SQL.

我是SQL的菜鸟，这看起来不适合菜鸟:).谢谢.

I am a noob in SQL and this doesn't look like job for a noob :). Thanks.

推荐答案

Ditto显示了解决此问题的简单方法.如果Apache Spark确实对此基本查询有问题，请先加入(这可能会导致较大的中间结果)并进行聚合，然后:

Ditto has shown the straight-forward way to solve this. If Apache Spark really has problems with this very basic query, then join first (which can lead to a big intermediate result) and aggregate then:

select t1.v, max(t2.v)
from table1 t1
join table2 t2 on t2.v <= t1.v
group by t1.v
order by t1.v;

这篇关于SQL连接，其中第二个表中的值是第一个表的第一个较低值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！