时间戳

首页 > 解决方案 > 如何从 2 个表中获取最近的 DateTime

sqlite - 如何从 2 个表中获取最近的 DateTime

问题描述


在 SQLite 中，我想构建一个查询来获取针对“tick”列表的“标记”条目的最近日期时间：

CREATE TABLE Tick    (
     id integer primary key, 
     dt varchar(20) 
    );

INSERT INTO Tick (id, dt) VALUES 
( 1, '2018-10-30 13:00:00'), 
( 2, '2018-10-30 14:00:00'), 
( 3, '2018-10-30 15:00:00'), 
( 4, '2018-10-30 16:00:00'), 
( 5, '2018-10-30 17:00:00'), 
( 6, '2018-10-30 18:00:00'), 
( 7, '2018-10-30 19:00:00'), 
( 8, '2018-10-31 05:00:00'), 
( 9, '2018-10-31 06:00:00'), 
(10, '2018-10-31 07:00:00');


CREATE TABLE Tag    (
     id integer primary key, 
     dt varchar(20) 
    );

INSERT INTO Tag (id, dt) VALUES 
(100, '2018-10-30 16:08:00'), 
(101, '2018-10-30 17:30:00'), 
(102, '2018-10-30 19:12:00'), 
(103, '2018-10-31 04:00:00'), 
(104, '2018-10-31 13:00:00');

以下查询为我提供了良好的匹配（基于差异），但我无法获得 Tick 列：

SELECT Tag.dt,
       (SELECT ABS(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as diff
        FROM Tick
        ORDER BY diff ASC
        LIMIT 1
       ) as diff from Tag


我尝试了以下操作，但我在 ORDER BY 中的 Tag.dt 上收到错误：

SELECT 
     Tag.id, Tag.dt,
     Tick.id, Tick.dt,
     abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as Diff FROM Tag   JOIN Tick   ON Tick.dt =    (SELECT Tick.dt
    FROM Tick 
    ORDER BY abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) ASC
    limit 1)


我想要的结果是这样的：

TagID,DateTimeTag        ,TickID,DateTimeTick
    100,2018-10-30 16:08:00,     4,2018-10-30 16:00:00
    101,2018-10-30 17:30:00,     6,2018-10-30 18:00:00
    102,2018-10-30 19:12:00,     7,2018-10-30 19:00:00
    103,2018-10-31 04:00:00,     8,2018-10-31 05:00:00
    104,2018-10-31 13:00:00,    10,2018-10-31 07:00:00

稍后编辑...
根据forpas的回答，我能够在不使用在 FME 中无法使用的 ROW_COUNTER() 关键字的情况下推导出一些东西。我还设置了最大增量时间差（10000 秒）来查找匹配项：

SELECT t.TagId, t.Tagdt, t.TickId, t.Tickdt, MIN(t.Diff)
FROM
(
SELECT 
     Tag.id as TagId, Tag.dt as Tagdt,
     Tick.id as TickId, Tick.dt as Tickdt,
     abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as Diff
FROM Tag, Tick
WHERE Diff < 10000
) AS t
GROUP BY t.TagId


再次感谢！

标签： sqlitedatetime

解决方案

使用ROW_NUMBER()窗口函数：

SELECT t.tagID, t.tagDT, t.tickID, t.tickDT
FROM (
  SELECT t.*, 
    ROW_NUMBER() OVER (PARTITION BY t.tagID, t.tagDT ORDER BY t.Diff) AS rn
  FROM (
    SELECT Tag.id tagID, Tag.dt tagDT, Tick.id tickID, Tick.dt tickDT,
      ABS(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as Diff 
    FROM Tag CROSS JOIN Tick 
  ) AS t                                        
) AS t  
WHERE t.rn = 1

请参阅演示。
结果：

| tagID | tagDT               | tickID | tickDT              |
| ----- | ------------------- | ------ | ------------------- |
| 100   | 2018-10-30 16:08:00 | 4      | 2018-10-30 16:00:00 |
| 101   | 2018-10-30 17:30:00 | 5      | 2018-10-30 17:00:00 |
| 102   | 2018-10-30 19:12:00 | 7      | 2018-10-30 19:00:00 |
| 103   | 2018-10-31 04:00:00 | 8      | 2018-10-31 05:00:00 |
| 104   | 2018-10-31 13:00:00 | 10     | 2018-10-31 07:00:00 |

推荐阅读