根据日期列返回列的值，该列的值最接近xx:00和xx:30

Question

我试图以30分钟的间隔返回数据，但是如果在30分钟没有数据，我想取最接近30分钟或0分钟的值，公差为-5 +5，如果仍然没有这个范围为+5-5( 55-05或25-35 )的数据，则不会返回值。目前，我有一个sql语句，它可以每隔30分钟返回55-05和25-35之间的平均值，但我只想要一个与现在的30分钟最接近的值，如果55-05和25-35不能是done.The sql语句，我就可以使用55-00或25-30。

SELECT  [target],AVG([Temperature]) as Temperature,AVG([Humidity]) As Humidity,DATEADD(minute,(datediff(minute,0,[Date Time])/30)*30,0) as [Date Time],COUNT (*)                                    
FROM mytable                                    
WHERE DATEPART(minute, [Date Time]) IN (0,1,2,3,4,5,25,26,27,28,29,30,31,32,33,34,35,55,56,57,58,59)                                    
AND (CAST([Date Time] AS DATE)= '2020-09-17')                                   
AND ([target] = '1')                                    
                                    
GROUP BY [target],DATEADD(minute,(datediff(minute,0,[Date Time])/30)*30,0) ORDER BY [Date Time] ASC
output is
Target  Temperature Humidity    Date Time              (No column name)
1       21.100000   76.900000   2020-09-17 00:00:00.000      2
1       21.200000   77.300000   2020-09-17 01:00:00.000      1
1       21.133333   77.333333   2020-09-17 01:30:00.000      3
1       21.100000   77.400000   2020-09-17 02:00:00.000      1
1       21.100000   77.550000   2020-09-17 02:30:00.000      2
1       21.120000   77.640000   2020-09-17 03:00:00.000      5                      

预期输出应大致相同，没有列名为1，因为只应返回一个值，只有温湿度的变化将不是时间25-35和55-05的平均范围，所以所采集的温湿度数据应该是最接近30分钟和0分钟的数据。

Answer 1

您可以使用ROW_NUMBER()。但是，关键的想法是将时间值舍入到适当的“时间组”。然后，您可以使用时间组时间和观察到的日期时间之间的差异来排序行：

SELECT  t.*                                
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY t.target, v.timegrp
                                ORDER BY ABS(DATEDIFF(SECOND, t.[Date Time], v.timegrp))
                               ) as seqnum
      FROM mytable t CROSS APPLY
           (VALUES (DATEADD(MINUTE,
                            (DATEDIFF(MINUTE, 0, DATEADD(MINUTE, 5, t.[Date Time])) / 30) * 30,
                            0)
                   )
           ) v(timegrp)
      WHERE CAST([Date Time] AS DATE) = '2020-09-17' AND                        
            [target] = '1' AND
            v.timegrp > DATEADD(MINUTE, -5, t.[Date Time]) AND
            v.timegrp < DATEADD(MINUTE, 5, t.[Date Time])
     ) t                                  
WHERE seqnum = 1;

注意:这个逻辑不适用于午夜，因为时间间隔跨越多天。要处理这个问题，可以将筛选移到外部查询。我没有这样做，因为您的查询没有考虑到这一点。