SQL statement that calculates per-interval growth

Question

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.

Answer 1

I think the best way to deal with this is to first generate your intervals, and then left join your data, since this firstly makes the grouping much less complicated for variable intervals, and also means you still get results for intervals with no data. To do this you will need a numbers table, since many people don't have one below is a quick way of generating one on the fly:

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT *
FROM Numbers;

This simply generates a sequence from 1 to 10,000. For more reading on this see the following series:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

You can then define a start time, an interval and the number of records to show, and along With your numbers table you can generate your data:

DECLARE @Start DATETIME2 = '2015-01-09 08:00',
        @Interval INT = 60,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)

SELECT  TOP (@IntervalCount)
        Interval = DATEADD(MINUTE, (N - 1) * @Interval, @Start)
FROM    Numbers;

Finally you can LEFT JOIN this to your data to get the minimum and the maximum values for each interval

DECLARE @Start DATETIME2 = '2015-01-09 08:00',
        @Interval INT = 60,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
(   SELECT  TOP (@IntervalCount)
            IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
            IntervalEnd = DATEADD(MINUTE, N  * @Interval, @Start)
    FROM    Numbers AS n
)
SELECT  i.IntervalStart,
        MinVal = MIN(t.Value),
        MaxVal = MAX(t.Value),
        Difference = ISNULL(MAX(t.Value)  - MIN(t.Value), 0)
FROM    Intervals AS i
        LEFT JOIN T AS t
            ON t.timestamp >= i.IntervalStart
            AND t.timestamp < i.IntervalEnd
GROUP BY i.IntervalStart;

If your values can go up and down within the inverval, then you will need to use a ranking function to get the first and last record for each hour, rather than min and max:

DECLARE @Start DATETIME2 = '2015-01-09 08:00',
        @Interval INT = 60,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
(   SELECT  TOP (@IntervalCount)
            IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
            IntervalEnd = DATEADD(MINUTE, N  * @Interval, @Start)
    FROM    Numbers AS n
), RankedData AS
(   SELECT  i.IntervalStart,
            t.Value,
            t.timestamp,
            RowNum = ROW_NUMBER() OVER(PARTITION BY i.IntervalStart ORDER BY t.timestamp),
            TotalRows = COUNT(*) OVER(PARTITION BY i.IntervalStart)
    FROM    Intervals AS i
            LEFT JOIN T AS t
                ON t.timestamp >= i.IntervalStart
                AND t.timestamp < i.IntervalEnd
)
SELECT  r.IntervalStart,
        Difference = ISNULL(MAX(CASE WHEN RowNum = TotalRows THEN r.Value END) - 
                            MAX(CASE WHEN RowNum = 1 THEN r.Value END), 0)
FROM    RankedData AS r
WHERE   RowNum = 1
OR      TotalRows = RowNum
GROUP BY r.IntervalStart;

Example on SQL Fiddle with 1 Hour intervals

Example on SQL Fiddle with 15 minute intervals

Example on SQL Fiddle with 1 Day intervals

---

EDIT

As pointed out in comments neither of the above solutions account for the advance over period boundaries, the below will account for this:

DECLARE @Start DATETIME2 = '2015-01-09 08:25',
        @Interval INT = 5,     -- INTERVAL IN MINUTES
        @IntervalCount INT = 18; -- NUMBER OF INTERVALS TO SHOW

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
(   SELECT  TOP (@IntervalCount)
            IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
            IntervalEnd = DATEADD(MINUTE, (N - 0) * @Interval, @Start)
    FROM    Numbers AS n
), LeadData AS
(   SELECT  T.timestamp,
            T.Value,
            NextValue = nxt.value,
            AdvanceRate = ISNULL(1.0 * (nxt.Value - T.Value) / DATEDIFF(SECOND, T.timestamp, nxt.timestamp), 0),
            NextTimestamp = nxt.timestamp
    FROM    T AS T
            OUTER APPLY 
            (   SELECT  TOP 1 T2.timestamp, T2.value
                FROM    T AS T2
                WHERE   T2.timestamp > T.timestamp
                ORDER BY T2.timestamp
            ) AS nxt
)
SELECT  i.IntervalStart,
        Advance = CAST(ISNULL(SUM(DATEDIFF(SECOND, d.StartTime, d.EndTime) * t.AdvanceRate), 0) AS DECIMAL(10, 4))
FROM    Intervals AS i
        LEFT JOIN LeadData AS t
            ON t.NextTimestamp >= i.IntervalStart 
            AND t.timestamp < i.IntervalEnd
        OUTER APPLY
        (   SELECT  CASE WHEN t.timestamp > i.IntervalStart THEN t.timestamp ELSE i.IntervalStart END,
                    CASE WHEN t.NextTimestamp < i.IntervalEnd THEN t.NextTimestamp ELSE i.IntervalEnd END
        ) AS d (StartTime, EndTime)
GROUP BY i.IntervalStart;