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

Ответ 1

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

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

CTE:

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

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

ПРИМЕЧАНИЯ:

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

Ответ 2

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

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;

Ответ 3

Быстрый способ сделать это - получить дату + час от 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')