sql - 获取最接近另一个表中每一行时间的行

Question

问题:

您好，我有一张表，其中包含来自一堆传感器的历史数据，我正在尝试为每条历史数据记录获取最接近所需时间的行。例如，我想获得最接近每分钟的记录。

我已将问题简化为以下内容，如果我能解决，我可以用它来告知我的一般解决方案:

取两个表如下:

CREATE TABLE [TempDataTable](
[DataIndex] [int] IDENTITY(0,2) NOT NULL,
[DataName] [varchar](40) NOT NULL,
[DataValue] [decimal](10,2) NOT NULL,
[DataTimeStamp] [datetime2](7)
)

CREATE TABLE [TempTargetTable](
[TargetIndex] [int] IDENTITY(1,2) NOT NULL,
[TargetTime] [datetime2](7)
)

对于 TempTargetTable 中的每一行，获取 TempDataTable 中 TempDataTable.DataTimeStamp 最接近 TempTargetTable.TargetTime 的行>

如果我能做到这一点，我相信我能解决剩下的问题，但我不知道如何让这第一步发挥作用。为了便于测试您的代码，我可以提供以下内容，用一些测试数据填充两个表:

有用的测试数据:

INSERT INTO [TempDataTable]
    ([DataName],
    [DataValue],
    [DataTimeStamp])
VALUES
    ('Sensor',0,    '2017-01-01 00:00:00'),
    ('Sensor',0.5,  '2017-01-01 00:00:17'),
    ('Sensor',1,    '2017-01-01 00:01:03'),
    ('Sensor',1.5,  '2017-01-01 00:01:30'),
    ('Sensor',1.5,  '2017-01-01 00:01:38'),
    ('Sensor',2,    '2017-01-01 00:02:01'),
    ('Sensor',2.5,  '2017-01-01 00:02:15'),
    ('Sensor',3,    '2017-01-01 00:02:56'),
    ('Sensor',3.5,  '2017-01-01 00:03:27'),
    ('Sensor',4,    '2017-01-01 00:04:01'),
    ('Sensor',5,    '2017-01-01 00:05:00'),
    ('Sensor',5.5,  '2017-01-01 00:05:15'),
    ('Sensor',5.5,  '2017-01-01 00:05:46'),
    ('Sensor',6,    '2017-01-01 00:06:10'),
    ('Sensor',7,    '2017-01-01 00:06:57'),
    ('Sensor',7.5,  '2017-01-01 00:07:13'),
    ('Sensor',8,    '2017-01-01 00:08:01'),
    ('Sensor',9,    '2017-01-01 00:09:03')

INSERT INTO [TempTargetTable]
    ([TargetTime])
VALUES
    ('2017-01-01 00:00:00'),
    ('2017-01-01 00:01:00'),
    ('2017-01-01 00:02:00'),
    ('2017-01-01 00:03:00'),
    ('2017-01-01 00:04:00'),
    ('2017-01-01 00:05:00'),
    ('2017-01-01 00:06:00'),
    ('2017-01-01 00:07:00'),
    ('2017-01-01 00:08:00'),
    ('2017-01-01 00:09:00')

Answer 1

对于您发布的当前问题(简化的问题)，我执行了以下操作:

交叉连接表以对每个目标时间和每个现有数据时间戳进行区分。然后应用 DENSE_RANK 函数，该函数将为每个 TargetTime 提供排名，然后仅选择那些以毫秒为单位的差异最小的记录。

您可以找到可行的解决方案 here .

 select TargetIndex, TargetTime, DataIndex, DataName, DataValue, DataTimeStamp
 from
  (
   select t.*, DENSE_RANK() OVER(PARTITION BY t.targetindex ORDER BY t.diff) as Rank
   from 
    (
      select tg.targetindex, tg.targettime, t.dataindex, t.dataname, t.datavalue, t.datatimestamp, abs(datediff(ms, tg.TargetTime, t.DataTimeStamp)) diff
      from TempDataTable t cross join TempTargetTable tg
    ) t
 ) f 
 where Rank = 1