使一排最接近另一排的时间

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

对于您发布的当前问题(简化的问题(，我做了以下内容：

Cross加入了表，以每个目标时间和每个现有的数据时间戳在每个目标时间内有所不同。然后应用DENSE_RANK函数，该功能将为每个目标时间提供排名，然后仅选择这些记录，而这些记录的最小差异为毫秒。

您可以在此处找到工作解决方案。

 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

Answer 2

如果要在每个日历中的第一个记录，则可以使用row_number()：

select tdt.*
from (select tdt.*,
             row_number() over (partition by format(DataTimeStamp, 'yyyy-MM-dd HH:mm')
                                order by DataTimeStamp asc
                               ) as seqnum
      from TempDataTable tdt
     ) tdt
where seqnum = 1;

Answer 3

如果我正在阅读您的问题，即使在前一分钟，您也需要最接近记录。如果是这样，您可以查询。我以多个步骤进行了操作，因此您可以轻松遵循(我希望(

我做了什么：

• 找到最近的一分钟，如果秒> = 30圆形到第二分钟，请保持实际的分钟
• 计算秒的差异并找到其ABS值
• 获取最接近时间点的值

查询

SELECT tempd.TargetTime, tdfinal.DataName, tdfinal.DataValue, tdfinal.DataTimeStamp
FROM @TempTargetTable as tempd
LEFT OUTER JOIN
   (SELECT tdseconds.*, ROW_NUMBER() OVER(PARTITION BY closestMinute ORDER BY secondDiff) AS r
    FROM (SELECT td.*, ABS(DATEDIFF(SECOND, DataTimeStamp, closestMinute)) AS secondDiff
          FROM (SELECT DataName,DataValue,DataTimeStamp,
                  CONVERT(DATETIME,CONVERT(DATE, datatimestamp, 121)) + 
                  CONVERT (DATETIME,TIMEFROMPARTS(DATEPART(HOUR, datatimestamp), 
                             CASE WHEN DATEPART(SECOND, DataTimeStamp) >= 30 
                                    THEN DATEPART(MINUTE, DATATimeStamp) + 1 
                                    ELSE DATEPART(MINUTE, DATATimeStamp) END, 0,0,0), 121) AS closestMinute
                FROM @TempDataTable ) AS td
          ) AS tdseconds
   ) AS tdfinal
ON tdfinal.closestMinute = tempd.TargetTime
WHERE tdfinal.r = 1

结果

TargetTime               DataName  DataValue  DataTimeStamp
2017-01-01 00:00:00.000  Sensor    0.00       2017-01-01 00:00:00.000
2017-01-01 00:01:00.000  Sensor    1.00       2017-01-01 00:01:03.000
2017-01-01 00:02:00.000  Sensor    2.00       2017-01-01 00:02:01.000
2017-01-01 00:03:00.000  Sensor    3.00       2017-01-01 00:02:56.000
2017-01-01 00:04:00.000  Sensor    4.00       2017-01-01 00:04:01.000
2017-01-01 00:05:00.000  Sensor    5.00       2017-01-01 00:05:00.000
2017-01-01 00:06:00.000  Sensor    6.00       2017-01-01 00:06:10.000
2017-01-01 00:07:00.000  Sensor    7.00       2017-01-01 00:06:57.000
2017-01-01 00:08:00.000  Sensor    8.00       2017-01-01 00:08:01.000
2017-01-01 00:09:00.000  Sensor    9.00       2017-01-01 00:09:03.000