根据彼此间隔5分钟的时间戳联接两个表 [英] Join two tables based on timestamps with a 5 minute interval from each other

问题描述

我想基于时间戳联接两个表，问题是我注意到两个表都没有完全相同的时间戳，所以我想使用最近的时间戳(间隔5分钟)联接它们.

I want to join two tables based on timestamp, the problem is that i noticed both tables didn't had the exact same timestamp so i want to join them using the nearest timestamp using a 5 minute interval.

 Freezer |       Timestamp      | Temperature_1
     1      2018-04-25 09:45:00       10  
     1      2018-04-25 09:50:00       11   
     1      2018-04-25 09:55:00       11


Freezer |       Timestamp      | Temperature_2
     1      2018-04-25 09:46:00       15  
     1      2018-04-25 09:52:00       13   
     1      2018-04-25 09:59:00       12   

我想要的结果是:

Freezer |       Timestamp      | Temperature_1 | Temperature_2
     1      2018-04-25 09:45:00       10             15
     1      2018-04-25 09:50:00       11             13
     1      2018-04-25 09:55:00       11             12

我正在处理的当前查询是:

The current query that i'm working on is:

SELECT A.Freezer, A.Timestamp, Temperature_1,Temperature_2 From TABLE_A as A
RIGHT JOIN TABLE_B as B
ON A.FREEZER = B.FREEZER
WHERE A.Timestamp = B.Timestamp (this of course doesn't work because timestamps aren't the same)

推荐答案

这是您想要的吗?

SELECT . . .
From TABLE_A A JOIN
     TABLE_B B
     ON A.FREEZER = B.FREEZER AND
        A.Timestamp >= DATEADD(minute, -5, B.TimeStamp) AND
        A.Timestamp <= DATEADD(minute, 5, B.TimeStamp) ;

我认为没有理由使用外部JOIN.

I see no reason for an outer JOIN.

如果要查找最近的时间戳，请使用apply:

If you want to find the nearest timestamp, then use apply:

select . . .
from table_a a cross apply
     (select top (1) b.*
      from table_b b
      where a.freezer = b.freezer 
      order by abs(datediff(second, a.timestamp, b.timestamp))
     ) b;

您可以在子查询中添加where子句，以将匹配范围限制为五分钟.

You can add a where clause to the subquery to limit the range of matches to five minutes.

这篇关于根据彼此间隔5分钟的时间戳联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！