按最近时间合并两个表的SQL

Question

我有一个全球定位系统的位置和时间信息(时间，拉特，长)。我有一个表事件，它保存特定类型事件(时间)的时间。

我需要sql (最好是mysql)来创建一个新的表：events_with_gps，对于表事件中的每个记录，注册表gps寄存器的位置(lat，long)，该寄存器在事件发生时更接近。

示例：

gps:
11:00:00, 10, 10
11:05:00, 14, 14
11:10:00, 18, 18
11:15:00, 20, 20
11:20:00, 22, 22

events:
11:06:02
11:14:09

events_with_gps:
11:06:02, 14, 14
11:14:09, 20, 20

另外，是否可以将events_with_gps中的位置设置为上一次和下一次全球定位系统寄存器之间的平均位置？

events_with_gps_avg:
11:06:02, 16, 16
11:14:09, 19, 19

Answer 1

在最近时间执行连接的一种方法是首先使用CROSS JOIN来计算gps表和event表中的时间列之间的所有可能的差异。

以下查询：

SELECT e.`time` AS etime, g.`time` AS gtime, g.lat, g.`long`, 
       ABS(TIMEDIFF(e.`time`, g.`time`)) AS diff
FROM events AS e
CROSS JOIN gps AS g
ORDER BY e.`time`, diff

产生以下输出：

etime       gtime       lat long    diff
------------------------------------------
11:06:02    11:05:00    14  14      102
11:06:02    11:10:00    18  18      358
11:06:02    11:00:00    10  10      602
11:06:02    11:15:00    20  20      858
11:06:02    11:20:00    22  22      1358
11:14:09    11:15:00    20  20      51
11:14:09    11:10:00    18  18      409
11:14:09    11:20:00    22  22      551
11:14:09    11:05:00    14  14      909
11:14:09    11:00:00    10  10      1409

如果您使用GROUP BY etime并使用GROUP_CONCAT，您可以从上面的结果集中选择具有最小diff值的记录。您可以将JOIN这些记录返回到gps以获取要插入到events_with_gps中的记录

SELECT t.`time`, g.lat, g.`long`
FROM (
SELECT e.`time`, 
       SUBSTRING_INDEX(
          GROUP_CONCAT(g.`time` 
                       ORDER BY ABS(TIMEDIFF(e.`time`, g.`time`))), ',', 1) AS matchedTime
FROM events AS e
CROSS JOIN gps AS g
GROUP BY e.`time` ) t
INNER JOIN gps AS g ON CAST(t.matchedTime AS TIME) = g.`time` 

输出：

 time       lat long
-----------------------
'11:06:02'  14  14
'11:14:09'  20  20

Demo here

要获得第二个结果集，可以使用以下查询获取每个events记录的前一个和下一个events记录：

SELECT e.`time` as etime, 
       (SELECT `time`
        FROM gps AS g
        WHERE g.`time` <= e.`time`
        ORDER BY g.`time` DESC 
        LIMIT 1) AS prevTime,
       (SELECT `time`
        FROM gps AS g
        WHERE g.`time` > e.`time`
        ORDER BY g.`time`  
        LIMIT 1) AS nextTime        
FROM events AS e

输出：

etime     prevTime  nextTime
------------------------------
11:06:02  11:05:00  11:10:00
11:14:09  11:10:00  11:15:00

现在，您可以将上面的表用作派生表，以便连接到gps并获取“以前”和“下一步”lat和long值：

SELECT t.etime, (g1.lat + g2.lat) /2 , (g1.`long` + g2.`long`) / 2
FROM (
SELECT e.`time` as etime, 
       (SELECT `time`
        FROM gps AS g
        WHERE g.`time` <= e.`time`
        ORDER BY g.`time` DESC 
        LIMIT 1) AS prevTime,
       (SELECT `time`
        FROM gps AS g
        WHERE g.`time` > e.`time`
        ORDER BY g.`time`  
        LIMIT 1) AS nextTime        
FROM events AS e ) t
INNER JOIN gps AS g1 ON t.prevTime = g1.time
INNER JOIN gps AS g2 ON t.nextTime = g2.time

输出：

 time       lat long
-----------------------
'11:06:02'  16  16
'11:14:09'  19  19

Demo here