SQL:使用等号和最近号连接(类似于Pandas的合并) [英] SQL: join with an equal key and nearest key (similar to Pandas's merge as of)

问题描述

例如，我有2张这样的桌子

For example, I have 2 tables like this

对于表1中的每一行，我想使用

For every row in table 1, I want to get the row with

• 相同的customer id和

最近的date(在我的情况下为table2.date < table1.date)

nearest date (in my case, table2.date < table1.date)

结果应该像这样

如何在SQL中执行此操作?我尝试搜索，但是没有找到很多相关的信息.以及如果我想将条件更改为<=，>=或>怎么办?

How can I do this in SQL? I tried to search, but didn't find many things related. and what if I want to change the condition to <=, >= or >?

谢谢！

注意:

• 请使用标准SQL，因为我没有使用PostgreSQL
• 如果我可以使用Python，我可能会简单地使用for循环.

推荐答案

我同意Gordon的观点-横向连接是最好的解决方案.

I agree with Gordon - lateral join is best solution.

如果不支持横向连接，则基于窗口函数的解决方案可能会派上用场.

If lateral join is not supported, window function-based solution may come in handy.

with table1 (order_id, customer_id, date) as (
  select 1, 1, date '2019-10-10' union
  select 2, 1, date '2019-10-11' union
  select 3, 2, date '2019-10-11' union
  select 4, 2, date '2019-10-12' union
  select 5, 3, date '2019-10-12'
), table2 (order_id, customer_id, date) as (
  select  8, 1, date '2019-10-08' union
  select  9, 1, date '2019-10-09' union
  select 10, 1, date '2019-10-10' union
  select 11, 2, date '2019-10-10' union
  select 11, 2, date '2019-10-10' union
  select 11, 2, date '2019-10-10'
), all_rows as (
  select t1.*, t2.*, row_number() over (partition by t1.order_id order by t2.date desc) rn
  from table1 t1
  left join table2 t2 on t1.customer_id = t2.customer_id and t2.date < t1.date
)
select * from all_rows where rn = 1

这篇关于SQL:使用等号和最近号连接(类似于Pandas的合并)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！