Robert Robert - 6 months ago 13
SQL Question

SQL statement that calculates per-interval growth

In our database we do have a table that keeps track of the power consumption of a device. The rate at which new values get inserted is not fixed, they only get written when there really is a change, so the temporal distance between the values is varying and may reach from 1 second to several minutes. The entries consist of a timestamp and a value. The value always increases with every new row as it counts the kWh.

What I want to achieve is the following: I want to specify a start and an end datetime, let's say a month. I also want to specify an interval like 15 minutes, 1 hour, 1 day or similar. The outcome I need to get is in the form of [Beginning of interval as datetime], [power consumption in that interval], e.g. like this (where interval would be set to 1 hour):

2015-01.01 08:00:00 - 65
2015-01.01 09:00:00 - 43
2015-01.01 10:00:00 - 56


This is what the table looks like:

TimeStamp Value
-------------------------
2015-01-08 08:29:47, 5246
2015-01-08 08:36:15, 5247
2015-01-08 08:37:10, 5248
2015-01-08 08:38:01, 5249
2015-01-08 08:38:38, 5250
2015-01-08 08:38:51, 5251
2015-01-08 08:39:33, 5252
2015-01-08 08:40:20, 5253
2015-01-08 08:41:10, 5254
2015-01-09 08:56:25, 5255
2015-01-09 08:56:43, 5256
2015-01-09 08:57:31, 5257
2015-01-09 08:57:36, 5258
2015-01-09 08:58:02, 5259
2015-01-09 08:58:57, 5260
2015-01-09 08:59:27, 5261
2015-01-09 09:00:06, 5262
2015-01-09 09:00:59, 5263
2015-01-09 09:01:54, 5265
2015-01-09 09:02:44, 5266
2015-01-09 09:03:39, 5267
2015-01-09 09:04:22, 5268
2015-01-09 09:05:11, 5269
2015-01-09 09:06:08, 5270


I have the feeling that I would have to combine the
SUM()
function with a
GROUP BY
, but I have no clue how to do that, because as far as I can see I would also have to consider only the growth per interval and not the sum of the absolute values within that interval. It would be great if someone could bring me onto the right track.

flo flo
Answer

Your sample data does not match the result intervals, so you may miss increases within an interval at the end or the beginning. Therefore, I assumed a linear increase between sample data rows and matched them to the result interval.

declare @start datetime2 = '2015-01-09 09:00:00'
declare @end datetime2 = '2015-01-09 09:30:00'
declare @intervalMinutes int = 5

;with intervals as (
      select @start iStart, dateadd(minute, @intervalMinutes, @start) iEnd

      union all

      select iEnd, dateadd(minute, @intervalMinutes, iEnd) from intervals
      where iEnd < @end

), increases as (
        select 
             T.Timestamp sStart, 
             lead(T.Timestamp, 1, null ) over (order by T.Timestamp) sEnd, -- the start of the next period if there is one, null else
             lead(T.value, 1, null ) over (order by T.Timestamp) - T.value increase  -- the increase within this period
        from @T T
), rates as (
        select 
           sStart rStart, 
           sEnd rEnd, 
           (cast(increase as float))/datediff(second, sStart, sEnd) rate -- increase/second
        from increases where increase is not null
), samples as (
        select *, 
            case when iStart > rStart then iStart else rStart end sStart, -- debug
            case when rEnd>iEnd then iEnd else rEnd end sEnd,  -- debug                
            datediff(second, case when iStart > rStart then iStart else rStart end, case when rEnd>iEnd then iEnd else rEnd end)*rate x -- increase within the period within the interval
        from intervals i
        left join rates r on rStart between iStart and iEnd or rEnd between iStart and iEnd or iStart between rStart and rEnd -- overlaps
)
select iStart, iEnd, isnull(sum(x), 0) from samples 
group by iStart, iEnd

The CTEs:

  • intervals holds the intervales you want data for
  • increaese calculates the increases within the sample data periods
  • rates calculates the increase per second in the sample data periods
  • samples matches the result intervals to the sample intervals by respecting the overlaps between the bounds

Finally the select sums up the sample periods matched to a single interval.

NOTES:

  • For an interval amount > [your max recursion depth] you have to use another solution to crate the intervals CTE (see @GarethD solution)
  • Debug hint: By simply using select * from samples you can see the sample periods matched to your result intervals