Блог

Я пытаюсь агрегировать данные за последние 24 часа с интервалом в 5 минут. Я написал следующий запрос:

 select  
    DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', t.datetime) / 5 * 5, '2000') 
as datetimevalue,    
    cast(sum(t.value) as decimal(10,2)) as value 
from 
    dbo.data t  
where 
    t.datetime <= '2020-09-12 19:23:00.000' 
    and t.datetime > DATEADD(day,-1,'2020-09-12 19:23:00.000')    
group by
    DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', t.datetime) / 5 * 5, '2000')   
order by 
    datetimevalue asc
  

Результат возвращается следующим образом:

 datetimevalue               value
------------------------------------
2020-09-12 18:45:00.000,    54227.16
2020-09-12 18:50:00.000,    53681.54
2020-09-12 18:55:00.000,    49379.01
2020-09-12 19:00:00.000,    50751.53
2020-09-12 19:05:00.000,    55033.14
2020-09-12 19:10:00.000,    55858.37
2020-09-12 19:15:00.000,    54236.57
2020-09-12 19:20:00.000,    26731.36
  

Мне нужна агрегация последних 5 минут, начиная с текущей временной метки вместо указанной выше, которая агрегирует последние 3 минуты, а затем через 5 минут после этого.

Например

 datetimevalue               value
--------------------------------------
2020-09-12 19:03:00.000,    22444.47
2020-09-12 19:08:00.000,    45674.46
2020-09-12 19:13:00.000,    35737.23
2020-09-12 19:18:00.000,  34675.34
  

Любая помощь приветствуется.

Вам нужно вычислить смещение к временному окну агрегации (5 минут), вычесть его при вычислении значений и снова добавить к конечным временам:

 DECLARE @dt DATETIME = GETDATE()
DECLARE @offset INT = (DATEDIFF(SECOND, CAST(CAST(@dt AS DATE) AS DATETIME), @dt) % (60 * 5)) / 60 * 60
-- In case you want an offset in seconds
--DECLARE @diff INT = DATEDIFF(SECOND, CAST(CAST(@dt AS DATE) AS DATETIME), @dt) % (60 * 5)


SELECT DATEADD(SECOND, @offset, datetimevalue) AS datetimevalue, value
FROM (
    SELECT
        DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', DATEADD(SECOND, -@offset, t.DATETIME)) / 5 * 5, '2000') AS datetimevalue
       ,cast(sum(t.value) AS DECIMAL(10, 2)) AS value
    FROM dbo.data t
    WHERE t.DATETIME <= @dt
    -- in case you want only for last 24 hours
    --  AND t.DATETIME > DATEADD(DAY, - 1, @dt)
    -- in case if you want to include offset in last timestamp
        AND t.DATETIME > DATEADD(SECOND, -@offset, DATEADD(DAY, - 1, @dt))
    GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', DATEADD(SECOND, -@offset, t.DATETIME)) / 5 * 5, '2000')
    ) T
ORDER BY datetimevalue DESC