SQL-оператор, который вычисляет рост на каждый интервал

В нашей базе данных есть таблица, которая отслеживает энергопотребление устройства. Скорость, с которой вставлены новые значения, не фиксирована, они записываются только в том случае, когда действительно происходит изменение, поэтому временное расстояние между значениями изменяется и может достигать от 1 секунды до нескольких минут. Записи состоят из метки времени и значения. Значение всегда увеличивается с каждой новой строкой, когда она подсчитывает кВтч.

Я хочу достичь следующего: я хочу указать начало и конец datetime, скажем, месяц. Я также хочу указать интервал, как 15 минут, 1 час, 1 день или аналогичный. Результат, который мне нужно получить, в форме [Начало интервала как дата-время], [потребляемая мощность в этом интервале], например, как это (где интервал будет установлен в 1 час):

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

Это выглядит так:

 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 

У меня такое ощущение, что мне придется объединить функцию SUM() с GROUP BY , но я не знаю, как это сделать, потому что, насколько я вижу, мне также нужно будет учитывать только рост за интервал, а не сумма абсолютных значений в этом интервале. Было бы здорово, если бы кто-то смог привести меня на правильный путь.

    Ваши данные образца не совпадают с интервалами результатов, поэтому вы можете пропустить увеличение в пределах интервала в конце или в начале. Поэтому я принял линейное увеличение между рядами данных образца и сопоставил их с интервалом результатов.

     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 @TT ), 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 

    CTE:

    • intervals содержат интервалы, которые вы хотите получить для
    • increaese рассчитывает увеличение в периоды данных выборки
    • rates вычисляют увеличение в секунду в периодах выборки данных
    • samples сопоставляют интервалы результатов с интервалами выборки, соблюдая перекрытия между границами

    Наконец, выбор суммирует периоды выборки, соответствующие одному интервалу.

    ЗАМЕТКИ:

    • Для интервальной суммы> [вашей максимальной глубины рекурсии] вам нужно использовать другое решение для сортировки intervals CTE (см. Решение @GarethD)
    • Подсказка отладки: просто используя select * from samples вы можете увидеть периоды выборки, соответствующие вашим интервалам результатов

    Я думаю, что лучший способ справиться с этим – сначала сгенерировать ваши интервалы, а затем присоединиться к вашим данным, поскольку это во-первых делает группировку намного менее сложной для переменных интервалов, а также означает, что вы все еще получаете результаты для интервалов без данных. Для этого вам понадобится таблица чисел, так как многие люди не имеют одного из них, это быстрый способ генерации «на лету»:

     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; 

    Это просто генерирует последовательность от 1 до 10000. Для получения дополнительной информации об этом см. Следующую серию:

    • Создать набор или последовательность без петель – часть 1
    • Создать набор или последовательность без петель – часть 2
    • Создать набор или последовательность без циклов – часть 3

    Затем вы можете определить время начала, интервал и количество отображаемых записей, а вместе с таблицей чисел вы можете сгенерировать свои данные:

     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; 

    Наконец, вы можете ПОЛНОСТЬЮ ПРИСОЕДИНИТЬСЯ к этим данным, чтобы получить минимальные и максимальные значения для каждого интервала

     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; 

    Если ваши значения могут увеличиваться и уменьшаться внутри инвертирования, вам нужно будет использовать функцию ранжирования для получения первой и последней записи за каждый час, а не min и 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; 

    Пример SQL-скрипта с интервалом в 1 час

    Пример скрипта SQL с 15-минутными интервалами

    Пример скрипта SQL с интервалом в 1 день


    РЕДАКТИРОВАТЬ

    Как отмечалось в комментариях, ни одно из вышеупомянутых решений не учитывает границы заранее периода, ниже будет учтено следующее:

     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; 

    Быстрый способ сделать это – получить дату + час с TimeStamp, чем GROUP BY, а значение для потребления энергии будет MAX (Value) – MIN (Value). Вы можете манипулировать этим TimeStamp другими способами для получения разных интервалов, этот пример предназначен только для почасового потребления.

     SELECT CONVERT(datetime, CONVERT(varchar(10), TimeStamp, 120) + ' ' + CONVERT(varchar(2), DATEPART(hour, TimeStamp)) + ':00:00'), MAX(Value) - MIN(Value) AS Value FROM [Table] GROUP BY CONVERT(datetime, CONVERT(varchar(10), TimeStamp, 120) + ' ' + CONVERT(varchar(2), DATEPART(hour, TimeStamp)) + ':00:00') 
    Interesting Posts
    Давайте будем гением компьютера.