I want to fetch some data (energy consumption) from an SQL Server table by creating a window of 5 mins and finding its aggregate.

The SQL Table stores some telemetry data captured every minute. Now what I have to do is, calculate the difference of values of (T 5 mins) and T, for all the windows.

I tried to use the aggregate window but was not able to get the intended results.

Are there any T-SQL functionalities that I can utilize to get such output without running a loop (Want to avoid this as it would be a non-optimal solution)?

An example of my scenario is mentioned below.

Needs to be converted to (Timestamps have been converted for simplicity and brackets are provided for explanation of calculation)

CodePudding user response：

Although it's fully inline with your example, it will work only if you have every minute without seconds and so on and so forth.

take a look, please

create table #t
(
     ts datetime2
    ,val int
)

insert into #t
values
 ('2021-11-09T01:01:00.000Z', 100)
,('2021-11-09T01:02:00.000Z', 102)
,('2021-11-09T01:03:00.000Z', 103)
,('2021-11-09T01:04:00.000Z', 105)
,('2021-11-09T01:05:00.000Z', 107)
,('2021-11-09T01:06:00.000Z', 108)
,('2021-11-09T01:07:00.000Z', 120)
,('2021-11-09T01:08:00.000Z', 123)
,('2021-11-09T01:09:00.000Z', 128)
,('2021-11-09T01:10:00.000Z', 135)

select  format(ts, 'hh:mm:ss')   ' - '   format(next_ts, 'hh:mm:ss')
        ,cast(next_val - val as varchar(10))   ' ('   cast(next_val as varchar(10))   ' - '   cast(val as varchar(10))   ')'
from
    (
        select   ts
                ,val
                ,lead(ts) over(order by ts) as next_ts
                ,lead(val) over(order by ts) as next_val
        from
            (
                select *, (row_number() over(order by ts) - 1) % 4 as row_id
                from #t
            ) SQ
        where row_id = 0
    ) SQ
where next_ts is not null

CodePudding user response：

To aggregate them in windows of 5 minutes?
Then this may be as simple as also grouping by the minutes divided by 12.

SELECT 
 CONCAT(CONVERT(VARCHAR, MIN(t.[Timestamp]), 108), ' - ', CONVERT(VARCHAR, MAX(t.[Timestamp]), 108)) AS Agg_Timestamp,
 CONCAT(MAX(t.Energy_Value)-MIN(t.Energy_Value), ' (', MAX(t.Energy_Value), '-', MIN(t.Energy_Value), ')' ) AS Agg_Energy_Value
FROM yourtable t
GROUP BY CAST(t.[Timestamp] AS DATE),
  DATEPART(hour, t.[Timestamp]),
  ROUND((DATEPART(minute, t.[Timestamp])/12.0), 0)

Result:

Agg_Timestamp       | Agg_Energy_Value
:------------------ | :---------------
01:01:00 - 01:05:00 | 7 (107-100)     
01:06:00 - 01:10:00 | 27 (135-108)