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.
SELECT DATEDIFF(second, '2000-01-01 08:00:00' , '2000-01-01 16:00:00'); Return the difference/interval in seconds. Divide by 60 to get minutes, and divide by 60 again to get hours. Since you are only using the time component, you can set the datepart to whatever you like.
Measures the difference between two points in time. Intervals can be positive or negative. The INTERVAL data type is SQL:2008 compliant, and supports interval qualifiers that are divided into two major subtypes: Year-month: Span of years and months.
SELECT sale. quantity*item. price as TOTAL FROM item,sale WHERE item. product_id=sale.
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:
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With